Repeating VLookUp Code

[lorcizzle]

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.

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:

1. 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
2. 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.
3. 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

 

[dave3009]

Hi lorcizzle

I've been pondering this since you posted it. What you're proposing sounds possible but I'd need a few things answered.

RD2_Data obv a named range, can you tell me exactly what it refers to?
You want to check the value of E2 in that range and return 4 cells to the right, do you want this to loop for all values that equal E2 in RD2_Data or just the first?
If it's more than once where do the next occurrence of the return go?
Do you want to then move on to E3 value in RD2_Data and do the same?
When it's returning blank is this E2 not found or blank cell in RD2_Data?

Thanks

Dave

 

[lorcizzle]

Hi Dave3009,

Many thanks for taking the time to respond!

For your questions:

RD2_Data range is a defined table on a sheet in a separate workbook (the code below has all sheets in the same workbook but that was to allow an easier step by step for me as I figured it out....)

The code below sort of does what I want (and hopefully answers your other questions on what I was attempting to do):

1) Does a VLookUp starting in Row 2 and ending in the last row of Column AA (obviously the formula I have entered finds the last row....I couldn't figure out how to do it with my named ranges for columns E and AA);
2) Firstly, checks to see if the corresponding cell in Column E is blank, if this is the case it returns blank ("");
3) Otherwise it will look-up the value in the defined table ("RD2_Data") and return the correct number.

Sub BID_VLookUp()
Dim InFirstRow As Long
Dim InLastRow As Long
Dim Data As Range ' Named table in another sheet
Dim j As Long

' On Error GoTo MyErrorHandler:
On Error Resume Next ' Need to figure out how to handle the error properly!

    Set Data = Range("RD2_Data") ' Set Range
    InFirstRow = 2    'First row with deal
    InLastRow = Cells(Cells.Rows.Count, "AA").End(xlUp).Row
    
        For j = InFirstRow To InLastRow
            If Cells(j, "E").Value = "" Then
                    Cells(j, "AA") = ""
                ElseIf Cells(j, "E").Value <> "" Then
                    Cells(j, "AA") = WorksheetFunction.VLookup(Cells(j, "E").Value, Data, 4, False)
            End If

'MyErrorHandler:
'            If Err.Number = 1004 Then
'                Cells(j, "E").Value = ""
'            End If
        
        Next j
  
    MsgBox "VLookUp Complete"
    [AA2].Select

It seems to work, but I do have an error I'm not 100% sure how to handle - when the lookup value in column E is NOT found in the table (there are a few instances) I will get an error message. When its not found, I just need the corresponding AA cell to be blank. So basically, for the VLookUp, if it finds a match, populate column AA otherwise all else should be blank. My code below does the "On Error Resume Next" which works...but I know this isn't good coding practice.

So basically, I "think" I have it cracked so my question to you would be (i) can you see any fundamental flaws with what I've done and (ii) any suggestions on my error issue?

Happy for any other advice or pointers you have, as I said new enough to VBA so don't worry if you want to shred my coding to pieces!

Thanks again for your time,

L

Excel 2010, Windows 7

 

[dave3009]

Hi

I don't think there is anything fundamentally wrong with your code, it executes well on my test range and I guess it does it's job.

The use of On Error Resume Next isn't all that bad tbh, different if you were sending your code up, down, left and right on error but in this case it seems a valid use. In the event that you notice things getting missed or your results being skewed you might want to look at what is causing the error other than value not found or range reference not valid.

No code shredding today

You know where to get help if you ever need it.

Dave

 

[lorcizzle]

Thanks Dave,

I feel like a VBA master now!

Much appreciate the checking, at least will give me confidence the code is doing what its meant to. Agree on error checker, just knew it was bad coding practice but will keep an eye out.

And don't worry, I'm sure I will be back plenty of times in the future!!

L

 

[lorcizzle]

Hi Dave,

Apologies, I thought I was done but my VBA master skills have let me down one more time!

At the moment, my tidied code is as follows:

Sub BID_VLookUp()

Dim InFirstRow As Long
Dim InLastRow As Long
Dim Data As Range 
Dim j As Long

On Error Resume Next    

    Set Data = Range("RD2_Data") 
    InFirstRow = 2    
    InLastRow = Cells(Cells.Rows.Count, "AA").End(xlUp).Row 
    
        For j = InFirstRow To InLastRow 
            If Cells(j, "E").Value = "" Then 
                    Cells(j, "AA") = "" 
                ElseIf Cells(j, "E").Value <> "" Then 
                    Cells(j, "AA") = WorksheetFunction.VLookup(Cells(j, "E").Value, Data, 4, False) 
            End If 
        
        Next j 

End Sub

So, as we discussed works well and does what I want it. The only wrinkle is the Data = Range("RD2_Data") is on a sheet in the workbook which in practice isn't where it is saved. As a result, is there an easy way to link the "looked up" table in another workbook?

I have tried a few different variations of

Set Data = Workbooks("C:\Folder Name \File Name.xlsm").Worksheets("Sheet Name").Range("Defined Range Name")

But can't seem to get it working, most of the time its a run-time error 9; Subscript out of Range.

My assumption of the problem is the Workbook isn't open, but obviously it would be easier to automate it as much as possible without opening it? Is this possible - or do I need to add some code to open the workbook while the VLookUp runs and then close afterwards?

Again, thanks!

 

[dave3009]

Hi

Have you tried to open the workbook via your sub first?

EDIT: Oops, didn't read that last bit, you will likely need to open the file.

 

[lorcizzle]

Hi Dave,

I spent a while trying to get it to auto open the external workbook but couldn't get it to work. My VBA master days seem so distant now...

Unfortunately, I managed to close without saving and lost all of my attempted code which I could have posted here for your review. The main problem I was encountering was after I opened the workbook to LookUp, it would then revert to my "PERSONAL MACRO" excel book to execute the code which was obviously not what I wanted. A problem for another time I think.

However, upon some further thought it is likely I will need to refer to this external sheet a few more times. As a result, it might be useful to simply copy this tab into my "active" worksheet. I found this code (I love google):

Sub ImportWorksheet()
    ' This macro will import a tab from another workbook into this workbook
    Dim PathName As String
    Dim FileName As String
    Dim TabName As String
    Dim ControlFile As String
        
    Sheets("Assumptions").Select
    PathName = Range("P3").Value ' C:\Folder Name, saved in cell P3 on the active sheet
    FileName = Range("P4").Value ' File Name, saved in cell P4 on the active sheet
    TabName = Range("P5").Value ' Tab Name to copy, saved in cell P5 on the active sheet
    ControlFile = ActiveWorkbook.Name ' Defines my active book as "Control"
    Workbooks.Open FileName:=PathName & FileName ' Opens the non-Control workbook
    ActiveSheet.Name = TabName ' Activates the target Tab
    Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1) 'Copies it and pastes it into Control Book
    Windows(FileName).Activate ' Re-activates the target workbook
    ActiveWorkbook.Close SaveChanges:=False ' closes it without saving
    Windows(ControlFile).Activate ' makes the Control file the active sheet
End Sub

So, in a round about way I think I have the VLookUp working.

Thanks agasin for your help...I'm sure I'll be back in a while after I break s