How to use a For loop with Vlookup in Excel using Variables to identify cells

Question

1) My objective is the following one:

To replace the content of a cell with a conditional statement that is either "Monitorato" or "Non Monitorato". The Condition determining the type of statement is the output of a Vlookup function. IF vlookup finds a value I would have "Monitorato", while if this does not happen I would have "Non Monitorato".

2) By using traditional excel functions, the thing is solved In this way:

=IF(ISNA(VLOOKUP(cell to look for, range, column, false)),"Non Monitorato","Monitorato")

I then copy and paste this formula to all the cells below the one I wrote the initial formula

3) However I would like to implement this thing using VBA.

Moreover, there is an additional problem. The data are actually filtered, so I need to use the Vlookup function only on the filtered data. The Vlookup function should look for the first visible cell in the dataset.

4) The horrible code that I wrote is the following one:

Sub MyFunction()
Dim i As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim Header As Range

Set Header = Range("d1")
FirstRow = Range(Header.Offset(1, 0), Header.End(xlDown)).SpecialCells(xlCellTypeVisible).Cells(1).Count

LastRow = ActiveSheet.UsedRange.Rows.SpecialCells(xlCellTypeVisible).Count
For i = FirstRow To LastRow
Sheets(1).Cells(FirstRow, 5) = Application.WorksheetFunction.VLookup(Sheets(1).Cells(i, 3), Sheets(1).Range("C2:D100"), 2)
Next i
End Sub

The error I am getting is

Unable to get the vlookup property of the worksheet function class

I would like to solve it, but I also know there must be an easier solution to the problem...

Thank you all!

[See if this helps in avoiding this error](http://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error) — Dan, Mar 29 '16 at 18:01

score 1 · Answer 1 · answered Mar 29 '16 at 18:15

There's actually several issues with the current code and it will not accomplish what you are after. See if this code works for you:

Sub Monitorato() 

Dim ws1 as Worksheet

Set ws1 = Sheets(1)

Dim rLookup As Range, rCell as Range

With ws1

    Set rLookup = .Range(.Range("D1"),.Range("D1").End(xlDown)).SpecialCells(xlCellTypeVisible)

    For each rCell in rLookup

        If Not .Range("C2:D100").Find(rCell.Offset(,-1).Value2) Is Nothing Then

            rCell.Offset(,1).Value = "Monitorato"

        Else

            rCell.Offset(,1).Value = "Non-Monitorato"

        End If

    Next

End With    

End Sub`

@amolinaro - great! please mark as answered so others know going forward (check mark on top left of answer). — Scott Holtzman, Mar 30 '16 at 18:49

How to use a For loop with Vlookup in Excel using Variables to identify cells

1 Answers1