Reputation: 23
I made the code below to match a cell from a cell on another sheet so I can then offset and paste more data where needed.
The code seems to only be able to find the top row of the sheet not the cell with the value.
Sub Find_select()
Dim matchValue As Variant, FindCell As Long
Sheets("Booking Sheet").Activate
matchValue = Worksheets("Booking Sheet").Range("BE5").Value
FindCell = WorksheetFunction.Match(matchValue, Worksheets("Data Storage").Range("A2:A10000"), 0)
Sheets("Data Storage").Activate
ActiveSheet.Cells(FindCell).Select
End Sub
Upvotes: 1
Views: 668
Reputation: 1697
The minimum change you would need to do to get the result you want would be this:
ActiveSheet.Cells(FindCell,1).Select
or ActiveSheet.Cells(FindCell,"A").Select
Basically when you use the .cells
collection you need to specify both the row index and the column index for VBA to understand which cell you are referring to.
This ActiveSheet.Cells(FindCell)
refers to the first cell of the column whose index is FindCell
, so for example, if FindCell=10
then your code would select cell J1
.
Having said that, I would also avoid using the .Activate
method and the ActiveSheet
object when they are not specifically needed and i would use explicit references instead.
Dim matchValue As Variant, FindCell As Long
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = ThisWorkbook.Worksheets("Booking Sheet")
Set sht2 = ThisWorkbook.Worksheets("Data Storage")
matchValue = sht1.Range("BE5").value
FindCell = Application.WorksheetFunction.Match(matchValue, sht2.Range("A2:A10000"), 0)
sht2.Cells(FindCell, "A").Select
Also I'm not sure why you want to use the .select
method but chances are there's a better way of achieving the same result without it.
Upvotes: 1