H
Howard
If I enter a number on sheet 1 I want to know if it exist on any of the sheets in the array.
As is, if I enter 2468 on sheet 1 it runs through the array sheets but does not activate the number 2468 on sheet 4 which I am using for the test. The other sheets have no matching number in my test.
I can un-comment the MsgBox and it verifies "CheckNum" as the sheets are tested, but nothing happens on sheet 4 where there is indeed a true match to CheckNum.
If I run the Macro 8 sub in sheet 4 module it selects the 2468 as I would expect from both it and the change event.
If I try this line I get an yellow highlight error. Doesn't want the . in front of UsedRange.
.UsedRange.Find(What:=CheckNum).Activate
My ultimate goal is to produce a MsgBox on sheet 1 telling the user that the number exists on ...Sheet 3 $D:$6, for example or "Not Found".
Gotta get past this elusive Find code first.
Thanks,
Howard
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckNum As Long
Dim varSheets As Variant
Dim i As Long
CheckNum = Target.Value
varSheets = Array("Sheet2", "Sheet3", "Sheet4")
For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
'MsgBox CheckNum
UsedRange.Find(What:=CheckNum).Activate
End With
Next
End Sub
Option Explicit
Sub Macro8()
Dim CheckNum As Long
CheckNum = 2468
With Sheets("Sheet4")
UsedRange.Find(What:=CheckNum).Activate
End With
End Sub
As is, if I enter 2468 on sheet 1 it runs through the array sheets but does not activate the number 2468 on sheet 4 which I am using for the test. The other sheets have no matching number in my test.
I can un-comment the MsgBox and it verifies "CheckNum" as the sheets are tested, but nothing happens on sheet 4 where there is indeed a true match to CheckNum.
If I run the Macro 8 sub in sheet 4 module it selects the 2468 as I would expect from both it and the change event.
If I try this line I get an yellow highlight error. Doesn't want the . in front of UsedRange.
.UsedRange.Find(What:=CheckNum).Activate
My ultimate goal is to produce a MsgBox on sheet 1 telling the user that the number exists on ...Sheet 3 $D:$6, for example or "Not Found".
Gotta get past this elusive Find code first.
Thanks,
Howard
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim CheckNum As Long
Dim varSheets As Variant
Dim i As Long
CheckNum = Target.Value
varSheets = Array("Sheet2", "Sheet3", "Sheet4")
For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
'MsgBox CheckNum
UsedRange.Find(What:=CheckNum).Activate
End With
Next
End Sub
Option Explicit
Sub Macro8()
Dim CheckNum As Long
CheckNum = 2468
With Sheets("Sheet4")
UsedRange.Find(What:=CheckNum).Activate
End With
End Sub