VBA Relative Vlookup Formula

[Cpinhey]

Hi,

I have a userform that is coded to enter data into the next empty row on a worksheet. The first 8 columns are populated with data from the user form, I need column 9 to be populated with a VLOOKUP formula. The extract code below does enter the formula, but "MONTH(B8)" appears to be absolute as each row of new data returns a result based on B8.

Can anyone point me in the right direction as to how I can make the VLOOKUP relative for each row of data entered?

Dim lRow As Long
Dim lcol As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")




'find first empty row in data sheet
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


lcol = Me.cboCase.ListIndex


With ws
  .Cells(lRow, 1).Value = Me.DTPicker1.Value
  .Cells(lRow, 2).Value = Me.cboCase.Value
  .Cells(lRow, 3).Value = Me.txtTeam.Value
  .Cells(lRow, 4).Value = Me.cboFeedback.Value
  .Cells(lRow, 5).Value = Me.cboClassification.Value
  .Cells(lRow, 6).Value = Me.cboTopic.Value
  .Cells(lRow, 7).Value = Me.txtTitle.Value
  .Cells(lRow, 8).Value = Me.txtComments.Value
  .Cells(lRow, 9).Formula = "VLOOKUP(MONTH(B8),Lists!$C$2:$D$15,2,FALSE)"
End With

For Background. I need the VLOOKUP to be entered relative for each row as the purpose of the vlookup is to find the month serial entered in column 1 vide .Cells(lRow, 1).Value = Me.DTPicker1.Value and return the relative month ie. If 01/08/2012 entered in column1, column9 needs to show "August". Of course if anyone has a better suggestion as to how this can be achieved I would be more than happy to hear your idea.

Many Thanks in advance

 

[Momentman]

Maybe change

.Cells(lRow, 9).Formula = "VLOOKUP(MONTH(B8),Lists!$C$2:$D$15,2,FALSE)"

to

.Cells(lRow, 9).Formula = "VLOOKUP(MONTH(B & lRow),Lists!$C$2:$D$15,2,FALSE)"

 

[Cpinhey]

Hi,

Thanks for your reply, unfortunately it's not quite working. The ws cell is returning the error #Name?

Any ideas?

 

[Momentman]

Hi,

Thanks for your reply, unfortunately it's not quite working. The ws cell is returning the error #Name?

Any ideas?

My bad, it has to be

.Cells(lRow, 9).Formula = "VLOOKUP(MONTH(cells(lRow,"B").value),Lists!$C$2:$D$15,2,FALSE)"

 

[Cpinhey]

Still not working.

I'm getting a Run Time Error 1004, Object Defined Error, Application Defined Error.

I've tried a couple of variations myself including double quotes around ""B"" but with no luck.

It has to be something obvious! I think my brain is too involved with it now, I will come back to it in a few days and hopefully the answer will become clear.

Thanks for your help.