My code works until I nest it inside another For Loop, am I missing something

[jpk89]

Sub Move3Mo()

Dim CL As Range
Dim RNG As Range
Dim WS As Worksheet
Application.ScreenUpdating = False


For Each WS in ThisWorkBook.Worksheets

Set RNG = Range("c9:C200")

For Each CL In RNG

If CL.HasFormula = True Then
CL.Offset(, 3) = CL.Value

ElseIf IsNumeric(CL) = True Then
CL.Offset(, 3) = CL.Value

Else

End If

Next CL

Next WS

End Sub

 

[V_Malkoti]

Do you want to repeat the inner loop code for all worksheet from outer loop? If yes, then try changing your line to:

Set RNG = [COLOR=#0000ff]WS.[/COLOR]Range("c9:C200")

 

[jpk89]

Thanks, I always get hung up on how to correctly specify an object. If you don't mind, why is range insufficient and something like workbooks.ws.range is unnecessary?

 

[V_Malkoti]

If you don't mention the parent object, then VBA by default refers to currently active object as its parent.

If you have Sheet1 active then you can't write Range("A1:A10") to access range from Sheet2. That's because in absence of a parent qualifier object VBA will look for it in Sheet1. If you want to refer to that range in Sheet2, then you need to specify that: Sheets("Sheet2").Range("A1:A10").

Similarly if you write Sheets("Sheet1") to refer to a worksheet, then VBA will look for Sheet1 in currently active workbook; not other workbooks that you might have open at the moment.