Auto filter using msgbox in macro not woking

[ashish128]

Dear All,

I am facing a strange situation while recording and editing a macro
for autofilter results

I want to view two autofilter results from a single column using macro

Manually it is done as
1. Apply autofilter on one column, say X [This column contains dates]
2. Click the down arrow and select Custom
3. show rows where
equals 06/06/2007
4. Click OK
5. Click on other Column's name, say Y, to select entire column [I am
doing this to get the autosum of filtered values]
6. Note the Autosum value displayed on statusbar
7 Click the down arrow on column X
8. show rows where
is greater than 06/06/2007
9. Click OK
10. Note the Autosum value displayed on statusbar.

But my following macro has two problems
1. When the messagebox is displayed the autosum value is not shown on
statusbar.
2. After clicking "OK" both the "X" & "Y" column show no values

I just recorded this macro doing manually and inserted the mesagebox
to pause the execution of macro.

Sub financing_accrual_received()
'
' Accrual_financing Macro
' Macro recorded 6/7/2007 by Ashish Sharma
'

'
Dim s, accrual, received

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter

'Range("A:x, aa:am").Select
'Selection.EntireColumn.Hidden = True
Range("Z1").Select
Selection.AutoFilter Field:=26, Criteria1:=">=" & CLng(Date),
Operator:=xlAnd

Columns("Y").Select
s = MsgBox("Please Mark Interest Accrual: ", vbOKOnly, "Please
Confirm")


Columns("z").Select
Selection.AutoFilter Field:=26, Criteria1:="=" & CLng(Date),
Operator:=xlAnd
Columns("Y").Select

End Sub


Kindly guide and help.

 

[Dave Peterson]

Maybe it's time to try the Format() suggestion from yesterday.

Be aware that even though you show a message box between the autofilters, the
user won't be able to pause to make any changes. The code will continue when
they click the ok button.

Dear All,

I am facing a strange situation while recording and editing a macro
for autofilter results

I want to view two autofilter results from a single column using macro

Manually it is done as
1. Apply autofilter on one column, say X [This column contains dates]
2. Click the down arrow and select Custom
3. show rows where
equals 06/06/2007
4. Click OK
5. Click on other Column's name, say Y, to select entire column [I am
doing this to get the autosum of filtered values]
6. Note the Autosum value displayed on statusbar
7 Click the down arrow on column X
8. show rows where
is greater than 06/06/2007
9. Click OK
10. Note the Autosum value displayed on statusbar.

But my following macro has two problems
1. When the messagebox is displayed the autosum value is not shown on
statusbar.
2. After clicking "OK" both the "X" & "Y" column show no values

I just recorded this macro doing manually and inserted the mesagebox
to pause the execution of macro.

Sub financing_accrual_received()
'
' Accrual_financing Macro
' Macro recorded 6/7/2007 by Ashish Sharma
'

'
Dim s, accrual, received

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter

'Range("A:x, aa:am").Select
'Selection.EntireColumn.Hidden = True
Range("Z1").Select
Selection.AutoFilter Field:=26, Criteria1:=">=" & CLng(Date),
Operator:=xlAnd

Columns("Y").Select
s = MsgBox("Please Mark Interest Accrual: ", vbOKOnly, "Please
Confirm")

Columns("z").Select
Selection.AutoFilter Field:=26, Criteria1:="=" & CLng(Date),
Operator:=xlAnd
Columns("Y").Select

End Sub

Kindly guide and help.

 

[ashish128]

Maybe it's time to try the Format() suggestion from yesterday.

Be aware that even though you show a message box between the autofilters, the
user won't be able to pause to make any changes. The code will continue when
they click the ok button.

Dear All,

I am facing a strange situation while recording and editing a macro
for autofilter results

I want to view two autofilter results from a single column using macro

Manually it is done as
1. Apply autofilter on one column, say X [This column contains dates]
2. Click the down arrow and select Custom
3. show rows where
equals 06/06/2007
4. Click OK
5. Click on other Column's name, say Y, to select entire column [I am
doing this to get the autosum of filtered values]
6. Note the Autosum value displayed on statusbar
7 Click the down arrow on column X
8. show rows where
is greater than 06/06/2007
9. Click OK
10. Note the Autosum value displayed on statusbar.

But my following macro has two problems
1. When the messagebox is displayed the autosum value is not shown on
statusbar.
2. After clicking "OK" both the "X" & "Y" column show no values

I just recorded this macro doing manually and inserted the mesagebox
to pause the execution of macro.

Sub financing_accrual_received()
'
' Accrual_financing Macro
' Macro recorded 6/7/2007 by Ashish Sharma
'

'
Dim s, accrual, received

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.AutoFilter

'Range("A:x, aa:am").Select
'Selection.EntireColumn.Hidden = True
Range("Z1").Select
Selection.AutoFilter Field:=26, Criteria1:=">=" & CLng(Date),
Operator:=xlAnd

Columns("Y").Select
s = MsgBox("Please Mark Interest Accrual: ", vbOKOnly, "Please
Confirm")

Columns("z").Select
Selection.AutoFilter Field:=26, Criteria1:="=" & CLng(Date),
Operator:=xlAnd
Columns("Y").Select

End Sub

Kindly guide and help.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dear Dave,

I dont want user to do anything. I am applying msgbox just to pause
the macro so that the user can note the autosum displayed on status
bar and once he clicks "OK" the code will continue. But the problem
stated in my post are not expected in this routine.

Any guidance, I just want to see two autofilter results one by one
using macro, I need to pause the macro so that the user can note the
autosum displayed in statusbar for the first result and then the macro
can proceed.

 

[Dave Peterson]

I'm not sure I'd rely on that statusbar to show anything. If I have it set to
Count and you wanted Average, Sum, standard deviation, ..., then it won't do
what you want.

If you want to show the subtotals from the autofilter, maybe using

With Worksheets("sheet1")
With .AutoFilter.Range
MsgBox Application.Subtotal(9, .Columns(3))
End With
End With

Did changing the code to use Format() instead of clng() help displaying the
values?

 

[ashish128]

I'm not sure I'd rely on that statusbar to show anything. If I have it set to
Count and you wanted Average, Sum, standard deviation, ..., then it won't do
what you want.

If you want to show the subtotals from the autofilter, maybe using

With Worksheets("sheet1")
With .AutoFilter.Range
MsgBox Application.Subtotal(9, .Columns(3))
End With
End With

Did changing the code to use Format() instead of clng() help displaying the
values?









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave,

The format() function worked nicely and so did your suggestion to
using subtotal in msgbox.

I am thankful to you for this help.