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

 

[JoeMo]

What exactly happens or does not happen when you run your code?

 

[spydey]

What exactly happens or does not happen when you run your code?

Ok, so the whole code is this:

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

I am just currently interested in the code I originally posted. But maybe the first part is interfering with the second part (the part I am trying to get to work)???

When I run just the first part, it work great. It adds/removes the date from the correct cell as needed.

When I run the second part, the reason for this post, then I get the following error:

Sub or Function not defined.

It immediately goes into debug mode, highlights the Private Sub line yellow, and the 1st "VLookup" blue.

That is it.

Any thoughts?

-Spydey

P.S. In my original post, my code had "rng2 ....' I changed it to "rng1 ....." Nothing big, but didn't want to confuse anyone.

 

[spydey]

Ok, so if I dumb it down to this:

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

And try to run it, I get this error:

Run-Time Error '1004':
Method 'Range' of object '_Worksheet' failed


It has this line highlighted in yellow:

rng1.Offset(0, 7) = Application.WorksheetFunction.VLookup(rng1.Offset(0, 0), Range("Completed.Range_Completed"), 3, False)

.......

I am thinking that it has to do with my named range of Completed.Range_Completed ....

If I try and use a simple vlookup in-cell, and use the named range as my array parameter, I get a #REF error.

So if it is the named range of Completed.Range_Completed, then perhaps I have it setup incorrectly as a dynamic named range ...... ?????? ... which in turn would affect anything referencing the named range.

-Spydey

 

[spydey]

Ok, so I got it to work .... in part .....

I changed

rng1.Offset(0, 7) = Application.WorksheetFunction.VLookup(rng1.Offset(0, 0), [B]Range("Completed.Range_Completed")[/B], 3, False)

to

rng1.Offset(0, 6) = Application.WorksheetFunction.VLookup(rng1.Offset(0, 0), [B]Worksheets("Completed").Range("A2:G10")[/B], 3, False)

And it worked!!!

So I know now that the issue, with the simplified code, has to do with my dynamic named range ..... now I just have to figure out what , how, and why .......

Again, my dynamic named range Completed.Range_Completed should start with $A2 and go to $G#, the # being dynamic based upon the number of records found in column A of the same worksheet.

I am using this in my named range formula to determine the range end value:

=Completed!$A$2:INDEX(Completed!$A:$G,COUNTA(Completed!$A:$A))

But I feel like something is askew with it ....


Any thoughts?

-Spydey

 

[JoeMo]

I think you need to explicitly reference ALL worksheet functions in your formula. I've tried to tidy up the code you posted in post #3 and added the worksheetfunction modifiers, but have not tested this so it may still require some tweaks.

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

 

[spydey]

I think you need to explicitly reference ALL worksheet functions in your formula. I've tried to tidy up the code you posted in post #3 and added the worksheetfunction modifiers, but have not tested this so it may still require some tweaks.

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

Ok, I see what you did there on two front:

Cleaned up the repetitive coded

And called out each function

Ok, I got it.

I will give it a try.

Thanks for your input!

On a side note, when using:

rng1.Offset(0, 6) = Application.WorksheetFunction.VLookup(rng1.Offset(0, 0), Worksheets("Completed").Range("A2:G10"), 3, False)

if the numeric value found in Column A in Population is not present in column A in Completed, then it gives me a run time error.

I will give your code above a try and see if it solves that too.

Also, I have to figure out the issue with my Dynamic Named Range.

-Spydey

 

[spydey]

Got my dynamic named range corrected.

It was:

=Completed!$A$2:INDEX(Completed!$A:$G,COUNTA(Completed!$A:$A))

But should have been:

=Completed!$A$2:INDEX(Completed!$G:$G,COUNTA(Completed!$A:$A))

-Spydey

 

[spydey]

Ok, so I keep getting an error with the Application.WorksheetFunctions.

After reading a bit more and researching, I came across this:

https://www.mrexcel.com/forum/excel...-vlookup-error-vba-need-help.html#post1943185

I think that I can implement it somehow to get around the errors, and not rely on the Application.WorksheetFunction.

This is what I was thinking:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim rng As Range
    Application.EnableEvents = False
    For Each rng In Target
    X = Application.VLookup(rng, Range("Completed.Range_Completed"), 3, False)
        If IsError(X) Then
            Next rng
        If Len(rng.Value) = 0 Then
           rng.Offset(0, 4).ClearContents
           rng.Offset(0, 7).ClearContents
        ElseIf IsNumeric(rng.Value) = True Then
           rng.Offset(0, 4) = Date
           rng.Offset(0, 7) = X
        End If
    Next rng
    Application.EnableEvents = True


End Sub

But when I add a numeric value to a cell in Column A in the Population worksheet, ..... nothing happens.

??????

-Spydey

 

[JoeMo]

The code you posted in post #9 has some errors in it. Here's an untested revision, but before you try it, make sure you haven't inadvertently disabled events. To do this:
1. Open the VBE
2. Press ctrl+g to open the Immediate Window
3. In the Immediate Window enter: Application.EnableEvents = True
4. Press Enter

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim rng As Range
    Application.EnableEvents = False
    For Each rng In Target
        On Error Resume Next
        X = Application.VLookup(rng, Range("Completed.Range_Completed"), 3, False)
        On Error GoTo 0
        If IsError(X) Then
            Application.EnableEvents = True
            GoTo Nx
        End If
        If Len(rng.Value) = 0 Then
           rng.Offset(0, 4).ClearContents
           rng.Offset(0, 7).ClearContents
        ElseIf IsNumeric(rng.Value) = True Then
           rng.Offset(0, 4) = Date
           rng.Offset(0, 7) = X
        End If
Nx: Next rng
Application.EnableEvents = True
End Sub