Share via

Macro to do nothing or go to the next section if nothing exists

Anonymous
2012-04-25T18:45:53+00:00

I have a macro that looks for a date in a spreadsheet. Right now, if the date is not present, it will copy a blank cell and paste into another sheet. I need it to go to the next spreadsheet to look for the date again to paste in the master sheet in a different location. Right now, i am using "on error resume next" but on the master spreadsheet, i want the value of the prior day to stay as is and not paste a blank into the cell. hope this makes sense.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-04-25T22:06:49+00:00

    Try this - make sure that you change the "d-mmm" to the format string of the date you are looking for.

    Sub t()

    Dim myD As Range

    Dim myS As Worksheet

    With Workbooks("SERVICES CKG 2011.xlsx")

        For Each myS In .Worksheets

            Set myD = myS.Cells.Find(Format(date2, "d-mmm"), myS.Range("A1"), xlValues)

            If Not myD Is Nothing Then

                Set myD = myD.Offset(0, 1)

                Do Until myD.Offset(1, -1) <> "" Or myD.Offset(1, 0) = ""

                    Set myD = myD.Offset(1, 0)

                Loop

                GoTo Found:

            End If

        Next myS

        MsgBox Format(date2, "d-mmm") & " was not found."

        Exit Sub

    Found:

        Workbooks("CASH REPORT_ " & (crdate1)).Activate

        Range("SERVICES_BOOK").Value = myD.Offset(0, 10).Value

    End With

    End Sub

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-10-10T10:42:33+00:00

    Hi Bernie,

    Many thanks for this. It is very helpful

    What if instead of going to Found: if myD is nothing, I wanted it to go to the next Next (bypass the current next)

    Thanks

    Pedro

    0 comments
  2. Anonymous
    2012-04-25T19:50:07+00:00

    Windows("SERVICES CKG 2011.xlsx").Activate

    Sheets("April 12").Select

    range("a1").Select

    On Error Resume NextCells.Find(What:=date2, After:=ActiveCell, LookIn:=xlFormulas, LookAt _

            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

            False, SearchFormat:=False).Activate

    ActiveCell.Offset(0, 1).SelectDo Until ActiveCell.Offset(1, -1) <> "" Or ActiveCell.Offset(1, 0) = "" ActiveCell.Offset(1, 0).SelectLoopActiveCell.Offset(0, 10).Select

    ActiveCell.Copy

    Windows("CASH REPORT_ " & (crdate1)).Activaterange("SERVICES_BOOK").SelectSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _        SkipBlanks:=False, Transpose:=False

    Can you tell me how that would fit in with this code? "Date2" is specified by formula based on the current date in the spreadsheet. Crdate1 is another date that is specified by formula based on a value in another worksheet. I added the on error resume next because the macro would stop if the date wasn't in the sheet. Now it just copies a blank cell. Is there any way that I can just get the macro to skip the section above that is bolded and italicized?

    0 comments
  3. Anonymous
    2012-04-25T19:39:00+00:00

    To search all sheets for a date, change the "d-mmm" to the date format you are using, and change Now() to a source of the date you want to find - then run the macro:

    Sub TestMacro()

        Dim myD As Range

        Dim myS As Worksheet

        For Each myS In Worksheets

            Set myD = myS.Cells.Find(Format(Now(), "d-mmm"), , xlValues)

            If Not myD Is Nothing Then

                GoTo Found:

            End If

        Next myS

        MsgBox "not found"

        Exit Sub

    Found:

        MsgBox "Found in " & myD.Parent.Name & " in cell " & myD.Address

    End Sub

    0 comments