For Statement cannot support 2 Next commands in the same routine (1 Viewer)

scott-atkinson

scott-atkinson

I'm with the Witch.......
Local time
Today, 10:08
Joined
Aug 31, 2006
Messages
1,622
Guys,

I hope you can help, my code is below;

I am trying to use a For Next routine within the same Procedure with an error trap. And I am getting an error message stating Next without For.

My code basically uses a For Next Loop to perform a copy and paste function but with two different criteria's, if criteria one is not met, then the error trap prompts another part of the routine to run to satisfy the alternative criteria, but I am trying to confine this within one For Next loop

Code: 
Application.ScreenUpdating = False
Dim MS As String, Fis As String, sku As Long
' Counts the number of Master Skus in range and applies to a Variable
    Sheets("List").Select
    Cells(1, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    sku = Selection.Count
    Cells(1, 1).Select
    MS = ActiveCell.Value
' Opens the Archive Workbook
    Workbooks.Open Filename:= _
        "I:\H914 Development and Supply Chain\AWR\FLM Archive\FLM Archive - Homewares.xlsx"
        Cells(1, 1).Select
        
' Checks to see if the selected Master Sku exists in the Archive
For b = 1 To sku
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Range("B4").Select
    ActiveCell.Value = MS
    Windows("FLM Archive - Homewares.xlsx").Activate
    On Error GoTo A ' If Master Sku is not in Archive then call the procedure to create the Archive Entry
    Cells.Find(What:=MS, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
' continues with routine once existing Master Sku is found
' reverts to Line Monitor to copy new data
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Cells(6, 3).Select
    Fis = ActiveCell.Value ' assigns the first week of the Line Monitor to a variable
    Windows("FLM Archive - Homewares.xlsx").Activate
' navigates to week variable on archive worksheet
    Cells.Find(What:=Fis, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
' reverts to Line Monitor and copies data table
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Range("C6:BB19").Select
    Selection.Copy
' reverts to archive and pastes as values data table
    Windows("FLM Archive - Homewares.xlsx").Activate
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
' saves anc closes archive
    Cells(1, 1).Select
' reverts to Line Monitor
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Sheets("List").Select
    Selection.Offset(1, 0).Select
    MS = ActiveCell.Value
    Next b
    Application.ScreenUpdating = True
    End
    
A:
    
    Windows("FLM Archive - Homewares.xlsx").Activate
' selects the last cell in the worksheet
    Cells(1048576, 1).Select
' brings the curser up to the next available entry, if none then to the top of the worksheet, and then offsets by one row
    Selection.End(xlUp).Offset(2, 0).Select
' Puts the new Master Sku code into the valid cell
    ActiveCell.Value = MS
    Selection.Offset(1, 0).Select
' Reverts to Line Monitor to copy data
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
' Copies titles
    Range("A7:A19").Select
    Selection.Copy
' Pastes titles inot archive
    Windows("FLM Archive - Homewares.xlsx").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("A:A").EntireColumn.AutoFit
' places active cell next to Master Sku cell to paste data
    Selection.Offset(-1, 1).Select
' reverts to Line Monitor to copy data table
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Range("C6:BB19").Select
    Selection.Copy
' reverts to archive and pastes data formats and values
    Windows("FLM Archive - Homewares.xlsx").Activate
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
' saves and closes the archive
    Cells(1, 1).Select
' reverts to Line Monitor
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Sheets("List").Select
    Selection.Offset(1, 0).Select
    MS = ActiveCell.Value
    Next b
    Application.ScreenUpdating = True
End Sub
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 10:08
Joined
Jun 2, 2003
Messages
12,701
Well what it says is true, you have two Next b and only one For , you will need to use the dreaded GoTo and break out the Next b as a possible simple solution.

Brian
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 10:08
Joined
Jun 2, 2003
Messages
12,701
Can't you do the error checking before the For I'm somewhat rusty nowadays so having some difficulty deciphering the code but isn't. That the way to go? Then you either put it right which is what you appear to do or use two separate loops.

Brian
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 10:08
Joined
Jun 2, 2003
Messages
12,701
Another alternative might be to switch from a For Next loop to a Do Until mycount in SKU, you can add to mycount anywhere.

Brian
 
scott-atkinson

scott-atkinson

I'm with the Witch.......
Local time
Today, 10:08
Joined
Aug 31, 2006
Messages
1,622
I have tried all of these approaches, and both return an error message, I cannot have to Loops with only one Do Until, I also tried having just the one For Next statement with the Error Trap outside with a GOTO statement leading back into the For Next statement, but that produced a 91 Error Message basically saying that I cannot use a GOTO statement to jump back into a For Next loop... so I am stumped at present...
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 10:08
Joined
Jun 2, 2003
Messages
12,701
You don't jump back into the for next you jump within it.

For..

On Error goto A

.
.
.
.
Goto nextb

A:
.
.
.
Nextb:
Next b



Brian
 
scott-atkinson

scott-atkinson

I'm with the Witch.......
Local time
Today, 10:08
Joined
Aug 31, 2006
Messages
1,622
Brianwarnock said:
You don't jump back into the for next you jump within it.

For..

On Error goto A

.
.
.
.
Goto nextb

A:
.
.
.
Nextb:
Next b



Brian
Click to expand...

OK,

I have changed the code as suggested, and now have contained everything within one Loop.

The issue now is that during the first Loop, the Find function works fine, however on the Second Loop, I now get an Error 91 Message at the Find Function.. any ideas, this is driving me nuts..:banghead::banghead:

Code: 
Sub archive()
'
' archive_new Macro
'
Application.ScreenUpdating = False
Dim MS As String, Fis As String, sku As Long, works As Range
' Counts the number of Master Skus in range and applies to a Variable
    Sheets("List").Select
    Cells(1, 1).Select
    Range(Selection, Selection.End(xlDown)).Select
    sku = Selection.Count
    Cells(1, 1).Select
    MS = ActiveCell.Value
' Opens the Archive Workbook
    Workbooks.Open Filename:= _
        "I:\H914 Development and Supply Chain\AWR\FLM Archive\FLM Archive - Homewares.xlsx"
        Cells(1, 1).Select
        
' Checks to see if the selected Master Sku exists in the Archive


For b = 1 To sku

    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Sheets("Workings").Select
    Range("B4").Select
    ActiveCell.Value = MS
    Calculate
    Windows("FLM Archive - Homewares.xlsx").Activate
    On Error GoTo A ' If Master Sku is not in Archive then call the procedure to create the Archive Entry
    Cells.Find(What:=MS, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    On Error GoTo 0
' continues with routine once existing Master Sku is found
' reverts to Line Monitor to copy new data
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Cells(6, 3).Select
    Fis = ActiveCell.Value ' assigns the first week of the Line Monitor to a variable
    Windows("FLM Archive - Homewares.xlsx").Activate
' navigates to week variable on archive worksheet
    Cells.Find(What:=Fis, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
' reverts to Line Monitor and copies data table
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Range("C6:BB19").Select
    Selection.Copy
' reverts to archive and pastes as values data table
    Windows("FLM Archive - Homewares.xlsx").Activate
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
' saves anc closes archive
    Cells(1, 1).Select
' reverts to Line Monitor
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Sheets("List").Select
    Selection.Offset(1, 0).Select
    MS = ActiveCell.Value
   
    GoTo Nextb
    
    
    
A:
    
   
    Windows("FLM Archive - Homewares.xlsx").Activate
' selects the last cell in the worksheet
    Cells(1048576, 1).Select
' brings the curser up to the next available entry, if none then to the top of the worksheet, and then offsets by one row
    Selection.End(xlUp).Offset(2, 0).Select
' Puts the new Master Sku code into the valid cell
    ActiveCell.Value = MS
    Selection.Offset(1, 0).Select
' Reverts to Line Monitor to copy data
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
' Copies titles
    Range("A7:A19").Select
    Selection.Copy
' Pastes titles inot archive
    Windows("FLM Archive - Homewares.xlsx").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("A:A").EntireColumn.AutoFit
' places active cell next to Master Sku cell to paste data
    Selection.Offset(-1, 1).Select
' reverts to Line Monitor to copy data table
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Range("C6:BB19").Select
    Selection.Copy
' reverts to archive and pastes data formats and values
    Windows("FLM Archive - Homewares.xlsx").Activate
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
' saves and closes the archive
    Cells(1, 1).Select
' reverts to Line Monitor
    Windows("AWR Forward Line Monitor - Homewares.xlsm").Activate
    Sheets("List").Select
    Selection.Offset(1, 0).Select
    MS = ActiveCell.Value
Nextb:
 Next b
    Application.ScreenUpdating = True
End Sub
 
scott-atkinson

scott-atkinson

I'm with the Witch.......
Local time
Today, 10:08
Joined
Aug 31, 2006
Messages
1,622
I have now fixed this code.

I replaced the Error Trap routine with a Set Loop.

I basically created an Object using the Set command and then checked to see if the Object existed, if it did then it performed one routine, and if it didn't then it performed another routine.

I have posted the new code section below, the rest of the code is the same as posted previously, only the below section has been changed.

Code: 
   Set works = Cells.Find(What:=MS, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If works Is Nothing Then GoTo A
    Cells.Find(What:=MS, After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 10:08
Joined
Jun 2, 2003
Messages
12,701
I think that is the type of solution I was getting at in post 3 , but I'm very rusty on detail nowadays, 8 years retired, so glad you got it sorted.

Brian
 
You must log in or register to reply here.

Similar threads

B
Solved VBA code which exports data from Access to Excel and then loops through the Excel file
Replies
0
Views
521
BlueFllame
B
K
Solved MS Access Query of remote Database pulling partial data
2
Replies
26
Views
2,702
poliadisa
P
P
VBA compiler can't see For statement?
Replies
5
Views
852
The_Doc_Man
The_Doc_Man
Rx_
Excel VBA code to return used Column Count from the row of an ActiveCell
Replies
0
Views
2,038
Rx_
Rx_
R
Split Sentence Into 40 Character Whole Words,
Replies
2
Views
1,863
roccoau
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom