Excel VBA - worksheet/range reference does not work properly

Question

I have problem with the worksheet/range initiating and I can't see why my code doesn't work. I was debugging and it seems like I had to be on a certain worksheet to make the related references to work. Can anyone please let me know what did I do wrong?

Sub salesImport()

Application.ScreenUpdating = False

'Excel workbook, the source and target worksheets, and the source and target ranges.
Dim wbBook As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim rnSource As Range
Dim rnTarget As Range
Dim rng As Range
Dim cIndex, rIndex1, rIndex2, rIndex3, iR, iC As Integer
Dim rowC, columnC As Integer


'Initialize the Excel objects
Set wbBook = ThisWorkbook
With wbBook
    Set wsSource = .Worksheets("Sales")
    Set wsTarget = .Worksheets("Summary-Official")
End With


'On the source worksheet, set the range to the data stored
With wsSource
    rowC = .Cells.SpecialCells(xlCellTypeLastCell).row
    columnC = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Set rnSource = .Range(Cells(1, 1), Cells(rowC, columnC))
End With

With wsTarget
    Set rnTarget = .Range("B98:AM122")
End With

rIndex1 = 6 'month
rIndex2 = 10 'plant
rIndex3 = 17 'sales
iR = 0
iC = 0

For Each Column In rnSource
    Column.Cells(rIndex1, 1).Select
    Dim m As String: m = Column.Cells(rIndex1, 1).Value
    Select Case Month(DateValue("01 " & m & " 2012"))
        Case 1
            iC = 6
        Case 2
            iC = 7
        Case 3
            iC = 8
        Case 4
            iC = 9
    End Select
    iR = findrow2(Column.Cells(rIndex2, 1), rnTarget)
    If iR <> 0 Then
        rnTarget.Cells(iR - 97, iC).Value = Column.Cells(rIndex3, 1).Value
    End If
    'MsgBox ("got here")
Next Column

Application.ScreenUpdating = True

End Sub

Thanks!

Important note: `Column` is not defined. Add `Option Explicit` to the top of the module and declare all variables. Also, perhaps consider using a different variable name. — BigBen, Dec 04 '19 at 22:29
Note that `Dim rowC, columnC As Integer` only declares `columnC As Integer` but `rowC As Variant`. In VBA you need to specify a type for **every** variable, otherwise it is `Variant` by default: `Dim rowC As Long, columnC As Long`. Additionally row counting variables need to be `Long` because Excel has more rows than fit into `Integer`. I recommend [always to use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) as there is no benefit at all in using `Integer` in VBA. — Pᴇʜ, Dec 05 '19 at 10:28

score 0 · Accepted Answer · answered Dec 05 '19 at 00:55

0

With wsSource
    '...
    Set rnSource = .Range(Cells(1, 1), Cells(rowC, columnC))
End With

should be

With wsSource
    '...
    Set rnSource = .Range(.Cells(1, 1), .Cells(rowC, columnC))
End With

...otherwise your Cells() calls default to the activesheet, which may not be wsSource

answered Dec 05 '19 at 00:55

Tim Williams

154,628
8
97
125

I'm not seeing an area for me to accept the answer but after modified my code with the suggestions from the 3 replies, it is working now. Thanks a lot everyone! – Viola Dec 05 '19 at 15:28

Excel VBA - worksheet/range reference does not work properly

1 Answers1