Match a cell in a range to a value from another sheet and select r

[Johno]

I have 2 worksheets A & B. I have a variable cell in sheet A which I change
from 1 - 26. I have a permanent range in a column on sheet B of 1 - 26. I
need a macro that will select the row in sheet B that matches the variable in
Sheet A. If I type 5 in Sheet A, the ROW with 5 on sheet B is selected.

 

[Shasur]

You can add the following code in your Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iVal As Integer
Dim rFnd As Range
If Target.Address = "$A$2" Then
If IsNumeric(Target.Value) Then
iVal = CInt(Target.Value)
Set rFnd = Worksheets(2).Range("A:A").Find(what:=iVal,
LookIn:=xlValues, lookat:=xlWhole)
If Not rFnd Is Nothing Then
Worksheets(2).Activate
Worksheets(2).Rows(rFnd.Row).Select
End If
End If
End If

End Sub

The above code checks value in Sheet1!A2 against Sheet2 Entire Column A and
selects the Row containing the value

Cheers
Shasur

 

[marcus]

Hi Johno

If you set up a worksheet change event in the sheet tab module should
do the trick.
This assumes your Sheet B is called Sheet2 and the cell to change is
in A1 of Sheet1.

You can modify to suit. Good Luck

Marcus



Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRw As Integer
Dim rngB As Range, rngA As Range

LastRw = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Set rngA = Range("A1")
Set rngB = Sheets("Sheet2").Range("A2:A" & LastRw)

If Target.Address = "$A$1" Then

For Each cellB In rngB
Found = False
For Each cellA In rngA
If cellB.Value = cellA.Value Then
Found = True
Sheets("Sheet2").Activate
cellB.EntireRow.Select
End If
Next

Next
End If

End Sub

 

[Johno]

I have 2 worksheets A & B. I have a variable cell in sheet A which I change
from 1 - 26. I have a permanent range in a column on sheet B of 1 - 26. I
need a macro that will select the row in sheet B that matches the variable in
Sheet A. If I type 5 in Sheet A, the ROW with 5 on sheet B is selected.

Thanks Guys for your help. I ended up doing this and it worked a treat:

Dim rngB As Range, rngA As Range
Set rngA = Range("A1")
Set rngB = Sheets("Sheet2").Range("A1:A26")
For Each cellB In rngB
Found = False
For Each cellA In rngA
If cellB.Value = cellA.Value Then
Found = True
Sheets("Sheet2").Activate
cellB.EntireRow.Select
End If
Next
Next
End Sub