multiple Target Ranges

[Mohamedazees]

Dear Sir,

I need to apply target for multiple ranges in a same sheet refer the below code i have applied Target for Column "G" like wise I need to target Column N & T accordingly do VLOOKUP's like below with difference ranges
Thanks in advance
Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, [G:G]) Is Nothing Or Target.Count > 1 Then Exit Sub
'Target.Offset(, -1) = Time
Target.Offset(, 1) = Application.VLookup(Target, Sheet10.Range("A:B"), 2, False)

End Sub

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If (Intersect(Target, [G:G]) Is Nothing) And (Intersect(Target, [N:N]) Is Nothing) And (Intersect(Target, [T:T]) Is Nothing) Then Exit Sub
'Target.Offset(, -1) = Time
Target.Offset(, 1) = Application.VLookup(Target, Sheet10.Range("A:B"), 2, False)

End Sub

 

[Mohamedazees]

Dear Sir,
Thanks for your prompt reply
Sorry I fell I am not Properly explain my requirement

VBA to do :

1. When Entering value in Column Range "G" then offset column "H" will populate the Lookup Value
2. When Entering value in Column Range "N" then offset column "W" will populate the Lookup Value ( Another Lookup Value)
3. When Entering value in Column Range "T" then offset column "X" will populate the Lookup Value ( Another Lookup Value)

Thanks for your guldens & supports

 

[Joe4]

Since the number of columns you are offsetting by is not the same for all three columns, I recommend you split them up into their own sections.

I have done the first one for you, you just need to enter the appropriate VLOOKUP formula for the other two.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Count > 1 Then Exit Sub

'   Column G check
    If Not (Intersect(Target, [G:G]) Is Nothing) Then
       Target.Offset(, 1) = Application.VLookup(Target, Sheet10.Range("A:B"), 2, False)
    End If
   
'   Column N check
    If Not (Intersect(Target, [N:N]) Is Nothing) Then
'       Enter your vlookup formula here
    End If
   
'   Column T check
    If Not (Intersect(Target, [T:T]) Is Nothing) Then
'       Enter your vlookup formula here
    End If

End Sub

 

[Alex Blakenburg]

Hopefully I haven't confused you doing it this way.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim Target As Range
    Set Target = ActiveCell
    
    Dim rng As Range
    Set rng = Range("G:G,N:N,T:T")
    
    If Not Intersect(Target, rng) Is Nothing And Target.Count = 1 Then
        Select Case Target.Column
            Case Columns("G").Column
                Debug.Print "G Lookup goes here"
            Case Columns("N").Column
                Debug.Print "N Lookup goes here"
            Case Columns("T").Column
                Debug.Print "T Lookup goes here"
        End Select
    End If

End Sub

 

[Mohamedazees]

Dear Sir,
Compile Error

Duplicate Declaration in Current Scope

 

[Joe4]

Dear Sir,
Compile Error

Duplicate Declaration in Current Scope

Did you put two procedures named "Worksheet_Change" in the same module? That is not allowed.
The code that we gave you is meant to replace what you currently have.

 

[Mohamedazees]

Since the number of columns you are offsetting by is not the same for all three columns, I recommend you split them up into their own sections.

I have done the first one for you, you just need to enter the appropriate VLOOKUP formula for the other two.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Count > 1 Then Exit Sub

'   Column G check
    If Not (Intersect(Target, [G:G]) Is Nothing) Then
       Target.Offset(, 1) = Application.VLookup(Target, Sheet10.Range("A:B"), 2, False)
    End If
  
'   Column N check
    If Not (Intersect(Target, [N:N]) Is Nothing) Then
'       Enter your vlookup formula here
    End If
  
'   Column T check
    If Not (Intersect(Target, [T:T]) Is Nothing) Then
'       Enter your vlookup formula here
    End If

End Sub

Excellent & Thank of Lot it works perfect

 

[Alex Blakenburg]

Oops if you are using mine remove the 1st 2 lines, I tested it as a normal Sub and had to declare and set target.

So the code should be:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("G:G,N:N,T:T")
   
    If Not Intersect(Target, rng) Is Nothing And Target.Count = 1 Then
        Select Case Target.Column
            Case Columns("G").Column
                Debug.Print "G Lookup goes here"
            Case Columns("N").Column
                Debug.Print "N Lookup goes here"
            Case Columns("T").Column
                Debug.Print "T Lookup goes here"
        End Select
    End If

End Sub

 

[Joe4]

Excellent & Thank of Lot it works perfect

You are welcome.
Glad we were able to help!