TryhisBest
TryhisBest

Reputation: 23

Matching a cell on another sheet

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

Answers (1)

Stavros Jon
Stavros Jon

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

Related Questions