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

  • Thread starter Thread starter Sunil Patel
  • Start date Start date
S

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