Populate Hlookup formula through VBA

Hi,

I have 2 sheets - first sheets are "TestCases" sheet where data is in horizontal format and another sheet is TCTemplate sheet where data is in vertical format. In test cases I am populating 5 field after A python program run and want that status fail pass is updated automatically in TC template looking the test id Horizontally. I want Hlookup formula only and not copy paste the values.

So how can I populate the Hlookup formula in TCTemplate sheet where Lookup value comes from vertical range and lookup range in horizontal range incrementing by every column.

Please see attached Excel sheet. I was tryomg with macro but not able to resolve. Pls help

Hi,

yes VBA can write to cells any Excel formula but as your forgot to share your working formula …

saxena_mk,
try

Sub test()
    With Sheets("TCTemplate")
        With .Range("c2", .Range("b" & Rows.Count).End(xlUp)(1, 2))
            .Formula = "=iferror(hlookup(""*""&b2,'testcases'!$e$3:$r$7,5,false),"""")"
        End With
    End With
End Sub

Originally Posted by jindon

saxena_mk,
try

Sub test()
    With Sheets("TCTemplate")
        With .Range("c2", .Range("b" & Rows.Count).End(xlUp)(1, 2))
            .Formula = "=iferror(hlookup(""*""&b2,'testcases'!$e$3:$r$7,5,false),"""")"
        End With
    End With
End Sub

Excellent Jindon.

Another 2 issues I am sure you can help with

1. The test Cases sheet will be dynamic I mean the name will change as such I need to use a parametrized sheet name. I tried using the sheet name in a variable ws as sunder and it is working fine in the sample excel but when i put it in my actual code it gives " Application defined ...." error

Sub test()
ws1 = "TestCases"
With Sheets("TCTemplate")
With .Range("c2", .Range("b" & Rows.Count).End(xlUp)(1, 2))
.Formula = "=iferror(hlookup(""*""&b2," & ws1 & "!$e$3:$r$7,5,false),"""")"
End With
End With
End Sub

2. Another issue I need to use cell property in target so that I can use the variables for columns number Lookup range as the columns will be dynamic I mean will be either reducing or adding with different sheets being generated.. so instead of using $e$3:$r$7 how can we use cell range?

Thanks

I don't really understand how you want it exactly,

Sub test()
    Dim ws1 As String, myRng As String
    ws1 = "TestCases"
    myRng = Sheets(ws1).Range("e3:r7").Address
    With Sheets("TCTemplate")
        With .Range("c2", .Range("b" & Rows.Count).End(xlUp)(1, 2))
            .Formula = "=iferror(hlookup(""*""&b2,'" & ws1 & "'!" & myRng & ",5,false),"""")"
        End With
    End With
End Sub

HI Jindon,

The problem is solved. The issue was I was not inputting another & inside quotes and the correct formula was to be "=iferror(hlookup(" & Chr(34) & "*" & Chr(34) & "&" & tRng & ",'" & ws1 & "'!" & myRng & ",5,false),"""")" .

Thanks for all the support.

Originally Posted by saxena_mk

HI Jindon,
Thanks for all the support.

You ae welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.