Hi,
A newly initiated VBA user struggling a little to get some VBA code to work - this website has been very useful thus far but I haven't been able to make my code work even while looking at other examples people have had -any help will be greatly appreciated (as I don't think its difficult I'm just missing the "next step" in my skillset!).
Unsurprisingly, I am looking at a value on my active sheet, searching for the data in a named table on another sheet and populating the result in a single cell.
This seems to work which, while slightly satisfying )), is too simple for what I want.
What I would like to do is the following:
AS far as I can tell, I'll need some sort of loop in my code, it's just beyond me at the moment.
Looking forward to hearing your solutions, as I'm pretty sure its relatively easy
A newly initiated VBA user struggling a little to get some VBA code to work - this website has been very useful thus far but I haven't been able to make my code work even while looking at other examples people have had -any help will be greatly appreciated (as I don't think its difficult I'm just missing the "next step" in my skillset!).
Unsurprisingly, I am looking at a value on my active sheet, searching for the data in a named table on another sheet and populating the result in a single cell.
Code:
Sub VlookUp_Test()
Dim myVal As String ' contains both text and numbers
Dim Data As Range ' Named table in another sheet
Dim Clm As Integer
Set Data = Range("RD2_Data") ' Set Range
myVal = [E2] ' Value to be searched
Clm = 4 ' Column to be fetched
Worksheets("Data").[AA2] = WorksheetFunction.VLookup(myVal, Data, Clm, False)
' basically does the VLookup for cell "AA2" and populates it with the correct answer
End Sub
This seems to work which, while slightly satisfying )), is too simple for what I want.
What I would like to do is the following:
- Search for the "Data" in a separate sheet; I have a copy of the sheet in the active workbook but it is much more preferable to search for it in its home sheet
- instead of a single VLookup, I would like the VBA to tell it to do it for every row in column AA by looking for the corresponding data in column E. I have given names to the columns AA and E as the data is in an dynamic table which will expand or decline if I add/remove data, I know this can help code in some circumstances.
- if possible have an error check to populate the cell in AA as blank ("") if there is either no info in column E or the "Data" table
AS far as I can tell, I'll need some sort of loop in my code, it's just beyond me at the moment.
Looking forward to hearing your solutions, as I'm pretty sure its relatively easy