Nested Loop Offset Glitch - Object required (Error 424)

[Arturo]

Hello –

When I get to
“ther.Offset(bb - 1, 0).Value = varA * 5â€
Object required (Error 424) generates.
What am I doing wrong to alter a cell’s content in sheet2 from a value in
sheet1*5?


Sub Rand1()
Dim myRange As Range
Dim ro As Integer
Dim co As Integer
Dim aa, bb As Integer
Dim varA As String

' Application.ScreenUpdating = False
Set myRange = Worksheets("Sheet1") _
.Range("A1").CurrentRegion
ro = myRange.Rows.Count
co = myRange.Columns.Count
ther = Worksheets("Sheet2").Range("F2").Address
For aa = 2 To ro
varA = Worksheets("Sheet1").Cells(aa, 6).Value
' MsgBox "Row " & aa & " # " & varA
For bb = 2 To 11

ther.Offset(bb - 1, 0).Value = varA * 5 ‘Runtime Error 424

Next bb
Next aa
' Application.ScreenUpdating = True
End Sub

Appreciatively,
Arturo

 

[JE McGimpsey]

You set the implicit variant variable "ther" to a string in

ther = Worksheets("Sheet2").Range("F2").Address

(That's one of the problems with not using Option Explicit at the top of
your module - any variables you don't declare are implicitly declared as
variants, so you won't get a type mismatch when you try to use it
improperly.)


Instead include

Dim ther as Range

and use

Set ther = Worksheets("Sheet2").Range("F2")