CODEWITHSUNDEEP
excelvba
thankseveryone
thankseveryone

Reputation: 127

vba vlookup to populate a worksheet

I have a user form with a combo box and a submit button which enters the selections made by users from the drop down onto a worksheet on the first empty row.The combo box vba works fine. However, for example if the combo box choice is entered in cell C2 (because the first empty row is 2 and C isalways the column where the combo box enters the result) I want in column D to have a vlookup formula in VBA which, based on what it is in column C to autofill AUTOMATICALLY AT THE SAME TIME column D. Like when you drag down a vlookup formula in column D based on the values in column C.

This is what I've got so far but it does not work. The vlookup part doesn't seem to work. Am I missing something? 

 With Sheets("TasksData").Range("D2", Sheets("TasksData").Cells      
  (Rows.Count, "D").End(xlUp))
    .Offset(, 1).Formula = "=VLOOKUP(C" & .Row & ",'ValidationTables'!$L:$M,2,FALSE)"
    .Offset(, 1).Value = .Offset(, 1).Value
  End With

enter image description here

Upvotes: 1

Views: 214

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

Your With statement looks a little off to me. Were you getting an error?

Try this instead:

Dim lastRow
With Sheets("TasksData")
    lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    With .Range(.Cells(2, 3), .Cells(lastRow, 3))
        .Offset(, 1).FormulaR1C1= "=VLOOKUP(RC[-1],'ValidationTables'!C12:C13,2,FALSE)"
        .Offset(, 1).Value = .Offset(, 1).Value
    End With
End With

Upvotes: 1

Related Questions