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

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

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
--
HTH...

Jim Thomlinson


"Sunil Patel" wrote:

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

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


"Sunil Patel" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>