Running Code with F5 vs F8

A

AlanAnderson

Board Regular
Joined
Jun 7, 2010
Messages
134
Hi,

I have really strange situation. When I run the identical code (far too much to post) using F5 it falls over (one of my variables not being set by if loop).
When I run code using F8 so I can step through it, the code runs correctly from start to finish and finds that variable.

Does this make sense to anyone? Are there any differences between F5 and F8?

Regards,

Alan
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
when you are testing are you running from the same position in the script/same workbook, F8 being laborious sometimes a short cut is taken.

has the code compiled

what line of code is failing, can you post a section that is relevant

if you add a break before your F% fails, run it to there as F5 then step through with F8, does that still work to conclusion
 
Upvote 0
Hi Mole,
1. I am running from beginning in all cases.
2. The code compiles ok
3.I'm showing just the line where var is (F8) and is not(F5) being picked up
4. If I run with F5 up to MsgBox "Wait2" and then F8 it falls over.

There isd probably something wrong within my "for ... Next" and or "if .. endif" logic. But why would it work with F8 and not F5. Makes no sense to me :confused:
Really appreciate your interest. I am in desperate need of some help here.

Regards,

Alan


Code: 
[FONT=Times New Roman]        MsgBox "WAIT2"<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Times New Roman]       Sheets(vpvquotefile).Select<o:p></o:p>[/FONT]
[FONT=Times New Roman]       finalRowQuotePage = Sheets(vpvquotefile).Cells(Rows.Count, 1).End(xlUp).Row<o:p></o:p>[/FONT]
[FONT=Times New Roman]               For i = 15 To finalRowQuotePage<o:p></o:p>[/FONT]
[FONT=Times New Roman]               'This will get 4 vars from CalcTypePage write them to Cols O.P,Q,R<o:p></o:p>[/FONT]
[FONT=Times New Roman]               'then get Qty and % from cols 4 & 5 QuotePage and write to Cols S & T<o:p></o:p>[/FONT]
[FONT=Times New Roman]               <o:p></o:p>[/FONT]
[FONT=Times New Roman]                   If Left(Cells(i, 3), 1) = "&" Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                       vCode = Cells(i, 1)<o:p></o:p>[/FONT]
[FONT=Times New Roman]                       Sheets(vpvquotefile).Cells(i, 20) = Sheets(vpvquotefile).Cells(i, 4) 'Qty -usually 1<o:p></o:p>[/FONT]
[FONT=Times New Roman]                       Sheets(vpvquotefile).Cells(i, 21) = Sheets(vpvquotefile).Cells(i, 5) '%<o:p></o:p>[/FONT]
[FONT=Times New Roman]                       Sheets("CalcType").Select<o:p></o:p>[/FONT]
[FONT=Times New Roman]                       FinalRowCalcType = Cells(Rows.Count, 1).End(xlUp).Row<o:p></o:p>[/FONT]
[FONT=Times New Roman]                           v = i<o:p></o:p>[/FONT]
[FONT=Times New Roman]                           For m = 2 To FinalRowCalcType<o:p></o:p>[/FONT]
[FONT=Times New Roman]                               <o:p></o:p>[/FONT]
[FONT=Times New Roman]                               If vCode = Cells(m, 1) Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                  Sheets(vpvquotefile).Select                                   <o:p></o:p>[/FONT]
[FONT=Times New Roman]                                   Sheets(vpvquotefile).Cells(v, 15) = Sheets("CalcType").Cells(m, 1) 'Code<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                   Sheets(vpvquotefile).Cells(v, 16) = Val(Sheets("CalcType").Cells(m, 2)) 'NumMult<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                   Sheets(vpvquotefile).Cells(v, 17) = Sheets("CalcType").Cells(m, 3) 'VarMult<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                   Sheets(vpvquotefile).Cells(v, 18) = Val(Sheets("CalcType").Cells(m, 4)) 'NumDiv<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                   Sheets(vpvquotefile).Cells(v, 19) = Sheets("CalcType").Cells(m, 5) 'VarDiv<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                   'v = v + 1<o:p></o:p>[/FONT]
[FONT=Times New Roman]                               End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]                           Next m<o:p></o:p>[/FONT]
[FONT=Times New Roman]                   End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]                  If Not Left(Cells(i, 3), 1) = "&" Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                      Ans = Ans + Val(Cells(i, 4))<o:p></o:p>[/FONT]
[FONT=Times New Roman]                   Else<o:p></o:p>[/FONT]
[FONT=Times New Roman]                       vExcl = vExcl + Val(Cells(i, 4))<o:p></o:p>[/FONT]
[FONT=Times New Roman]                   End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]               Next i<o:p></o:p>[/FONT]
[FONT=Times New Roman]               <o:p></o:p>[/FONT]
[FONT=Times New Roman]               MsgBox ("WAIT PRETOTAL NOW")<o:p></o:p>[/FONT]
[FONT=Times New Roman]               <o:p></o:p>[/FONT]
[FONT=Times New Roman]       finalRowQuotePage = Sheets(vpvquotefile).Cells(Rows.Count, 3).End(xlUp).Row<o:p></o:p>[/FONT]
[FONT=Times New Roman]‘XXXXX Next line Works with F8 but not with F5 – this causes fallover later on in prg <o:p></o:p>[/FONT]
[FONT=Times New Roman]       Cells(finalRowQuotePage + 1, 3) = "PRE % TOTAL"<o:p></o:p>[/FONT]
[FONT=Times New Roman]       Cells(finalRowQuotePage + 1, 4) = Val(Ans)<o:p></o:p>[/FONT]
[FONT=Times New Roman]       Cells(finalRowQuotePage + 2, 3) = "EXCLUSIONS"<o:p></o:p>[/FONT]
[FONT=Times New Roman]       Cells(finalRowQuotePage + 2, 4) = Val(vExcl)<o:p></o:p>[/FONT]
[FONT=Times New Roman]       Ans = 0<o:p></o:p>[/FONT]
[FONT=Times New Roman]       vExcl = 0<o:p></o:p>[/FONT]
[FONT=Times New Roman]‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX<o:p></o:p>[/FONT]
<o:p></o:p>
<o:p></o:p>
[FONT=Times New Roman]‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Times New Roman]‘LATER ON IN ROUTINE<o:p></o:p>[/FONT]
<o:p></o:p>
[FONT=Times New Roman]      If Cells(x, 3) = "PRE % TOTAL" Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]               vTotalLine = x + 1 ‘ XX THIS CAUSES PROBLEM AS IT RETURNS VALUE OF EMPTY<o:p></o:p>[/FONT]
[FONT=Times New Roman]               vpretot6 = Val(Cells(x, 6))<o:p></o:p>[/FONT]
[FONT=Times New Roman]               vpretot7 = Val(Cells(x, 7))<o:p></o:p>[/FONT]
[FONT=Times New Roman]               vpretot8 = Val(Cells(x, 8))<o:p></o:p>[/FONT]
[FONT=Times New Roman]               vpretot9 = Val(Cells(x, 9))<o:p></o:p>[/FONT]
[FONT=Times New Roman]           End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]           Next x<o:p></o:p>[/FONT]
[FONT=Times New Roman]           FinalRowQuoteSheet = Cells(Rows.Count, 6).End(xlUp).Row<o:p></o:p>[/FONT]
[FONT=Times New Roman]          WriteRow = FinalRowQuoteSheet + 1<o:p></o:p>[/FONT]
[FONT=Times New Roman]          For y = vTotalLine To FinalRowQuoteSheet<o:p></o:p>[/FONT]
[FONT=Times New Roman]           vTOT6 = vTOT6 + Cells(y, 6) ‘ XXX THIS IS FALLOVER LINE AS Cells (y,6)  has no value<o:p></o:p>[/FONT]
[FONT=Times New Roman]           vTOT7 = vTOT7 + Cells(y, 7)<o:p></o:p>[/FONT]
[FONT=Times New Roman]           vTOT8 = vTOT8 + Cells(y, 8)<o:p></o:p>[/FONT]
[FONT=Times New Roman]           vTOT9 = vTOT9 + Cells(y, 9)<o:p></o:p>[/FONT]
[FONT=Times New Roman]         Next y<o:p></o:p>[/FONT]
 
Upvote 0
do you use option explicit at the top of your code pages ?

are these right

Sheets(vpvquotefile).Select

vpvquotefile is an actual sheet and not a reference

otherwise

Sheets("vpvquotefile").Select

is there on error resume next above in the code ?
 
Last edited:
Upvote 0
Hi Mole,
No I have not used Option Explicit. I'm afraid I am a sloppy programmer and as I go along I'm forced to invent new Variables so I seldom use it. Would it have helped?

Regards,

Alan
 
Upvote 0
it will help keep a track of items that should be declared, then when you know whats missing you can

Dim Wednesday As String

I can't see it doing any harm
 
Upvote 0
Alan

The problem is probably nothing to do with how you are running the code.

I think the problem is that in the part of the code where the variables aren't being set there are no worksheet references for things like Cells.

For example here the first line of code has Cells with no worksheet reference but the following lines do have worksheet references.
Rich (BB code): 
                      vCode = Cells(i, 1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
                      Sheets(vpvquotefile).Cells(i, 20) = Sheets(vpvquotefile).Cells(i, 4) 'Qty -usually 1<o:p></o:p>
                      Sheets(vpvquotefile).Cells(i, 21) = Sheets(vpvquotefile).Cells(i, 5) '%<o:p></o:p>
I'm not 100% sure how to fix this but here's a guess, I've created references for the worksheets to save me typing them over and over.:)
Rich (BB code): 
Dim wsQuote As Worksheet
Dim wsCalc As Worksheet
 
    MsgBox "WAIT2"
 
    Set wsQuote = Sheets(vpvquotefile)
 
    finalRowQuotePage = wsQuote.Cells(Rows.Count, 1).End(xlUp).Row
 
    For i = 15 To finalRowQuotePage
        'This will get 4 vars from CalcTypePage write them to Cols O.P,Q,R
        'then get Qty and % from cols 4 & 5 QuotePage and write to Cols S & T
 
        If Left(wsQuote.Cells(i, 3), 1) = "&" Then
            vCode = wsQuote.Cells(i, 1)
            wsQuote.Cells(i, 20) = wsQuote.Cells(i, 4)    'Qty -usually 1
            swsQuote.Cells(i, 21) = wsQuote.Cells(i, 5)    '%
 
            Set wsCalc = Sheets("CalcType")
 
            FinalRowCalcType = wsCalc.Cells(Rows.Count, 1).End(xlUp).Row
 
            v = i
 
            For m = 2 To FinalRowCalcType
                If vCode = wsCalc.Cells(m, 1) Then
 
                    wsQuote.Cells(v, 15) = wsCalc.Cells(m, 1)    'Code
                    wsQuote.Cells(v, 16) = Val(wsCalc.Cells(m, 2))    'NumMult
                    wsQuote.Cells(v, 17) = wsCalc.Cells(m, 3)    'VarMult
                    wsQuote.Cells(v, 18) = Val(wsCalc.Cells(m, 4))    'NumDiv
                    wsQuote.Cells(v, 19) = wsCalc.Cells(m, 5)    'VarDiv
                    'v = v + 1
                End If
            Next m
        End If
 
        ' not sure which sheet Cells is referring to here
        If Not Left(Cells(i, 3), 1) = "&" Then
            Ans = Ans + Val(Cells(i, 4))
        Else
            vExcl = vExcl + Val(Cells(i, 4))
        End If
    Next i
 
    MsgBox ("WAIT PRETOTAL NOW")
 
    finalRowQuotePage = wsQuote.Cells(Rows.Count, 3).End(xlUp).Row
    wsQuote.Cells(finalRowQuotePage + 1, 3) = "PRE % TOTAL"
    wsQuote.Cells(finalRowQuotePage + 1, 4) = Val(Ans)
    wsQuote.Cells(finalRowQuotePage + 2, 3) = "EXCLUSIONS"
    wsQuote.Cells(finalRowQuotePage + 2, 4) = Val(vExcl)
 
    Ans = 0
    vExcl = 0
    ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
    ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 
    ' again, not sure which sheet Cells is supposed to refer to here
    If Cells(x, 3) = "PRE % TOTAL" Then
        vTotalLine = x + 1    ' XX THIS CAUSES PROBLEM AS IT RETURNS VALUE OF EMPTY
                                    ' not sure about this - can't see anywhere in the code that x is given/not given a value
        vpretot6 = Val(Cells(x, 6))
        vpretot7 = Val(Cells(x, 7))
        vpretot8 = Val(Cells(x, 8))
        vpretot9 = Val(Cells(x, 9))
    End If
Next x
 
FinalRowQuoteSheet = wsQuote.Cells(Rows.Count, 6).End(xlUp).Row
 
WriteRow = FinalRowQuoteSheet + 1
 
For y = vTotalLine To FinalRowQuoteSheet
    vTOT6 = vTOT6 + Cells(y, 6)    ' XXX THIS IS FALLOVER LINE AS Cells (y,6)  has no value
                                                ' this could be because there is not worksheet reference the code is
                                                ' looking at Cells(y,6) on the wrong worksheet
    vTOT7 = vTOT7 + Cells(y, 7)
    vTOT8 = vTOT8 + Cells(y, 8)
    vTOT9 = vTOT9 + Cells(y, 9)
 
Next y
That's actually more of a guess than I thought - I've commented the bits where I wasn't sure which sheet to refer to.:eek:

PS Option Explicit is a very good idea, though perhaps a little irritating at first.
 
Last edited:
Upvote 0
Hi Mole, Thanks so much for all the time and effort.

I will replace those lines of my code and replace with your code and see if it helps. Will take a little time but I'll let you know what happens.

Thanks again,

Alan
 
Upvote 0
AlanAnderson said:
4. If I run with F5 up to MsgBox "Wait2" and then F8 it falls over.
Click to expand...
from which I'd expect that things were going awry before MsgBox "WAIT2", but we don't have that code.

One big difference between F8 and F5 is time between execution of lines of code. So I would first look for things in the code which take significant time.

Is this code working on/with several files at once?

If your code updates values in cells, two things are likely to happen:
1. a calculation is triggered
2. if you have event handlers they too may be triggered.

Sometimes, especially regarding calculation, the calculation has not finished before the code continues. This has happened to me albeit, so far, only with pre-Excel 2007.

To locate where it may going wrong (before "WAIT2"), put a break (or move MsgBox "WAIT2" statement) to a place before where it is now, then F5 to it and F8 from it, to see whether it still falls over or not, then again move the break/msgbox statement in the approprate direction to narrow down where it's tripping up. If you were to use the binary-chop method with discipline (where you place the break mid-way between the last known OK location and the last known not-OK location) even with many lines of code you'd quickly locate the problem line (10 trial breakpoints should do it for 1000 lines of code).
 
Upvote 0
Hi P45Cal,

Thanks for that. I am in middle on making changes suggested by Norie. As soon as I'm done, if they don't help I will try you suggestions.
Thanks again to you, Norie and Mole. I really appreciate the fact that you guys are investing time and effort into the problems o0f a stranger.

Thanks,

Alan
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
I added my macro's to the quick access toolbar but the buttons disappear when I run the code
Replies
8
Views
358
Jurg55
J
M
  • Question Question
Excel Debugger Extremely Slow
2
Replies
13
Views
442
missingsc
M
S
  • Solved
Macro runs different when stepping through code
Replies
3
Views
458
bobsan42
bobsan42
D
  • Question Question
F8 Has Stopped working for me
Replies
5
Views
977
Doug Mac
D
J
  • Question Question
Using Sheets().Select in a Worksheet_Activate() macro
2
Replies
10
Views
471
jdun
J

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

Share this page

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
Back
Top