Trying to find value in range and get its row. Variable not set error?

Question

The code below is supposed to take the value for net in each month, copies it, search for net name in range1(another worksheet) and pastes value in the cell corresponding to that row and column "AA".

This part of code is gaving issue: ""Set Netrng = Range("AA" & Range1.Find(What:=Net, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Rows.row)""

The error is - object variable or with block variable not set. what am I doing wrong?


Sub test()

    Dim Range2 As Range
    Dim lRow As Long
    Dim Count As Long
    Dim Net As String
    Dim Line As Range
    Dim Netrng As Range
    Dim First As Range
    Dim Range1 As Range
    Dim wb As Worksheet

    Set First = ActiveCell
    Set wb = ActiveSheet
    Set Range1 = wb.Range(First, First.End(xlDown))
    ActiveWindow.ActivatePrevious

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Client Code").CurrentPage _
        = "BUN"

    ActiveSheet.Range("B5").Activate
     lRow = Cells(Rows.Count, 1).End(xlUp).row - 6
     Set Range2 = Range(ActiveCell.Offset(2, -1), ActiveCell.Offset(lRow, -1))
     Set Months = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 2))



       Count = 1
       While Count <= Range2.Count
       Set Line = Range2.Rows(Count)
       Net = Line.Value
       Line.Offset(0, 1).Copy
       ActiveWindow.ActivatePrevious
       Set Netrng = Range("AA" & Range1.Find(What:=Net, After:=ActiveCell, LookIn:=xlValues, LookAt _
       :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).row)
       Netrng.Offset(0, 4).PasteSpecial Paste:=xlPasteValues
       Netrng.Value = 0
       ActiveWindow.ActivatePrevious
       Line.Offset(0, 2).Copy
       ActiveWindow.ActivatePrevious
       Netrng.Offset(0, 8).PasteSpecial Paste:=xlPasteValues
       ActiveWindow.ActivatePrevious
       Count = Count + 1
       Wend

End Sub

how would I test if succesful? I guess if not succesfull I would want to go to the next net in range2 — Goldaaron18
Why are you using Range("AA" & <find a cell>.Rows.Row? Did that even compile? — JvdV
@Goldaaron18 separate out your find from the Range("AA".... statement, then check if it's vbNullString — jclasley

BigBen BigBen · Accepted Answer · 2020-01-03T15:38:21

As is, the code is assuming that the Find is successful, which may not always be the case.

To test:

Dim foundRng as Range
Set foundRng = Range1.Find(What:=Net, After:=ActiveCell, LookIn:=xlValues, _ 
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not FoundRng is Nothing Then
    Set Netrng = Range("AA" & foundRng.Row)
    ...
End If

Other recommendations:

Avoid using Select and Activate. (and ActiveCell, ActiveWindow, anything Active).
Fully qualify which Workbook and Worksheet each Range is on (helpful reading in the answer on avoiding Select).
While...Wend is old-fashioned. Use a For Each loop.

Trying to find value in range and get its row. Variable not set error?

1 Answers