Odd Macro issue

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
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.

Code:
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
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

Board Regular
Joined
Mar 20, 2006
Messages
159
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.


?????
 

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
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

Well-known Member
Joined
Jan 27, 2006
Messages
912
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 .


Code:
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
 

Forum statistics

Threads
1,141,309
Messages
5,705,675
Members
421,404
Latest member
Mikecollo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top