Use VLookup in VBA everytime a cell in one of 4 columns is changed in Excel 2007

Hi all.

I just posted a question about using VLookup and that was answered, but now we want to try and change our Excel file to use VBA. We want to be able to have the Vlookup function in my module1 run and populate the same cell where the end user entered 1 the number.

So, using the attached Excel file as an example:

Let's say I enter 1 in column F, I want the VLookup to run and replace my 1 with Windowsills. If I enter 3 in column F, then I want the VLookup to run and replace my 3 with Bath Tile.

If I enter 3 in column G, then I want the VLookup to run and replace my 3 in column G with Gold in column G.

As you will see, I have sort of started and I get it to replace but then I get the attached error. In my code I have it hard coded to be cell F4, but I would want it for any row in the entire column (F, G etc.)

I'm using the Worksheet_Change(ByVal Target As Range) function to sense when a cell is changed. In my attached worksheet I have code in Sheet1 and in Module 1.

Thank you in advance.
Example.xlsm
Error.bmp
Sim1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
Have you considered Validation list as per attached?
Example.xlsm
0
SteveCommented:
For your code:

Sub MySub()

Dim result As String
If IsNumeric(Worksheets("Sheet1").Range("F4").Value) Then
result = Application.WorksheetFunction.VLookup(Worksheets("Sheet1").Range("F4").Value, Worksheets("Sheet1").Range("I4:L17"), 3, False)

Worksheets("Sheet1").Range("F4") = result
End If
End Sub

Open in new window


1) needed .value after ("F4") to get the value out of the cell (though this was not the issue).
2) need to check for numeric in the cell F4 or changing it to the word will trigger the event again and the lookup fails.

you either need to disable events before making sheet changes (so it doesn't try to lookup again) or apply a logic to stop the change occurring again.
Example.xlsm
0
Sim1980Author Commented:
Ah I see now. Thank you!

Now, how do I do it so it does it for any row in column F starting in row 4, not just F4, but F5, F6 up until the end user stops.
0
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

byundtMechanical EngineerCommented:
I modified the code in the a attached file to turn events off and to watch F4:F33 for entries.
Example.xlsm
0
byundtMechanical EngineerCommented:
Code in Worksheet code pane
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel As Range, targ As Range
Set targ = Range("F4:F33")
Set targ = Intersect(targ, Target)
If Not targ Is Nothing Then
    Application.EnableEvents = False
    For Each cel In targ.Cells
        Call MySub(cel)
    Next
    Application.EnableEvents = True
End If
End Sub

Open in new window

Code in Module1
Sub MySub(cel As Range)

Dim result As Variant

result = Application.VLookup(cel, Worksheets("Sheet1").Range("I4:L17"), 3, False)

If Not IsError(result) Then cel.Value = result

End Sub

Open in new window

0
Sim1980Author Commented:
Thanks byundt. But what if there could be more than F33, maybe 50 I don't know in advance.

The end user could choose to stop at 32 or 33 or 35, it will depend on how many work orders they enter in a day, week, month which will vary.
0
byundtMechanical EngineerCommented:
Besides working on a range of cells in column F, I changed MySub to use Application.VLookup instead of Application.WorksheetFunction.VLookup. You lose the IntelliSense, but avoid the run-time error if VLOOKUP returns an error value.

If using using Application.WorksheetFunction.VLookup, you need to enclose it in error handling statements to avoid the run-time error:
Dim result As Variant
On Error Resume Next
result = Application.WorksheetFunction.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 3, False)
On Error GoTo 0
If Not IsError(result) Then cel.Value = result

Open in new window

Rather than add the error handling complexity, I prefer to use Application.VLookup.
0
byundtMechanical EngineerCommented:
There is no harm in allowing for more values that you think anybody will use. Just change the statement that assigns targ to a Range:
Set targ = Range("F4:F1000")      'Allows up to row 1000 for work orders
0
Sim1980Author Commented:
Ok, I gave it a max of 65536.

Now, I changed it to allow for the same did you did for column F, to be applied to column D and column G. But it's not working. How do I include an if, elseif statement to capture what column the end user is in and then go from there as far as the VLookup?

Here's what I did:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel_Sidemark As Range, targ_Sidemark As Range
Set targ_Sidemark = Range("D4:D65536")

Set targ_Sidemark = Intersect(targ_Sidemark, Target)
If Not targ_Sidemark Is Nothing Then
    Application.EnableEvents = False
    For Each cel_Sidemark In targ_Sidemark.Cells
        Call MySub_Sidemark(cel_Sidemark)
    Next
    Application.EnableEvents = True
End If



Dim cel_JobType As Range, targ_JobType As Range
Set targ_JobType = Range("F4:F65536")

Set targ_JobType = Intersect(targ_JobType, Target)
If Not targ_JobType Is Nothing Then
    Application.EnableEvents = False
    For Each cel_JobType In targ_JobType.Cells
        Call MySub_JobType(cel_JobType)
    Next
    Application.EnableEvents = True
End If


Dim cel_Installer As Range, targ_Installer As Range
Set targ_Installer = Range("G4:G65536")

Set targ_Installer = Intersect(targ_Installer, Target)
If Not targ_Installer Is Nothing Then
    Application.EnableEvents = False
    For Each cel_Installer In targ_Installer.Cells
        Call MySub_Installer(cel_Installer)
    Next
    Application.EnableEvents = True
End If



End Sub

Open in new window


And Module1:

Sub MySub_Sidemark(cel_Sidemark As Range)

Dim result_Sidemark As Variant
On Error Resume Next
result_Sidemark = Application.WorksheetFunction.VLookup(cel_Sidemark.Value, Worksheets("Sheet1").Range("I4:L17"), 2, False)
On Error GoTo 0
If Not IsError(result_Sidemark) Then cel_Sidemark.Value = result_Sidemark


End Sub

Sub MySub_JobType(cel_JobType As Range)


Dim result_JobType As Variant
On Error Resume Next
result_JobType = Application.WorksheetFunction.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 3, False)
On Error GoTo 0
If Not IsError(result_JobType) Then cel_JobType.Value = result_JobType



End Sub

Sub MySub_Installer(cel_Installer As Range)

Dim result_Installer As Variant
On Error Resume Next
result_Installer = Application.WorksheetFunction.VLookup(cel_Installer.Value, Worksheets("Sheet1").Range("I4:L17"), 4, False)
On Error GoTo 0
If Not IsError(result_Installer) Then cel_Installer.Value = result_Installer


End Sub

Open in new window

0
byundtMechanical EngineerCommented:
I assumed that you would enter a number in column D, but want values in columns D, F and G that correspond to that number.

Code for Sheet1 code pane
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cel_Sidemark As Range, targ_Sidemark As Range
Set targ_Sidemark = Range("D4:D65536")

Set targ_Sidemark = Intersect(targ_Sidemark, Target)
If Not targ_Sidemark Is Nothing Then
    Application.EnableEvents = False
    For Each cel_Sidemark In targ_Sidemark.Cells
        Call MySub(cel_Sidemark)
    Next
    Application.EnableEvents = True
End If
End Sub

Open in new window


Code for Module1
Sub MySub(cel As Range)

Dim result As Variant, result_Installer As Variant, result_Sidemark As Variant
On Error Resume Next
result_Sidemark = Application.WorksheetFunction.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 2, False)
result = Application.WorksheetFunction.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 3, False)
result_Installer = Application.WorksheetFunction.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 4, False)
On Error GoTo 0

If Not IsError(result_Sidemark) Then cel.Value = result_Sidemark
If Not IsError(result) Then cel.Offset(0, 2).Value = result
If Not IsError(result_Installer) Then cel.Offset(0, 3).Value = result_Installer

End Sub

Open in new window

Example3Q28232957.xlsm
0
Sim1980Author Commented:
That's the thing, we don't want it to work that way. It would have to be individual cells because there are times where we have to jump around and not automatically get all the data from rows J, K, L. Sometimes we would need the data for J5, K2 and L4. So the end user would enter in D4 = 5, F4 = 2 and G4 = 4.

And sometimes they won't enter anything in D4, because they are still waiting but they know F4 and G4 etc.
0
byundtMechanical EngineerCommented:
I'll make the macro modifications for you, but suggest that you take a look a data validation dropdowns instead. See the attached file (which turned off the macros)
Example3Q28232957.xlsm
0
byundtMechanical EngineerCommented:
I didn't catch that you used Application.WorksheetFunction.VLookup. I changed them back to Application.VLookup.
Sub MySub(cel As Range)

Dim result As Variant, result_Installer As Variant, result_Sidemark As Variant
On Error Resume Next
result_Sidemark = Application.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 2, False)
result = Application.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 3, False)
result_Installer = Application.VLookup(cel.Value, Worksheets("Sheet1").Range("I4:L17"), 4, False)
On Error GoTo 0

If Not IsError(result_Sidemark) Then cel.Value = result_Sidemark
If Not IsError(result) Then cel.Offset(0, 2).Value = result
If Not IsError(result_Installer) Then cel.Offset(0, 3).Value = result_Installer

End Sub

Open in new window


In the attached workbook, you enter a number in column D, and it pre-populates columns D, F and G in the same row. But columns D, F and G have data validation dropdowns, so the user can pick from different rows if he likes. This way, you get the flexibility of mixing and matching together with the productivity improvement of making one selection and having 3 cells populated.
Example3Q28232957.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sim1980Author Commented:
Great!

Thanks!
0
byundtMechanical EngineerCommented:
The data validations allow for you to increase the number of Builders, JobTypes and Installers. Just keep adding data in the appropriate columns, and the data validation dropdowns will pick up the new entries.

I used dynamic named ranges as the source for these dropdowns. As written, they allow up to 97 entries. This could be modified to allow even longer pick lists using the Name Manager. You should see a formula like:
=Sheet1!$J$4:INDEX(Sheet1!$J$4:$J$100,COUNTA(Sheet1!$J$4:$J$100))
Just change the reference to row 100 to increase or decrease the maximum possible number of choices in the dropdown.

When entering data in columns J:L, make sure that you don't put any blank cells between the first and last choice. Any blanks will throw off the COUNTA used to set the extent of the dropdown source.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.