Change event code Find .Activate does not activate Target.value

[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

 

[Claus Busch]

Hi Howard,

Am Sat, 23 Nov 2013 22:46:23 -0800 (PST) schrieb Howard:

.UsedRange.Find(What:=CheckNum).Activate

set c = .UsedRange(...

try:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CheckNum As Long
Dim varSheets As Variant
Dim i As Long
Dim c As Range

CheckNum = Target.Value

varSheets = Array("Sheet2", "Sheet3", "Sheet4")

For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
Set c = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)
If Not c Is Nothing Then
Application.Goto c
End If
End With
Next
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 24 Nov 2013 09:38:51 +0100 schrieb Claus Busch:

If Not c Is Nothing Then
Application.Goto c
End If

change it to:

If Not c Is Nothing Then
Application.Goto c
Exit For
End If


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Sat, 23 Nov 2013 22:46:23 -0800 (PST) schrieb Howard:

If I try this line I get an yellow highlight error. Doesn't want the . in front of UsedRange.

.UsedRange.Find(What:=CheckNum).Activate

no, the error comes because you only can select or activate a cell on a
active sheet. You first have to activate the sheet then you can activate
the cell.


Regards
Claus B.

 

[Howard]

try:



Private Sub Worksheet_Change(ByVal Target As Range)



Dim CheckNum As Long

Dim varSheets As Variant

Dim i As Long

Dim c As Range



CheckNum = Target.Value



varSheets = Array("Sheet2", "Sheet3", "Sheet4")



For i = LBound(varSheets) To UBound(varSheets)

With Sheets(varSheets(i))

Set c = .UsedRange.Find(What:=CheckNum, LookIn:=xlValues)

If Not c Is Nothing Then

Application.Goto c

End If

End With

Next

End Sub





Regards

Claus B.

Works great, I did make the small change you posted.

Thank Claus.

Howard