Can't seem to get an answer to this problem.

[Sunil Patel]

Hard to explain - so here is some code to fix and help a novice at the same
time ;-)

Sub Demo()
Dim rngTarget As Range, rngArea As Range, rngCell As Range
Dim i As Integer
'Set rngTarget =
Range("B3:C11,E3:F11,H3:I11,K3:L11,N3:O11,Q3:R11,T3:U11,B12:C20,E12:F20,H12:I20,K12:L20,N12:O20,Q12:R20")

' now try with this range BELOW - it does not work !!! Why ??

'Set
rngTarget("B3:C11,E3:F11,H3:I11,K3:L11,N3:O11,Q3:R11,T3:U11,B12:C20,E12:F20,H12:I20,K12:L20,N12:O20,Q12:R20,T12:U20,B21:C29,E21:F29,H21:I29,K21:L29,N21:O29,Q21:R29,T21:U29,B32:C40,E32:F40,H32:I40,K32:L40,N32:O40,Q32:R40,T32:U40,B41:C49,E41:F49,H41:I49,K41:L49,N41:O49,Q41:R49,T41:U49,B50:C58,E50:F58,H50:I58,K50:L58,N50:O58,Q50:R58,T50:U58")

'TRIED to split range into two and use Union but cells are numbered
differently !!

For Each rngArea In rngTarget.Areas
For Each rngCell In rngArea.Cells
rngCell.Value = i
i = i + 1
Next rngCell
Next rngArea

End Sub

 

[Jim Thomlinson]

Your string is too long... 328 characters. You need to split it up... Try this

Sub Demo()
Dim rngTarget As Range, rngArea As Range, rngCell As Range
Dim i As Integer
Set rngTarget =
Range("B3:C11,E3:F11,H3:I11,K3:L11,N3:O11,Q3:R11,T3:U11,B12:C20,E12:F20,H12:I20,K12:L20,N12:O20,Q12:R20")

' now try with this range BELOW - it does not work !!! Why ??

Set rngTarget = Union(rngTarget,
Range("Q12:R20,T12:U20,B21:C29,E21:F29,H21:I29,K21:L29,N21:O29,Q21:R29,T21:U29,B32:C40,E32:F40,H32:I40,K32:L40,N32:O40,Q32:R40,T32:U40,B41:C49,E41:F49,H41:I49,K41:L49,N41:O49,Q41:R49,T41:U49,B50:C58,E50:F58,H50:I58,K50:L58,N50:O58,Q50:R58,T50:U58"))

For Each rngArea In rngTarget.Areas
For Each rngCell In rngArea.Cells
rngCell.Value = i
i = i + 1
Next rngCell
Next rngArea

End Sub

 

[Bernie Deitrick]

Sunil,

Since you have a nice pattern, you could take advantage of the pattern:

Sub Demo2()
Dim rngTarget As Range
Dim rngCell As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer

Set rngTarget = Range("B3:C11")
With rngTarget
For k = 0 To 5
For j = 0 To 6
For Each rngCell In .Offset(k * 9 + IIf(k > 2, 2, 0), j * 3)
rngCell.Value = i
i = i + 1
Next rngCell
Next j
Next k
End With

End Sub

HTH,
Bernie
MS Excel MVP