asked on

Small vlookup issue

Hello all,

I have a small problem with a VLookup macro.

I want to put the result for that formula in column N of active cell.
=VLookup(S2135,'Rate Group'!K:L,2,0)

But it leaves the cell empty all the time.

Here is the macro in my

Private Sub Worksheet_Change(ByVal Target As Range)

Dim QW As Range
Set QW = Intersect(Target, Range("S6:S" & Rows.Count))
If Not QW Is Nothing Then
  If QW.Value > "" Then
    QW.Offset(, -5).Formula = "=IF(ISNA(VLOOKUP(QW.Value,'Rate Group'!K:L,1,0))"

  End If
  QW.Offset(, -5).Value = ""
End If

Open in new window

Can you please help me?

Thanks

CSLARSEN

Private Sub Worksheet_Change(ByVal Target As Range)

Dim QW As Range
Set QW = Intersect(Target, Range("S6:S" & Rows.Count))
If Not QW Is Nothing Then
If QW.Value > "" Then
QW.Offset(, -5).Formula = "=IF(ISNA(VLOOKUP(QW.Value,'Rate Group'!K:L,1,0))"

End If
'WHAT HAPPENS IF YOU REMOVE THIS LINE? QW.Offset(, -5).Value = ""
End If
cheers
cslarsen

Wilder1626

ASKER

Hello

I have an error in the formula.

Here is the excel spreadsheet has an example.

Thanks again.
DC-Province.xlsm

ASKER CERTIFIED SOLUTION