Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match a cell in a range to a value from another sheet and select r
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match a cell in a range to a value from another sheet and selectr
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match a cell in a range to a value from another sheet and select r
"Johno" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking sheet - Formula Match Exact or select next row and cell | Excel Discussion (Misc queries) | |||
Can't Select Range off sheet from inputbox | Excel Discussion (Misc queries) | |||
select a range, copy it to a new sheet | Excel Discussion (Misc queries) | |||
Can select range of sheet | Excel Programming | |||
Select Sheet then Select Range | Excel Programming |