Go to cell in a column that matches the value of another cell

[kivikatz]

Hi all. I would like to add VBA code that scrolls to cell in a column which matches the value of another cell. For instance if a cell in Column A1:A100 matches cell B1, I would like to scroll down to the cell in Column A. Alternatively, is it possible if I manually click on cell B1 that it will scroll down to the matching cell in Column A.

I've searched around but cannot seem to find a solution. Thanks for all your help.

 

[Fluff]

Ok, try

      Set Fnd = Range("A:A").Find(Target.Value, , xlValues, xlPart, , , False, , False)

 

[petertenthije]

The values are dates, and B1 is an exact match of the value found in column A. Thanks.

Are you sure the matches are identical? Right down to the split-second? Is it possible that due to formatting they only look alike, but that there are differences?

 

[kivikatz]

I think so, but that's a good question. I've attached a sample sheet. Maybe I am missing something. Could it be that B1 uses XLOOKUP to find values in column A? Thanks.

Analytics.xlsm
	A	B	C
1	Wed 01/08/20	Mon 03/16/20
2	Thu 01/09/20
3	Fri 01/10/20
4	Mon 01/13/20
5	Tue 01/14/20
6	Wed 01/15/20
7	Thu 01/16/20
8	Fri 01/17/20
9	Mon 01/20/20
10	Tue 01/21/20
11	Wed 01/22/20
12	Thu 01/23/20
13	Fri 01/24/20
14	Mon 01/27/20
15	Tue 01/28/20
16	Wed 01/29/20
17	Thu 01/30/20
18	Fri 01/31/20
19	Mon 02/03/20
20	Tue 02/04/20
21	Wed 02/05/20
22	Thu 02/06/20
23	Fri 02/07/20
24	Mon 02/10/20
25	Tue 02/11/20
26	Wed 02/12/20
27	Thu 02/13/20
28	Fri 02/14/20
29	Mon 02/17/20
30	Tue 02/18/20
31	Wed 02/19/20
32	Thu 02/20/20
33	Fri 02/21/20
34	Mon 02/24/20
35	Tue 02/25/20
36	Wed 02/26/20
37	Thu 02/27/20
38	Fri 02/28/20
39	Mon 03/02/20
40	Tue 03/03/20
41	Wed 03/04/20
42	Thu 03/05/20
43	Fri 03/06/20
44	Mon 03/09/20
45	Tue 03/10/20
46	Wed 03/11/20
47	Thu 03/12/20
48	Fri 03/13/20
49	Mon 03/16/20		TRUE
50	Tue 03/17/20
51	Wed 03/18/20
52	Thu 03/19/20
53	Fri 03/20/20
54	Mon 03/23/20
55	Tue 03/24/20
56	Wed 03/25/20
57	Thu 03/26/20
58	Fri 03/27/20		TRUE
59	Mon 03/30/20		TRUE
60	Tue 03/31/20		TRUE
61	Wed 04/01/20		TRUE
62	Thu 04/02/20		TRUE
63	Fri 04/03/20		TRUE
64	Mon 04/06/20
65	Tue 04/07/20
66	Wed 04/08/20
67	Thu 04/09/20
68	Fri 04/10/20
69	Mon 04/13/20		TRUE
70	Tue 04/14/20		TRUE
71	Wed 04/15/20
72	Thu 04/16/20
73	Fri 04/17/20
74	Mon 04/20/20
75	Tue 04/21/20
76	Wed 04/22/20
77	Thu 04/23/20
78	Fri 04/24/20
79	Mon 04/27/20
80	Tue 04/28/20
81	Wed 04/29/20
82	Thu 04/30/20
83	Fri 05/01/20		TRUE
84	Mon 05/04/20
85	Tue 05/05/20
86	Wed 05/06/20
87	Thu 05/07/20
88	Fri 05/08/20
89	Mon 05/11/20
90	Tue 05/12/20
91	Wed 05/13/20
92	Thu 05/14/20
93	Fri 05/15/20
94	Mon 05/18/20
95	Tue 05/19/20
96	Wed 05/20/20
97	Thu 05/21/20
98	Fri 05/22/20
99	Mon 05/25/20
100	Tue 05/26/20
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(XLOOKUP(TRUE,C1:C100,A1:A100,"",0)>TODAY(),"",XLOOKUP(TRUE,C1:C100,A1:A100,"",0))
A2:A100	A2	=IF(TEXT(A1+1,"ddd")="Sat",A1+3,IF(TEXT(A1+1,"ddd")="Sun",A1+3,A1+1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	=$B$1	text	NO

 

[Fluff]

Did you try the mod I suggested in post#11?

 

[kivikatz]

I did. Still not sure what I am doing wrong. Thanks.

 

[Fluff]

not sure what I am doing wrong.

Probably nothing at all, working with dates & VBA can be a right PITA, especially for those of us who are not in the US.

try it like this

      Set Fnd = Range("A:A").Find(Target.Text, , xlValues, xlPart, , , False, , False)

 

[kivikatz]

Wow! That works great. Thanks for all your help.

By the way, can you suggest a VBA book or online course that's geared for newbies?

 

[Fluff]

You're welcome & thanks for the feedback.

I've never read any books or done tutorials so don't really know.

 

[rkayz]

Sub Macro1()

' Find the last row number in column A.
    Dim lastRow As Long
    With Sheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

' Find the value you are looking for
    lookupvalue = Worksheets("Sheet1").Range("B1").Value
 
' Select the range you are looking in
    lookuprange = Worksheets("Sheet1").Range("A1:A" & lastRow)
 
' Find the first matching value, and return the row number
' If there is no matching number, the macro will jump to "ErrorMessageBox"
    On Error GoTo ErrorMesageBox
    FirstMatchRowNumber = WorksheetFunction.Match(lookupvalue, lookuprange, 0)

' Go to the applicable row.
    Worksheets("Sheet1").Range("A" & FirstMatchRowNumber).Select
Exit Sub



' In case of an unknown number, the macro will show an message box
ErrorMesageBox:
    MsgBox "The number entered in B1 is not known in A1:A" & lastRow
    Exit Sub
End Sub

Hey Peter,
I used your code as above, but I am not getting the desired results, I am getting some columns above and not the exact ones....when I print out the FirstMatchRowNumber, it gives out row#5, whereas it should be 8 from K1

Private Sub Worksheet_Activate()

' Find the last row number in column A.
    Dim lastRow As Long
    With Sheets("T2")
        lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
    End With

' Find the value you are looking for
    lookupvalue = Worksheets("T2").Range("K1").Value
 
' Select the range you are looking in
    lookuprange = Worksheets("T2").Range("K4:K26" & lastRow)
 
' Find the first matching value, and return the row number
' If there is no matching number, the macro will jump to "ErrorMessageBox"
    On Error GoTo ErrorMesageBox
    FirstMatchRowNumber = WorksheetFunction.Match(lookupvalue, lookuprange, 0)
MsgBox prompt:=FirstMatchRowNumber
' Go to the applicable row.
    Worksheets("T2").Range("K" & FirstMatchRowNumber).Select
Exit Sub



' In case of an unknown number, the macro will show an message box
ErrorMesageBox:
    MsgBox "The number entered in B1 is not known in A1:A" & lastRow
    Exit Sub

End Sub
[/Code}