looping prob

[briankimberly]

for some reason the code works correctly (moving down to new row in a range B2 is the header and goes to B15) the first time through, but then when i try to loop it again instead of starting on the first "LOI" (B3) it skips the first row and moves the second (B4)? any thoughts on way that would be the case?

Sheets("Main Cal").Select
Dim bottomA As Integer
bottomA = Range("B" & Rows.Count).End(xlUp).Row
Dim LOI As Range
Dim ws As Worksheet
For Each LOI In Range("B3:B" & bottomA)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(LOI.Value)
On Error GoTo 0
If ws Is Nothing Then
Sheets("Main Cal").Select
LOI.Select
Selection.Copy
Selection.Offset(0, 7).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
Next LOI

 

[Norie]

Which worksheet is the range you are looping supposed to be on?

 

[briankimberly]

Sheets("Main Cal").Select

 

[Norie]

Does this work?

Dim LOI As Range
Dim ws As Worksheet
Dim bottomA As Long

    bottomA = Sheets("Main Cal").Range("B" & Rows.Count).End(xlUp).Row
    
    For Each LOI In Sheets("Main Cal").Range("B3:B" & bottomA)
    
        Set ws = Nothing
        
        On Error Resume Next
        Set ws = Worksheets(LOI.Value)
        On Error GoTo 0
        
        If ws Is Nothing Then
            LOI.Copy
            LOI.Offset(0, 7).PasteSpecial Paste:=xlPasteValues
        End If

    Next LOI

 

[briankimberly]

nope it skips over the LOI.copy part?

 

[Norie]

If you set through the code what's the value of bottomA?

PS Is there any more to the code? Perhaps something that's deleting/clearing cells.

 

[briankimberly]

the value comes up ok its 15. and i have values until b15. something is happening where it is skipping the first "LOI" maybe beacuse there is a value already saved in Set ws = Worksheets(LOI.Value)? the first time around maybe it works since that is "Nothing" ? what do you think? thx again for your help

 

[Norie]

You are setting ws to Nothing on every iteration of the loop and it's Nothing before the loop starts.

Is the loop definitely not being executed?

What happens if you remove the On Error... stuff?