Vba VLookup from another worksheet on VBA on cell change

Question

I have some data validation on Column D, and when option 1 is chosen, it should run a makro that vlookup another sheet in the same file and input data in column G (same row).

This is what I have so far:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(True, True) = Sheets("SERVICES BREAKDOWN").Range("$D$15") Then
        Select Case Target
            Case "ORIGIN CHARGE"
              Sheets("SERVICES BREAKDOWN").Range("$G$15").Value = Application.WorksheetFunction.VLookup(Sheets("SERVICES BREAKDOWN").Range("$D$15"), Sheets("INITIAL").Range("D15:K22"), 4, False)
                      Case Else
                'Do nothing
        End Select
    End If

This code wont return an error , still it does nothing, anyone can guess how to fix?

Scott Craner · Accepted Answer · 2019-04-01 19:57:57Z

2

Target.Address(True, True) = Sheets("SERVICES BREAKDOWN").Range("$D$15")

Is going to try to compare the value in D15 to the address of target and unless D15 has its own address in it, this will never be true.

Range().Address returns a string, so use:

If Target.Address(1,1) = "$D$15" then

Or as is popular:

If Not Intesect(Target,Range("$D$15")) Is Nothing Then

edited Apr 1, 2019 at 19:57

answered Apr 1, 2019 at 19:52

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ADrex Over a year ago

Should I input : 'Target.Address(1,1) = "$D$15" ' before the Select Case?

Collectives™ on Stack Overflow

Vba VLookup from another worksheet on VBA on cell change

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related