CODEWITHSUNDEEP
excelvba
iMan7
iMan7

Reputation: 133

How to check for a value across columns and then copy the the contents of one cell below it?

So I have an excel sheet with the days of the week starting at D7 and extending to AF7 for 15 days of the month. I want to look through each of them and then copy the contents of the cell directly below them in row 8 to another sheet.

Im getting runtime error 1004 Method Range of Object _Global failed on this line. Im pretty new to VBA so I'm not exactly sure what went wrong. 

LastCol = Range(Cells(7, Columns.Count)).End(xlUp).Columns

Full code:

Sub Copy()
    Dim cell As Range
    Dim lastRow As Long, i As Long

    LastCol = Range(Cells(7, Columns.Count)).End(xlUp).Columns
    i = 4

    For Each cell In Sheets(1).Range(LastCol & "D7:7")
        If cell.Value = "MON" Then
            cell.Offset(-1, 0).Copy Sheets(2).Cells(1, i)
            i = i + 1
        End If
    Next

End Sub

Upvotes: 0

Views: 71

Answers (2)

user4039065
user4039065

Reputation:

The name you've assigned to LastCol seems to indicate that you want to get the column number yet you are using xlUp instead of xlToLeft. Additionally, you are asking for the .Columns, not the Column.

LastCol = Cells(7, Columns.Count).End(xlToLeft).Column

This returns the column number (e.g. column F = 6, column AA = 27, etc) of the last used column in row 7.

with Sheets(1)
    LastCol = .Cells(7, Columns.Count).End(xlToLeft).Column
    For Each cell In .cells(7, "D").resize(1, LastCol - 3)
        If ucase(cell.Value) = "MON" Then
            cell.Offset(1, 0).Copy destination:=Sheets(2).Cells(1, i)
            i = i + 1
        End If
    Next
end with
  1. Are you using an actual date in row 7 with a custom number format of ddd? If this is the case, then you should be using If weekday(cell.Value) = 2 Then instead of checking for MON.
  2. If you want to copy the contents of the cell directly below them then your offset should be 1 row not -1 row; e.g. cell.Offset(1, 0).Copy

Upvotes: 0

Hrothgar
Hrothgar

Reputation: 422

To get the last, that is rightmost, column in a row, first set MyRow to the row you want to use:

lastcol= ActiveSheet.Cells(MyRow, Columns.Count).End(xlToLeft).Column

For the last row, after setting MyColumn to the appropriate value:

lastrow = ActiveSheet.Cells(Rows.Count, MyColumn).End(xlUp).Row

But your range reference isn't quite right either. Personally, I don't see the value of using a range variable in the loop. You could try this. (note that a negative offset reads the row above, not below)

Sub Copy()
Dim lastRow As Long, i As Long, LocX as long
Dim source As Worksheet, dest As Worksheet
Set source = Sheets(1)
Set dest = Sheets(2)
lastcol = source.Cells(7, Columns.Count).End(xlToLeft).Column
i = 4
For locX = 4 To lastcol
    If source.Cells(7, locX).Value = "mon" Then
        source.Cells(7, locX).Offset(1, 0).Copy dest.Cells(1, i)
        i = i + 1
    End If
Next
End Sub

Upvotes: 1

Related Questions