Macro works in VBA editor but not in Excel

[farmertml]

Hi All,

I have quite a long Macro which is a little rudimentary. I've been using it for years but have now made some changes to the dates on the sheet to update it ready for this financial year. If I run the macro from within the VBA editor it will execute fine and do everything I need, if I run it using the "Macros" button in Excel or using a command button it brings up my error message "The search found nothing".

I'm confused why it would function correctly and find the date using the VBA editor to run the Macro but not work when the Macro is directly run from within Excel. Any light you can shed would be helpful.

The Macro does a search of a date contained in the 'Summary' sheet cell F3 against Column A in the 'Main Data' sheet which contains 52 dates for each week end.

I'm using Excel 2010 on Windows 7 64bit.

The code is :-

Sub Summary_Fill()
    Sheets("Pivot A").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("Pivot B").Select
    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
    Sheets("Summary").Select
'Clear row with data on in summary sheet'
    Rows("14:15").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("C13").Select
'Search in Summary sheet for value in F3 - the date!'
    Dim FindString As String
    Dim Rng As Range
    FindString = Sheets("Summary").Range("F3").Value
    If Trim(FindString) <> "" Then
        With Sheets("Main Data").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "THE SEARCH FOUND NOTHING!"
            End If
        End With
    End If
'Copy found row to summary sheet'
    ActiveCell.EntireRow.Copy
    Sheets("Summary").Select
    Range("A14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Thanks very much in advance,

Paul

 

[Andrew Poulsom]

This may help you:

VBA Procedures For Dates And Times

 

[farmertml]

This may help you:

VBA Procedures For Dates And Times

Hi Andrew,

Thanks for that. I am using the dd/mm/yyyy format in both the cell which contains the search string and the A column.

I still can't get my head around why it would work using the VBA editor but not when run within excel.

Any further help much appreciated.

 

[Andrew Poulsom]

Sorry, I don't know either.

 

[Titian]

The last time you changed the dates, I guess a year or so ago, were you using your current version of Excel?

You do say that you have been using the macro for "years", so what has changed since you last updated the dates?

Hope this helps.

 

[farmertml]

OK, your thoughts about that date got me thinking that it must be something to do with the way the cells are formatted.

I had a play around with the formatting of the dates both in the summary sheet and on the main data sheet and changed them all to be dd/mm/yyyy without the * before (which makes it change based on region) ... suddenly everything worked fine!

Can't believe something simple like this would cause me such headache. Thank you Andrew and Titian for getting me thinking along the right track - props to you both.

Thanks,
Paul