looping prob

briankimberly

New Member
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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Which worksheet is the range you are looping supposed to be on?

Sheets("Main Cal").Select

Does this work?
Code:
``````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``````

nope it skips over the LOI.copy part?

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.

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

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?

Replies
7
Views
148
Replies
3
Views
149
Replies
4
Views
106
Replies
7
Views
216
Replies
4
Views
231

1,202,966
Messages
6,052,846
Members
444,603
Latest member
dustinjmangum

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.

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

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