Odd Macro issue

[tvoltagg]

so i have this macro that loops through 9 tabs in a sheet, refreshing a separate query (from access) in each tab. Then it goes into each tab and does a simple replace (anything with entire cell=0, replace with "No Target"), then a sort and that's it.

When I step through it, everything works perfectly.

The problem is when I run it via a button. It does everything fine, but then at the end it somehow reverts the replace back to the original. It's so weird. So the No Targets revert back to 0s.

Can someone explain this? I've put the code below, and for space/simplicity reasons, I've set it to only do the first 2 tabs, but really there are 9.

Sub Format()

Dim i As Integer

Application.ScreenUpdating = False
    
    For i = 1 To 2
    ActiveSheet.Next.Select
    Range("A13").Select
    Selection.QueryTable.Refresh BackgroundQuery:=True
    Range("A12").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A13").Select
    Next i

    Sheets("MIS").Select
    Range("F13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="0", Replacement:="No Target", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("F:F").EntireColumn.AutoFit
    Range("A13").Select
      
    Sheets("BDD").Select
    Range("E13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="0", Replacement:="No Target", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("E:E").EntireColumn.AutoFit
    Range("A13").Select
       
        
Application.ScreenUpdating = True
End Sub

 

[sunnyland]

Hello,

What may be happening there is your data is refreshing either because you have a refreshing time set up or something. However everytime you open the workbook and it refreshes you No Target will revert back to Zero.

If this is a visual thing you need meaning formula or code use 0 but you want the user to see no Target then format the column as custom format and then:

#,##0;;"No Target"

It will display No Target wherever there is a zero but the value still will be zero.

What you have to think is when you are using queries to get data every time you refresh the data the value will return to whatever they are in the source as a query is just a copy of something else. The only think that will not change is if you format them a certain way as formatting is only an illusion the value remains the same is just the display that changes

 

[tvoltagg]

The custom format works (very cool, thanks), but the issue still remains.

I refresh the data as the first part of the macro and I understand that it will reset the formatting. That's why the macro refreshes the query FIRST, and THEN formats the columns. Problem is, once the macro is finished running, it takes my formatting away, as if it was refreshing upon ending the macro or something along those lines.

Just to repeat, when i step through the macro one line at a time, it works perfectly. But when I run the macro from the button, that issue happens.

And in the data range properties, I have:

Enable Background Refresh checked
Refresh every X seconds NOT checked
Refresh on open NOT checked... so it's not that.


?????

 

[sunnyland]

Hello,

Do you have :
preserve formatting ticked

 

[tvoltagg]

Yes. I have some conditional formatting and other formatting that I need to keep when I refresh the data. But why would that undo the Replace i do AFTER the refresh (in the macro)?

 

[sunnyland]

Hello,

Now of course if you mean by button: the Excel refresh query Button, this is normal that your formatting will not appear.

You can try this over way.
If you want to access the query before refresh or after refresh event you can try this approach meaning this way everytime it refresh your code will be applied: in my sample I have 2 worksheets: BDD and MIS on each worksheet I have a query. The query results start on A13 with row 13 been the header row. I used one of my database for the purpose.
In column 4 of this query result I have a column with 0 and 1.

Everytime after the query is refresh via the Excel query refresh the macro will run putting your NO TARGET in column 4 where there is 0.

My version of Excel [2000] doesn't accept the parameter DataOption1:=xlSortNormal in the sort so I remove it.

All the code will be place on the workbook. You will have to save the workbook, close it and open it again as the initialisation of the with events is on the workbook open event .

Private WithEvents myquery As QueryTable
Private WithEvents myquery1 As QueryTable

Private Sub myquery_AfterRefresh(ByVal Success As Boolean) 'MIS
If Success = True Then
Range("A13").Select
    Selection.Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom
Range("D13").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="0", Replacement:="NO TARGET", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False
    Columns("F:F").EntireColumn.AutoFit
    End If
    
End Sub
Private Sub myquery1_AfterRefresh(ByVal Success As Boolean) 'BDD
If Success = True Then
Range("A13").Select

Selection.Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=6, MatchCase:=False, Orientation:=xlTopToBottom

Range("D13").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="0", Replacement:="NO TARGET", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

Columns("E:E").EntireColumn.AutoFit

End If
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = False Then
Set myquery = Nothing
Set myquery1 = Nothing
end if
End Sub

Private Sub Workbook_Open()
Set myquery = Worksheets("MIS").QueryTables(1)
Set myquery1 = Worksheets("BDD").QueryTables(1)
End Sub

This should work