CODEWITHSUNDEEP
vbaexcel
user1872838
user1872838

Reputation: 11

Cell.Offset Issue

I am a newbie with vba and programming in general, but can generally modify existing code if it is simple enough. I modified a macro that pulls cells from another workbook based on the path defined by another macro I found. I've gotten it to pull in cells correctly based off of Column A. I now need it to pull information based on Column AF (different criteria). I modified it to do so, and successfully, but it copied the 41 following columns (I can't seem to get the offset working correctly) rather than those both proceeding and following.

How can I fix this to do so? I apologize for what is likely a bush league question.

ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = GetFileName

        Set Wkb = Workbooks.Open(FileName:=path)
        For Each WS In Wkb.Worksheets
            WS.Select
            B = Application.CountA(Range("A:A"))
            If B = 0 Then
                            Else
                For Each Cell In Range("A1:A" & B)
                If VarType(Cell.Value) <> vbnumber Then
                A = Application.CountA(Workbooks(ThisWB).Sheets("ComplaintsFetched").Range("A:A")) + 1
               Range(Cell, Cell.Offset(0, 41)).Copy Workbooks(ThisWB).Sheets("ComplaintsFetched").Range("A" & A)
               End If
               Next Cell
            End If
        Next WS
        Wkb.Close False
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
Workbooks(ThisWB).Sheets("ComplaintsFetched").Select

Upvotes: 0

Views: 271

Answers (2)

grahamj42
grahamj42

Reputation: 2762

Offset does what the name implies, and returns a selection the same size as the original. One can then restrict it with the Rows() and Columns() methods.

I don't quite understand what you're trying to do, but

Cell.Offset(0,41).Copy

will copy the cell 41 columns to the right of the range Cell.

It's not a good idea to name variables A and B in an Excel macro, too easy to confuse them with columns A and B.

Upvotes: 0

dash
dash

Reputation: 91462

Your function call:

Range(Cell, Cell.Offset(0, 41)).Copy 
    Workbooks(ThisWB).Sheets("ComplaintsFetched").Range("A" & A)

Is doing the following; Range(Cell, Cell.Offset(0, 41) means select a range that stretches from the current cell (the first argument) to a cell that is 0 rows off, and 41 columns to the right of your current cell (the second argument).

This is why you are selecting the following 41 columns.

Instead, to select the preceding, and following cells, effectively the entire row, you can try:

Range(Cell).EntireRow.Copy Workbooks(ThisWB).Sheets("ComplaintsFetched").Range("A" & A)

This will copy the entire row from your sheet to the Complaints Worksheet, starting at the required row, column A

Upvotes: 1

Related Questions