for each cell in range....direction problem

[Sunil Patel]

Hi, please help a novice !!


As an example ...i have

Set v1 = Range("B1:C10,E1:F10")
Set v2 = Range"B11:B20,E11:E20)
when i use "for each cell in range v1" then cells are targeted
b1,b2,b3....b10,c1,c2,c3.... as discrete columns

BUT when i use validarea=Union(v1,v2) the cells are targeted
b1,b2,b3.....b10,b11,b12,13,14 as longer columns


In practice my ranges are

Set v1 =
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,T12:U20,B21:C29,E21:F29,H21:I29,K21:L29,N21:O29,Q21:R29,T21:U29")
Set v2 =
Range("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")

if i try and make v1 my entire range i get an error, hence i need to split
into two ranges.

how do i run some code first with v1 and the with v2


Sunil

 

[Bunter_22]

Hi Sunil,

In V1 you have B3:C11 then later B12:C20 then later B21:C29 why can't
that be written as B3:C29 similarly you have this with all of them? You
could then combine V1 and V2 and your entire range would be made of 2/3
of what you currently have as V1.

Have I misinterpreted what you were asking for?

James

 

[Dave Ramage]

Sunil,

Split up the areas of the range and lop through each area separately, like
this:

Sub Demo()
Dim rngTarget As Range, rngArea As Range, rngCell As Range
Dim i As Integer

i = 1
Set rngTarget = ActiveSheet.Range("B1:C10,E1:F10,B11:B20,E11:E20")
For Each rngArea In rngTarget.Areas
For Each rngCell In rngArea.Cells
rngCell.Value = i
i = i + 1
Next rngCell
Next rngArea

End Sub

Cheers,
Dave

 

[davidm]

You have a syntax problem. Fix it as follows:

Set v1 = Union(Range("B1:C10"),Range("E1:F10"))
Set v2 = Union(Range"B11:B20"),Range("E11:E20")

 

[Sunil Patel]

Thanks but i cant get it to accept my range i.e

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,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")