CODEWITHSUNDEEP
excelvba
Ernesto Paredes
Ernesto Paredes

Reputation: 13

VBA Find and Offset

I'm trying to find a cell that I know it's there, but always tells me that there's not. Rng = Nothing

Set ws = Worksheets("Sheet1")

ws.Cells.UnMerge
Range("A1").Select

Set Rng = ws.Cells.find(What:="31/1/2019", After:=ActiveCell, 
        LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

Range(Selection, Selection.Offset(0, 9)).Select
Selection.Copy

Workbooks("Other.xlsm").Activate
ActiveWorkbook.Sheets("Sheet1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlValues
Workbooks("Other.xlsx").Close SaveChanges:=False

Upvotes: 0

Views: 1417

Answers (3)

Curt
Curt

Reputation: 1

Something to note for the future, when you are trying to use a date as a criteria, you should put the date within #'s and not ""s. Your VBA will automatically change the date to match the format that your system is set up to use.

For example if I type in the following:

If ActiveCell.Value = #31/1/2018# Then
    a = 1
ElseIf ActiveCell.Value = "31/1/2018" Then
    a = 1
End If

The compiler AUTOMATICALLY changes it to this:

If ActiveCell.Value = #1/31/2018# Then
    a = 1
ElseIf ActiveCell.Value = "31/1/2018" Then
    a = 1
End If

Obviously this won't help if you are looking for Feb 1st as 1/2/19...but it does help on the dates that can't exist.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

The code is looking for a string "31/1/2019" and this string is not presented as a date in Excel. To present it as a date, change the name of the month and the day, like this: "1/31/2019":

Sub TestMe()

    Dim rng As Range

    Set rng = Worksheets(1).Cells.Find(What:="1/31/2019", After:=ActiveCell, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

    If Not rng Is Nothing Then MsgBox rng.Address

End Sub

Of course, there are better ways to do it, for example writing What:=DateSerial(2019, 1, 31) and not caring what comes first and what comes second (as in Gary's solution.)

For the way Excel and VBA format date and search it, take a look here:

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Dim d As Date
d = DateSerial(2019, 1, 31)
Set Rng = ws.Cells.Find(What:=d, After:=ActiveCell, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

(You may have other errors)

Upvotes: 1

Related Questions