1

I'm trying to get a VBA code together that can take the date from cell B5 on one sheet and locate the exact match from a column on a different sheet, then select that as the active cell. any ideas?

Heres what i've tried;

Sub find()
    Sheets("Details").Select
    Range("B5").Select
    rngY = ActiveCell.Value

    Sheets("Calcs").Select
    Columns("C:C").Select
    Selection.find(What:=rngY, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
      
    ActiveCell.Select
End Sub
CallumDA
  • 12,025
  • 6
  • 30
  • 52
Josh
  • 27
  • 6

1 Answers1

1

Something like this should work for you:

Public Sub FindInSheet2()
    Dim r As Range, lookupVal As Variant
    
    lookupVal = ThisWorkbook.Worksheets("Details").Range("B5").Value
    Set r = ThisWorkbook.Worksheets("Calcs").Range("C:C").Find(What:=lookupVal, LookAt:=xlWhole, LookIn:=xlValues)
    
    If Not r Is Nothing Then
        ThisWorkbook.Worksheets("Calcs").Activate
        r.Select
    Else
        MsgBox "Lookup not found", vbOKOnly
    End If
End Sub

The main difference between this code and yours is that I haven't relied on ActiveCell or Selection. It's better to be specific about which ranges you are working with. For more info, see How to avoid using Select in Excel VBA

CallumDA
  • 12,025
  • 6
  • 30
  • 52
  • I've made the adjustments to you're code you suggested but it still doesn't seem to be working any suggestions? – Josh Nov 11 '21 at 11:57
  • I tested this code and it worked well for me. Where are you getting an error? – CallumDA Nov 11 '21 at 11:58
  • No error shows up, it just isn't selecting the cell – Josh Nov 11 '21 at 11:58
  • I've updated the code (try it and let me know). I think there's a good chance your lookup isn't being found. That could be due to differences in formats. You might need to add back in some of the conditions from your .Find(), like `MatchCase=False` – CallumDA Nov 11 '21 at 12:01
  • I keep getting lookup not found now, i cant understand why, the value I'm trying to lookup on the second sheet is identical – Josh Nov 11 '21 at 12:04
  • What is your lookup value? Perhaps update your question with a few screenshots - B5 and C:C – CallumDA Nov 11 '21 at 12:08
  • I just went over the cell with format painter just to make sure it was identical and I'm still getting the same message – Josh Nov 11 '21 at 12:09
  • There are many reasons two values can be different, format doesn't really change that much. Please tell me what your lookup value in B5 is? Screenshots help a lot – CallumDA Nov 11 '21 at 12:11
  • Oh it's a date... I bet they are different. Convert them to numbers and you might see the difference. I tested the code with a simple short date and it worked fine – CallumDA Nov 11 '21 at 12:15
  • I've convertedd them to numbers but im still getting no lookup found – Josh Nov 11 '21 at 12:17
  • so I've found the issue, the second date is referenced from a different cell which it doesn't like for some reason. If I put the second date just as its own number your code works fine, thank you for all your help i really appreciate it – Josh Nov 11 '21 at 12:24
  • hate to be a bit cheeky but you don't know a way around this do you – Josh Nov 11 '21 at 12:27
  • @Josh. Yes - just add `LookIn:=xlValues` as one of the arguments to `Range.Find` (see updated answer). If you found my answer helpful, please consider upvoting and accepting it – CallumDA Nov 11 '21 at 12:56
  • That's fixed it, really appreciate it – Josh Nov 11 '21 at 13:07