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 having 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, Jan 03 '20 at 15:34
Why are you using `Range("AA" & .Rows.Row`? Did that even compile? — JvdV, Jan 03 '20 at 15:36
@Goldaaron18 separate out your find from the `Range("AA"....` statement, then check if it's `vbNullString` — jclasley, Jan 03 '20 at 15:39
It seems the main issue was the rows.row I should have only used .row — Goldaaron18, Jan 03 '20 at 18:46
Regarding the updated code, you still have instances of `Range` that don't specify the `Worksheet`, e.g. `Set Netrng = Range("AA"...` — BigBen, Jan 03 '20 at 19:42

BigBen · Answer 1 · 2020-01-03T15:43:26.960

2

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.

edited Jan 03 '20 at 15:43

answered Jan 03 '20 at 15:38

BigBen

46,229
7
24
40

1

Exactly. And if OP expected actual hits on the `Find`, I would suggest that OP looks into explicit sheet referencing. – JvdV Jan 03 '20 at 15:40
That, and avoiding Select, and using something more modern than `While...Wend`... – BigBen Jan 03 '20 at 15:41
@BigBen when I take the Range1.activate away it works the first time through while loop but not the second time. Seems like the range needs to be activated, Any way around that? – Goldaaron18 Jan 03 '20 at 19:15
My recommendation #2 should cover that. Basically, you've almost never need to `Activate` if you qualify any range references with the appropriate workbook and worksheet. – BigBen Jan 03 '20 at 19:16
can you give some sample code of how I would do that? – Goldaaron18 Jan 03 '20 at 19:21
There is some useful sample code in the link from Recommendation #1. – BigBen Jan 03 '20 at 19:21
I tried Dim wb As WorksheetSet wb = ActiveSheet Set Range1 = wb.Range(First, First.End(xlDown)) but I still have same issue that 2nd time through while loop it it type mismatch. Note that the workbook im working with will not always have the same name – Goldaaron18 Jan 03 '20 at 19:34
Maybe edit your original question with your revised code so we can see what changes you've made. – BigBen Jan 03 '20 at 19:36
@Bigben I just edited the code. the error is different than b4 it is type mismatch now after 2nd loop. if I activate the range I dont have this issue – Goldaaron18 Jan 03 '20 at 19:48

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

1 Answers1