MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combining If Not Is Nothing


Posted by sid on October 16, 2001 9:48 AM

Combining If Not Is Nothing
I am trying to use the code below to check what column a value is in
It partially works but, gets confused over close values e.g. K1 is in col1, but shows as col3 after K100 is checked (which is in col3)
This is what I have cobbled together from the board, can anyone tidy it please. Or is there a better way to get this information.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Columns(9)) Is Nothing Then
Dim rng1 As Range
Set rng1 = Worksheets("INDEX").Columns(43).Find(Target.Value)
If Not rng1 Is Nothing Then Target.Offset(0, 8).Value = 1
End If
If Not Intersect(Target, Columns(9)) Is Nothing Then
Dim rng2 As Range
Set rng2 = Worksheets("INDEX").Columns(44).Find(Target.Value)
If Not rng2 Is Nothing Then Target.Offset(0, 8).Value = 2
End If
If Not Intersect(Target, Columns(9)) Is Nothing Then
Dim rng3 As Range
Set rng3 = Worksheets("INDEX").Columns(45).Find(Target.Value)
If Not rng3 Is Nothing Then Target.Offset(0, 8).Value = 3
End If


Posted by cpod on October 16, 2001 11:00 AM

You just need to add an arguement to your find statement to look only for whole cell contents:

Set rng1 = Worksheets("INDEX").Columns(43).Find(Target.Value,lookat: = xlwhole)

Posted by sid on October 16, 2001 1:46 PM

Thanx cpod,
butI don't think this is what I am looking for. To be clearer; I have numerous columns on an INDEX sheet and I want to find which column the target cell value is in and return a value to the target offset cell - so say K1 is entered in the target cell and is in the 2nd column on the index sheet, value 2 would be put in target offset cell.

Posted by Sid on October 17, 2001 2:17 PM

Cheers cpod - me being thick, finally saw what u meant