Excel VBA - Vlookup w/Named Range

[spydey]

Hello!

I am trying to write some code so that a vlookup is automatically performed when a change is made to any cell in column A of a specific worksheet.

Also, if the vlookup returns N/A, I want to leave the output blank, instead of N/A populating.

2 Worksheets, same Workbook:

Population
&
Completed

When a number is entered into a cell in column A in the worksheet Population, I need a vlookup performed. It is to look at the numeric value in that cell in column A worksheet Population, find that value in column A worksheet Completed, gather the data from column 3 worksheet Completed, and input it back into column 7 worksheet Population.

If I was writing the formula in-cell in worksheet Population, it would be like this (as an example):

=VLOOKUP($A3,Completed.Range_Completed,3,0)

However, I don't want to have formulas in the cells. I would like to have it done behind the scenes via VBA. Still learning so I am glad I have a resource to learn from.

This is the code I have so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("ColumnA.Population")) Is Nothing Then Exit Sub    Dim rng2 As Range
    Application.EnableEvents = False
    For Each rng2 In Target
        If Len(rng2.Value) = 0 Then
           rng2.Offset(0, 7).ClearContents
        ElseIf IsNumeric(rng2.Value) = True Then
           rng2.Offset(0, 7) = Application.WorksheetFunction.If(IsNA(VLookup(rng2.Offset(0, 0), Range("Completed.Range_Completed"), 3, False)), "", VLookup(rng2.Offset(0, 0), Range("Completed.Range_Completed"), 3, False))
        End If
    Next rng2
    Application.EnableEvents = True

End Sub

I have assigned a few named ranges so that it is (hopefully) easier to write this and have it work.

The named ranges are as follows:

"ColumnA.Population" refers to ColumnA of the Population worksheet.
"Completed.Range_Completed " refers to Completed!$A:$G

Please note that all my named ranges are dynamic. They only extend down as far as there is data in column a of the corresponding worksheet. (hopefully that makes sense)

Here is how I am making my ranges dynamic:

Completed.Range_Completed --> =Completed!$A$2:INDEX(Completed!$A:$G,COUNTA(Completed!$A:$A))
ColumnA.Population --> =Population!$A$2:INDEX(Population!$A:$A,COUNTA(Population!$A:$A))


So I have tried the above code, and I seem to be missing something.

Any pointers, thoughts, ideas, etc?

Also, I think that my dynamic Completed.Range_Completed might not be set up correctly. Does it look right?

Thank you everyone!!

-Spydey

 

[spydey]

Hey thanks JoeMo!

Yeah, I noticed that my code had errors, but it was more of the idea I was trying to get out there. I have tried a few different iterations of the code and none seem to work the way I am hoping.

I tried yours, as posted above, and it pulls over the date, but not the vlookup value.

Atleast I am not getting a debug error 1004 anymore, thanks to the error handling.

If only I could get the vlookup to work correctly.

Maybe there is a better way to get the same result as a vlookup but without actually using a vlookup? Maybe something similar to Index(Match. Granted, vlookup and Index(Match are Worksheet functions. Perhaps I need to implement non-worksheet functions to achieve the same thing natively. I am just unsure on how to go about doing that.

I am going to keep researching and playing around. If you have any further ideas or input, please let me know.

Also, if anyone else has anything, I am all ears!

-Spydey