Construct VLOOKUP in VBA ?

[Gasman]

[SOLVED] Construct VLOOKUP in VBA ?

Hi all,

I am trying to automate an excel sheet that I need to update each week.

I've got all the automatic input working and at present I am copying and pasting two formulae after that is carried out.

The formulae are for example row 416

=IF(ISNA(VLOOKUP($D416,Brady!$A:$K,9,FALSE)),0,VLOOKUP($D416,Brady!$A:$K,9,FALSE))

=IF(ISNA(VLOOKUP($D416,Brady!$A:$K,9,FALSE)),"NA","")


I already have one formula that is entered in another set of cells in a column to determine the data to extract.

That is =YEAR(A416)& TEXT(WEEKNUM(A416),"00") and is constructed from the code below

strFormula = "=YEAR(RC[-6])& TEXT(WEEKNUM(RC[-6])," & """00""" & ")"

How would I construct the string for the first two formulae please.?
The row number is obviously changing and I wish to see the formula in the cells when looking at the sheet and not the values of the formulae. Basically just automate what I am doing manually at present.


TIA

 

[Rx_]

Let me throw a few quick and simple examples of the Vlookup that I use from MS Access to create formulas in a remote Excel object.
The objXL. variable in my code is just a reference to Excel.Application.
From within Excel, it can be omitted when writing code.

Set WKB1 = ActiveWorkbook 
    WKB1.Sheets("MYDATA").Range("D20:D22").Formula = "=VLOOKUP(C18,[WKB4]Sheet1!$A$15:$B$22,2,FALSE)"

This uses Vlookup in a loop to create a Range of data referenced by a pull-down list box on another worksheet.

  objXL.Range("E3").Select ' Select cell for Answer Key to start - then offset from there by 1 for the loop
  objXL.ActiveCell.FormulaR1C1 = "=VLOOKUP(Drop_Down,Matrix_Values,4,0)" ' Offset at 5 R_58 ' add 4th parmater 0 or data must be in alpha order
' declared variables like MyCounter not shown  
For MyCounter = 5 To 12 ' Number of columns in the Answer key from Matrix tab
      objXL.ActiveCell.Offset(0, 1).Range("A1").Select
     ' the above could be used with variables:
       ActiveCell.Offset(0, 1).Range("A1").Select
      objXL.ActiveCell.FormulaR1C1 = "=VLOOKUP(Drop_Down,Matrix_Values, " & MyCounter & ",0)"
  Next MyCounter

The basic assignment would be something along this untested example:

ActiveCell.FormulaR1C1 = "=IF(ISNA(VLOOKUP($D416,Brady!$A:$K,9,FALSE)),0,VLOOKUP($D416,Brady!$A:$K,9,FALSE))"

If your code had variables that captured things like the RecordCount, or range's row count, worksheet names... then build the formula something like this:

Dim MySheetName, lookupFrom, MyRange       ' all just objects but could be fully qualified (better)
MySheetName= "WorksheetName"         
lookupFrom = ActiveCell.Offset(0, -5).address
MyRange = "'" & MySheetName & "'!A:D"        ' Note the !   
ActiveCell.Formula = "=VLOOKUP(" & lookupFrom & "," & myRange & ", 3, FALSE)"

As a beginner, take out one constant and insert one variable at a time.
It will help get the hang of building a string.
This simple example
intRowPosition is = 6 - it is just the location where a Recordset was pasted
objXL.Range("A" & intRowPos - 1 & ":R" & intRowPos - 1).Select
It would result with the following
objXL.Range("A6:R6").Select

 

[Gasman]

Wow, that looks very complicated, but gave me the idea to use the row number for the formula.

so I created

        strLookUp = "=IF(ISNA(VLOOKUP($D" & ActiveCell.Row & ",Brady!$A:$K,9,FALSE)),0,VLOOKUP($D" & ActiveCell.Row & ",Brady!$A:$K,9,FALSE))"

which in the debug window evaluates to

=IF(ISNA(VLOOKUP($D3,Brady!$A:$K,9,FALSE)),0,VLOOKUP($D3,Brady!$A:$K,9,FALSE))

and the hard coded version is

=IF(ISNA(VLOOKUP($D423,Brady!$A:$K,9,FALSE)),0,VLOOKUP($D423,Brady!$A:$K,9,FALSE))

so I am at a loss to see what I am doing wrong.

Test code is

Sub Test()
Dim strFormula As String, strLookUp As String, strNA As String
Dim lweeknum As Long, lRow As Long

lweeknum = 201612
strFormula = "=YEAR(RC[-6])& TEXT(WEEKNUM(RC[-6])," & """00""" & ")"
strLookUp = "=IF(ISNA(VLOOKUP(RC[-4]),Brady!$A:$K,9,FALSE)),0,VLOOKUP(RC[-4]),Brady!$A:$K,9,FALSE))"
strNA = "=IF(ISNA(VLOOKUP($D" & ActiveCell.Row & ",Brady!$A:$K,9,FALSE))," & """NA""" & ","""""")"

Range("G2").Select
 ActiveCell.FormulaR1C1 = strFormula
    Do Until ActiveCell.Value <> lweeknum
        ActiveCell.Offset(1, 0).Activate
        ActiveCell.FormulaR1C1 = strFormula
        ActiveCell.Offset(0, 1).Activate
        strLookUp = "=IF(ISNA(VLOOKUP($D" & ActiveCell.Row & ",Brady!$A:$K,9,FALSE)),0,VLOOKUP($D" & ActiveCell.Row & ",Brady!$A:$K,9,FALSE))"
        ActiveCell.FormulaR1C1 = strLookUp
        ActiveCell.Offset(0, 2).Activate
        ActiveCell.FormulaR1C1 = strNA
    Loop
    lLastRow = ActiveCell.Row() - 1

End Sub

The error I get is
Runtime error 1004
Application-defined or object-defined error

If I use strFormula for all the cells it works. ?

 

[Gasman]

[SOLVED] Re: Construct VLOOKUP in VBA ?

Ok, I found my mistake.

I was using the incorrect property of the active cell. For the initial formula it was fine using Activecell.FormulaR1C1 as I was using [RC-1] syntax, but when not using that syntax it appears I should use a Activecell.Formula

So my code now looks like

        ActiveCell.Formula = "=IF(ISNA(VLOOKUP($D" & ActiveCell.Row & ",Brady!$A:$K,9,FALSE)),0,VLOOKUP($D" & ActiveCell.Row & " ,Brady!$A:$K,9,FALSE))"

and it works fine.

 

[Rx_]

Just too busy to get back, thanks for posting your solution!

For your vlookup instead of hardcoding the workbook and tab (worksheet) names - this might give you an idea for assigning them to a variable.

Great work on your part!

Range("E3:E27").FormulaR1C1 = "=VLOOKUP(RC[-4],'" & WbkNameVar & TabNameVar & "'!R3C17:R59C18,2,False)"

 

[Gasman]

Well I found that despite putting the correct formula in the cells they did not eventually work.

This appears to be because when I added the formula to the input sheet, it has no knowledge of the worksheet 'Brady' as that exists in the Weekly Report workbook.
I was copying the data and these formulae from the input sheet and pasting into the relevant sheet in the workbook.

So I ended up having to copy the formula from a previous cell in the receiving worksheet and pasting in the new rows added by the incoming data.

Learnt something new anyway.