+ Reply to Thread
Results 1 to 6 of 6

Loop code

Hybrid View

  1. 02-26-2013, 06:37 AM #1
    martinp1
    martinp1 is offline
    Registered User
    Join Date
    09-10-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    10

    Loop code

    Hi All

    I'm after something I know will be really simple but if you could help me that would be great!

    I have a code that works well, it starts off at an invoicing sheet, then takes data from a tab in a quoting spreadsheet, then once finished, selects the next row in the invoicing sheet and the next tab in the quoting sheet

    I just want to add a loop to it that says if cell B8 says "or" stop, but it it doesn't then execute the code below

    A nice to have to be a message box to tell me how many loops it went through

    Thanks again, I'm tearing my hair out as I know that this is simple!

    Martin
    Reply With QuoteRegister To Reply
  2. 02-26-2013, 06:44 AM #2
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Loop code

    Martin

    Should be straightforward, can you post the code you have so far?
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  3. 02-26-2013, 06:52 AM #3
    martinp1
    martinp1 is offline
    Registered User
    Join Date
    09-10-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Loop code

    Thanks, it's below. The cell B8 which is the condition is in the Master QM file, but you start from the Quote index file

    Sub allabove()
'to add to all macro at very top
'description from QM
' start from SR number





ActiveCell.Offset(0, 4).Select



Windows("Seacroft Master QM 2012 v4.xlsm").Activate


Range("c4").copy

Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
ActiveCell.Offset(0, 3).Select

'units per case


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("c6").copy

Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
ActiveCell.Offset(0, 1).Select

'cases per pallet

Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("c7").copy

Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
ActiveCell.Offset(0, 1).Select

'cost per case

Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("m101").copy

Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

ActiveCell.Offset(0, 2).Select

'fixed cost

ActiveCell.Offset(0, 15).Select


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("n84").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("s84").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("X84").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("ac84").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate
ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'break

Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("ah84").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break

Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("am84").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'end


ActiveCell.Offset(0, -20).Select

ActiveCell.FormulaR1C1 = "=RC[15]+RC[16]+RC[17]+RC[18]+RC[19]+RC[20]"

ActiveCell.copy
ActiveCell.PasteSpecial xlPasteValues

ActiveCell.Offset(0, 13).Clear
ActiveCell.Offset(0, 14).Clear
ActiveCell.Offset(0, 15).Clear
ActiveCell.Offset(0, 16).Clear
ActiveCell.Offset(0, 17).Clear
ActiveCell.Offset(0, 18).Clear
ActiveCell.Offset(0, 19).Clear
ActiveCell.Offset(0, 20).Clear
ActiveCell.Offset(0, 21).Clear
ActiveCell.Offset(0, 22).Clear
ActiveCell.Offset(0, 23).Clear
ActiveCell.Offset(0, 24).Clear
ActiveCell.Offset(0, 25).Clear
ActiveCell.Offset(0, 26).Clear



ActiveCell.Offset(0, 1).Select



'labur rate

ActiveCell.Offset(0, 15).Select


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("m92").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("r92").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("w92").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("ab92").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate
ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'break

Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("ag92").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break

Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("al92").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'end


ActiveCell.Offset(0, -20).Select

ActiveCell.FormulaR1C1 = "=RC[15]+RC[16]+RC[17]+RC[18]+RC[19]+RC[20]"

ActiveCell.copy
ActiveCell.PasteSpecial xlPasteValues

ActiveCell.copy
ActiveCell.Offset(0, 25).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(0, -25).Select



  ActiveCell.FormulaR1C1 = "=RC[25]-RC[-1]"
  ActiveCell.copy
  ActiveCell.PasteSpecial xlPasteValues
  
  
ActiveCell.Offset(0, 13).Clear
ActiveCell.Offset(0, 14).Clear
ActiveCell.Offset(0, 15).Clear
ActiveCell.Offset(0, 16).Clear
ActiveCell.Offset(0, 17).Clear
ActiveCell.Offset(0, 18).Clear
ActiveCell.Offset(0, 19).Clear
ActiveCell.Offset(0, 20).Clear
ActiveCell.Offset(0, 21).Clear
ActiveCell.Offset(0, 22).Clear
ActiveCell.Offset(0, 23).Clear
ActiveCell.Offset(0, 24).Clear
ActiveCell.Offset(0, 25).Clear
ActiveCell.Offset(0, 26).Clear


ActiveCell.Offset(0, 1).Select


'total seconds


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("d91").copy

Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
ActiveCell.Offset(0, 2).Select

Selection.NumberFormat = "0"


'indirect time



ActiveCell.Offset(0, 15).Select


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("d93").copy
Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'break


Windows("Seacroft Master QM 2012 v4.xlsm").Activate

Range("d94").copy


Worksheets(ActiveSheet.Index - 1).Select


Windows("Seacroft Quote Index 2013 Reporting FOR INVOICE.xlsm").Activate

ActiveCell.Offset(0, 1).Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


ActiveCell.Offset(0, -16).Select

ActiveCell.FormulaR1C1 = "=RC[15]+RC[16]"

ActiveCell.copy
ActiveCell.PasteSpecial xlPasteValues

ActiveCell.Offset(0, 13).Clear
ActiveCell.Offset(0, 14).Clear
ActiveCell.Offset(0, 15).Clear
ActiveCell.Offset(0, 16).Clear
ActiveCell.Offset(0, 17).Clear
ActiveCell.Offset(0, 18).Clear
ActiveCell.Offset(0, 19).Clear
ActiveCell.Offset(0, 20).Clear
ActiveCell.Offset(0, 21).Clear
ActiveCell.Offset(0, 22).Clear
ActiveCell.Offset(0, 23).Clear
ActiveCell.Offset(0, 24).Clear
ActiveCell.Offset(0, 25).Clear
ActiveCell.Offset(0, 26).Clear

Selection.NumberFormat = "0"

ActiveCell.Offset(1, -15).Select
    Reply With QuoteRegister To Reply
  4. 02-26-2013, 07:14 AM #4
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Loop code

    Martin

    Is that code for one invoice?
    Reply With QuoteRegister To Reply
  5. 02-26-2013, 07:41 AM #5
    martinp1
    martinp1 is offline
    Registered User
    Join Date
    09-10-2012
    Location
    Yorkshire
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Loop code

    Yes, this updates 1 line in the invoice spreadsheet, which is one tab in the quote model spreadsheet, so I'd like the macro to work through all of the lines in the invoice sheet
    Reply With QuoteRegister To Reply
  6. 02-26-2013, 07:54 AM #6
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: Loop code

    Martin

    Where does B8 come into things?

    Also, what exactly are you looping?

    Sorry for the questions but the code is kind of hard to follow with all the jumping between workbooks.

    Actually, could you upload a sample workbook that had some sample data and sample invoice?

    Here's how you can do that:

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1