Solved: Progress bar complications

[lanhao]

Hi there, this may be a really dumb post but I have been getting stumped royally on this.

I'm tryingt o include a progress bar while a macro i have is going through and getting all the information it needs to process information from. The probelm is, I have tried to implement it into the code I have, but it isn't showing the progress bar at all, also, I need to make sure that it goes in tandem with the actual macro completing itself out.

This thing has been completely screwing with me and I don't know why I can't get this worked out.

Sorry to be a pain on this one ^^;

[OBP]

Are you going to post the code you have tried?

[lanhao]

Here is the code that i am trying to incorporate into it, but I am having issues getting this to work, it wasn't running at all.



Sub GetTier3()
'This macro is designed to get all the information for the Tier 3 representative's ticket data
Set sht9 = Worksheets("Tier3Test")
Dim i As Integer 'counter for page DPRAW for finding the last row
Dim j As Integer 'counter for the page
Dim k As Long 'Row counter for the datapage DataDump'
i = 1
j = 10
k = 1
Application.ScreenUpdating = False
'Clear all information
Range("A6:O32").Select
Range("A6").Activate
Selection.ClearContents
Application.Goto Reference:=Range("A6"), Scroll:=False

'Add the updated list of reps to the main page
Sheets("DataDump").Select
Range(Cells(2, 12), Cells(Rows.count, 12).End(xlUp)).Select
Selection.Copy
Sheets("Tier3Test").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:=Range("A6"), Scroll:=False

For k = 6 To Cells(Rows.count, "A").End(xlUp).Row
If sht9.Range("A" & k).Value = "" Then
sht9.Range("E" & k) = "0"
sht9.Range("F" & k) = "0"
sht9.Range("G" & k) = "0"
Else
'This is for the NOC tickets
sht9.Range("B" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C10:R65536C10=R[0]C1),--(DPRAW!R2C9:R65536C9>=R3C2),--(DPRAW!R2C9:R65536C9<=R3C4),--(DPRAW!R2C5:R65536C5=""VoIPOperations""))"
sht9.Range("C" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C12:R65536C12=R[0]C1),--(DPRAW!R2C11:R65536C11>=R3C2),--(DPRAW!R2C11:R65536C11<=R3C4),--(DPRAW!R2C5:R65536C5=""VoIPOperations""))"
sht9.Range("B" & k).Value = sht9.Range("B" & k).Value
sht9.Range("C" & k).Value = sht9.Range("C" & k).Value
'This is for digital phone tickets worked
sht9.Range("E" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C7:R65536C7=R[0]C1),--(DPRAW!R2C8:R65536C8>=R3C2),--(DPRAW!R2C8:R65536C8<=R3C4),--(DPRAW!R2C5:R65536C5=""TW-BinghamtonDPTech3""))"
sht9.Range("F" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C10:R65536C10=R[0]C1),--(DPRAW!R2C9:R65536C9>=R3C2),--(DPRAW!R2C9:R65536C9<=R3C4),--(DPRAW!R2C5:R65536C5=""TW-BinghamtonDPTech3""))"
sht9.Range("G" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C12:R65536C12=R[0]C1),--(DPRAW!R2C11:R65536C11>=R3C2),--(DPRAW!R2C11:R65536C11<=R3C4),--(DPRAW!R2C5:R65536C5=""TW-BinghamtonDPTech3""))"
sht9.Range("E" & k).Value = sht9.Range("E" & k).Value
sht9.Range("F" & k).Value = sht9.Range("F" & k).Value
sht9.Range("G" & k).Value = sht9.Range("G" & k).Value
'This is for the RR Tier 3 tickets closed
sht9.Range("I" & k).Formula = "=SUMPRODUCT(--(RRRAW!R2C9:R65536C9=R[0]C1),--(RRRAW!R2C8:R65536C8>=R3C2),--(RRRAW!R2C8:R65536C8<=R3C4),--(RRRAW!R2C3:R65536C3=""TW-BinghamtonTech3""))"
sht9.Range("I" & k).Value = sht9.Range("I" & k).Value
'This is for the Commercial Calls Taken
sht9.Range("K" & k).Formula = "=SUMPRODUCT(--(Comm!R2C6:R65536C6=R[0]C1),--(Comm!R2C5:R65536C5>=R3C2),--(Comm!R2C5:R65536C5<=R3C4))"
sht9.Range("L" & k).Formula = "=SUMPRODUCT(--(Comm!R2C8:R65536C8=R[0]C1),--(Comm!R2C7:R65536C7>=R3C2),--(Comm!R2C7:R65536C7<=R3C4))"
sht9.Range("K" & k).Value = sht9.Range("K" & k).Value
sht9.Range("L" & k).Value = sht9.Range("L" & k).Value
'This is for the ABUSE Calls taken
sht9.Range("N" & k).Formula = "=SUMPRODUCT(--(ABUSE!R2C10:R65536C10=R[0]C1),--(ABUSE!R2C9:R65536C9>=R3C2),--(ABUSE!R2C9:R65536C9<=R3C4))"
sht9.Range("N" & k).Value = sht9.Range("N" & k).Value

End If

Next k
Range("A6").Select
Application.ScreenUpdating = True
End Sub

[OBP]

Did you say that the VB code that you have posted is not working?

[lanhao]

no, the code is working fine, just the progress bar was not working when i tried to include it in with this code above. I don't know what to do with it unfortunately and it's been a big pain unfortunately

[mdmackillop]

Are you really needing to SumProduct 65000+ element arrays? What is the maximum number of rows? I suspect that there are no spare resources to display the progress bar.
Regards
MD

[lanhao]

haven't had any problems with system resources, i needed to cap the range on this because the pages are going to evenutally be filled up completely with information imported from other reports generated. (I'm just trying to get what they have working a bit better until i can get this set up in Access. I have not had any issues with any slowdown or anything fortunately, but I need to figure out how to get that progress bar code in there while this macro runs. :)

Work get's pretty crazy and they're asking me to try and streamline some of this stuff. The original code for this sheet was about twelve times larger and also took up alot more system resources, so it is progress... kind of ^^;

[jungix]

I don't see any reference to a progress bar in your code. You said you used another code for the progress bar, but if you just refer to a KB entry there may be some things you need to change before it works as it is just a template.

[lanhao]

I was using the one that was on jwalk's page, i removed all traceso f the code, so the macro would work as is (was having significant issues trying to figure out where to put the code in to tie it all together)

[jungix]

Post to be deleted

[jungix]

If you don't need a complicated progress bar just try:



Sub GetTier3()
'This macro is designed to get all the information for the Tier 3 representative's ticket data
Set sht9 = Worksheets("Tier3Test")
Dim i As Integer 'counter for page DPRAW for finding the last row
Dim j As Integer 'counter for the page
Dim k As Long 'Row counter for the datapage DataDump'
Dim numberRows As Integer


i = 1
j = 10
k = 1
Application.ScreenUpdating = False
'Clear all information
Range("A6:O32").Select
Range("A6").Activate
Selection.ClearContents
Application.Goto Reference:=Range("A6"), Scroll:=False


'Add the updated list of reps to the main page
Sheets("DataDump").Select
Range(Cells(2, 12), Cells(Rows.count, 12).End(xlUp)).Select
Selection.Copy
Sheets("Tier3Test").Select
Range("A6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Goto Reference:=Range("A6"), Scroll:=False

numberRows = Cells(Rows.count, "A").End(xlUp).Row

For k = 6 To numberRows
Application.StatusBar = "Treating row number " & k &"/" & numberRows
If sht9.Range("A" & k).Value = "" Then
sht9.Range("E" & k) = "0"
sht9.Range("F" & k) = "0"
sht9.Range("G" & k) = "0"
Else
'This is for the NOC tickets
sht9.Range("B" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C10:R65536C10=R[0]C1),--(DPRAW!R2C9:R65536C9>=R3C2),--(DPRAW!R2C9:R65536C9<=R3C4),--(DPRAW!R2C5:R65536C5=""VoIPOperations""))"
sht9.Range("C" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C12:R65536C12=R[0]C1),--(DPRAW!R2C11:R65536C11>=R3C2),--(DPRAW!R2C11:R65536C11<=R3C4),--(DPRAW!R2C5:R65536C5=""VoIPOperations""))"
sht9.Range("B" & k).Value = sht9.Range("B" & k).Value
sht9.Range("C" & k).Value = sht9.Range("C" & k).Value
'This is for digital phone tickets worked
sht9.Range("E" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C7:R65536C7=R[0]C1),--(DPRAW!R2C8:R65536C8>=R3C2),--(DPRAW!R2C8:R65536C8<=R3C4),--(DPRAW!R2C5:R65536C5=""TW-BinghamtonDPTech3""))"
sht9.Range("F" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C10:R65536C10=R[0]C1),--(DPRAW!R2C9:R65536C9>=R3C2),--(DPRAW!R2C9:R65536C9<=R3C4),--(DPRAW!R2C5:R65536C5=""TW-BinghamtonDPTech3""))"
sht9.Range("G" & k).Formula = "=SUMPRODUCT(--(DPRAW!R2C12:R65536C12=R[0]C1),--(DPRAW!R2C11:R65536C11>=R3C2),--(DPRAW!R2C11:R65536C11<=R3C4),--(DPRAW!R2C5:R65536C5=""TW-BinghamtonDPTech3""))"
sht9.Range("E" & k).Value = sht9.Range("E" & k).Value
sht9.Range("F" & k).Value = sht9.Range("F" & k).Value
sht9.Range("G" & k).Value = sht9.Range("G" & k).Value
'This is for the RR Tier 3 tickets closed
sht9.Range("I" & k).Formula = "=SUMPRODUCT(--(RRRAW!R2C9:R65536C9=R[0]C1),--(RRRAW!R2C8:R65536C8>=R3C2),--(RRRAW!R2C8:R65536C8<=R3C4),--(RRRAW!R2C3:R65536C3=""TW-BinghamtonTech3""))"
sht9.Range("I" & k).Value = sht9.Range("I" & k).Value
'This is for the Commercial Calls Taken
sht9.Range("K" & k).Formula = "=SUMPRODUCT(--(Comm!R2C6:R65536C6=R[0]C1),--(Comm!R2C5:R65536C5>=R3C2),--(Comm!R2C5:R65536C5<=R3C4))"
sht9.Range("L" & k).Formula = "=SUMPRODUCT(--(Comm!R2C8:R65536C8=R[0]C1),--(Comm!R2C7:R65536C7>=R3C2),--(Comm!R2C7:R65536C7<=R3C4))"
sht9.Range("K" & k).Value = sht9.Range("K" & k).Value
sht9.Range("L" & k).Value = sht9.Range("L" & k).Value
'This is for the ABUSE Calls taken
sht9.Range("N" & k).Formula = "=SUMPRODUCT(--(ABUSE!R2C10:R65536C10=R[0]C1),--(ABUSE!R2C9:R65536C9>=R3C2),--(ABUSE!R2C9:R65536C9<=R3C4))"
sht9.Range("N" & k).Value = sht9.Range("N" & k).Value


End If


Next k
Range("A6").Select
Application.ScreenUpdating = True
End Sub

[lanhao]

I'm not sure I followed what you did with the '...' on the first post, but I would just add the additional sub in there for k to count through each of the rows from row 6 to the last row, right?

[lanhao]

is there a way of applying that same code to a progress bar instead of hte status bar in the bottom left of the excel window?

[OBP]

A Status bar, like windows uses can be created using a Form and the userform activation calls your VBA code and uses your row count to form a bar on the Form. I have this code from Power Programming.
in the Form Activate
Call Main

Main is your code and where you have
Application.StatusBar = "Treating row number " & k &"/" & numberRows
you put
PctDone = k/numberRows
Call UpdateProgress(PctDone)

In the UpdateProgress(Pct) procedure you have
With Userform1
.FrameProgress.caption = Fromat(Pct, "0%)
.LabelProgress.Width = Pct* (.FrameProgress.width - 10)
.repaint
End with

and to start off the form you use
ShowDialog()
UserForm1.LabelProgress.width =0
Userform1 .show
end sub

[lanhao]

Ok - got the form to respond, and the code is looking good, only thing now is that it isn't showing when i hit the button for the macro to run it.... anjy ideas why it's being this ornery?

[OBP]

Did you include the Show dialog procedure in your code?
You have to have the userform1.show in your to get it show before the updating code can work.
Sorry I can't be more help, I just passed on what I had read.

[mdmackillop]

Can you post your workbook with sample data and code as you have it now?
Regards
MD

[lanhao]

well what you passed on as having read, hepled me out immensely in getting the code in there, so thank you for that :)

I think I am just missing a line of code somewhere in the main macro, here is what I have. Everything else is working great with it checking hte information, it just isn't showing the userform is all.

<Addendum> I'm a moron, I know why the userform wasn't showing, i had the wrong macro assigned to the button, it needed to have the userform (progress bar) show up, and then it would do the other macro (the GetTier1 code I had put on this post)

Thanks so much for all your hepl everyone! :)

[OBP]

Have you tried switching off the NO screen updating just to see if that is supressing it?
The other thing is the code should say

Call UpdateProgress(PctDone)

to "Call" the Module that has the code.
Yours says

UpdateProgress (PctDone)

[OBP]

Solved?

[lanhao]

Yeah, I found out why it wasn't showing, i had the wrong macro assigned to the button, insctead of the one where the button would show (which calls on the other macro to do all the formula work). It's working now. Only thing I am trying to kink out now is getting the cancel button to stop part way through running the macro.

Thank you again for helping put the information in a format I was able to follow.