Find a specific date in Excel (using VBA) and paste into the cell next to it-CodePudding

I am struggling to find a way to find a specific cell in VBA and select the cell to the right of it.

Here is the code, with the two relevant commands in text form. I have tried different search methods, but none are able find the date in the list. Picture one is of the "Overview" sheet, picture two is the "Data" sheet.

Sub Save_Button()
    Range("D6:F6").Select
    Selection.Copy
    
    Dim varDate As Variant
    varDate = Range("C6").Value

    Sheets("Data").Select
        
    'Find the same date as the one saved in varDate in the sheet "Data"
    'Select the cell next to it

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Overview").Select
    Application.CutCopyMode = False
    Range("D6").Select
End Sub

CodePudding user response：

Finding dates in worksheets can be difficult. underlying the date is a formatted number. In your code you are essentially trying to find a variant string as a number and that's not going to work.

You need to format the string as a date type (CDate) so that you can find this within the worksheet

Code you should be able to adapt is below:

Sub Foo()
    Dim ws As Worksheet: Set ws = ActiveSheet 'ThisWorkbook.Worksheets("Blah")
    
    With ws
        .Range("D6:F6").Copy
        
        Dim sDate As String: sDate = .Range("G11").Value
        Dim srcDate As Date: srcDate = CDate(Right(sDate, Len(sDate) - InStr(sDate, ",") - 1))
        
        Dim srcRange As Range: Set srcRange = .Cells.Find(srcDate)
        If Not srcRange Is Nothing Then
            srcRange.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    End With
    Application.CutCopyMode = False

End Sub

Generally, you should avoid select where possible and assign the specific worksheets you want to work with in the code (it will save you headaches in the future)

CodePudding user response：

I think this is the code you want

Dim foundCell As Range    
Set foundCell = Cells.Find(varDate, , , xlWhole)    
If foundCell Is Nothing Then
        MsgBox varDate & " not found"
Else
        foundCell.Offset(0, 1).Select
End If

Just put it in where you have the comment in your code. This code assumes that all your dates are actually dates on the worksheets, not text.