Is it possible to have 42 ranges in an array to loop through them with (for each cell in ...)

R

rouzacct

Board Regular
Joined
Aug 31, 2010
Messages
65
Code: 
Sub CALC2_()

    Dim R As Range
    Dim Rng As Range

    Set Rng = ThisWorkbook.Sheets("INV").Range("L2:L1000,P2:P1000,T2:T1000,X2:X1000,AB2:AB1000,AF2:AF1000,AJ2:AJ1000,AN2:AN1000,AR2:AR1000,AV2:AV1000,AZ2:AZ1000,BD2:BD1000,BH2:BH1000,BL2:BL1000,BP2:BP1000,BT2:BT1000,BX2:BX1000,CB2:CB1000,CF2:CF1000,CJ2:CJ1000,CN2:CN1000,CR2:CR1000,CV2:CV1000,CZ2:CZ1000,DD2:DD1000,DH2:DH1000,DL2:DL1000,DP2:DP1000,DT2:DT1000,DX2:DX1000,EB2:EB1000,EF2:EF1000,EJ2:EJ1000,EN2:EN1000,ER2:ER1000,EV2:EV1000,EZ2:EZ1000,FD2:FD1000,FH2:FH1000,FL2:FL1000,FP2:FP1000,FT2:FT1000")
       

For Each cell In Rng
' do something

The code above gives an error 1004, it seems to surpass the limit of such range array

BUT if i take most of the ranges off it works


Code: 
Sub CALC2_()
   
 Dim R As Range
    Dim Rng As Range

 Set Rng = ThisWorkbook.Sheets("INV").Range("L2:L1000,P2:P1000,T2:T1000,X2:X1000,AB2:AB1000,AF2:AF1000,AJ2:AJ1000,AN2:AN1000,AR2:AR1000,AV2:AV1000,AZ2:AZ1000")
       
For Each cell In Rng
'Do something

The code above works.

What can I do at this point ?
I need to include all 42 columns in one range so I can loop through and Eventually add in a
Do while (range is not empty) to break the loop as soon at it hits a range that is empty.
 
Sort by date Sort by votes
It works for me I just retested it.
I'm using Excel 2013. Not sure about what features may have changed since 2007.

In your post # 5 you showed:
Code: 
For Each c In ThisWorkbook.Sheets("[COLOR=#ff0000]VVV[/COLOR]").Range(Cells(2, i),
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Norie said:
Does this work?
Code: 
    With ThisWorkbook.Sheets("INV")
        Set Rng = Intersect(.Range("L2:L1000"), .Range("L:L,P:P,T:T,X:X,AB:AB,AF:AF,AJ:AJ,AN:AN,AR:AR,AV:AV,AZ:AZ,BD:BD,BH:BH,BL:BL,BP:BP,BT:BT,BX:BX,CB:CB,CF:CF,CJ:CJ,CN:CN,CR:CR,CV:CV,CZ:CZ,DD:DD,DH:DH,DL:DL,DP:DP,DT:DT,DX:DX,EB:EB,EF:EF,EJ:EJ,EN:EN,ER:ER,EV:EV,EZ:EZ,FD:FD,FH:FH,FL:FL,FP:FP,FT:FT"))
    End With
Click to expand...


Hi Norie Yes I did try yours and it seems to only act on Column L
 
Upvote 0
The code I posted doesn't 'act' on anything, it only sets up the range.

What are you actually trying to do with the range?
 
Upvote 0
Norie said:
The code I posted doesn't 'act' on anything, it only sets up the range.

What are you actually trying to do with the range?
Click to expand...

@Norie,

I inserted your Range method into my Inventory tracker

Code: 
[COLOR=#333333]Sub InventoryHelper_()[/COLOR]


Dim R1 As Range


Set R1 = ThisWorkbook.Sheets("INV").Range("L2:L1000")


For Each cell In R1




        '  IF INVENTAIRE IS EMPTY AND COMMANDE IS EMPTY
        If IsEmpty(cell.Value) And IsEmpty(cell.Offset(0, 2).Value) Then
        
                'THEN
                
                'EPUISE = ""
                cell.Offset(0, 1).Value = ""
                
                'TOTAL = ""
                cell.Offset(0, 3).Value = ""
                
                                
                
        '  IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NUMERIC
        ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And IsNumeric(cell.Offset(0, -1).Value) Then
        
                'THEN
                
                'EPUISE = LAST TOTAL - INVENTAIRE
                cell.Offset(0, 1).Value = cell.Offset(0, -1).Value - cell.Value
                
                'TOTAL = INVENTAIRE + COMMANDE
                cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
                
        '  IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL is either "NOUVEAU" or "nouveau"
        ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And (cell.Offset(0, -1).Value = "NOUVEAU" Or cell.Offset(0, -1).Value = "nouveau")    Then
        
                'THEN
                
                'EPUISE = "NOUVEAU"
                cell.Offset(0, 1).Value = "NOUVEAU"
                
                'TOTAL = INVENTAIRE + COMMANDE
                cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value


        
         ' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NOT NUMERIC
        ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And Not IsNumeric(cell.Offset(0, -1).Value) Then
        
                'THEN
                
                'EPUISE = "TEXT dans TOTAL"
                cell.Offset(0, 1).Value = "TEXT dans TOTAL"
                
                'TOTAL = INVENTAIRE + COMMANDE
                cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
                
                
                
             ' IF INVENTAIRE IS NOT NUMERIC AND COMMANDE IS NUMERIC
            ElseIf Not IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) Then
        
                'THEN
                
                'EPUISE = "TEXTE dans INV"
                cell.Offset(0, 1).Value = "TEXTE dans INV"
                
                'TOTAL =
                cell.Offset(0, 3).Value = cell.Offset(0, 2).Value
                
            
               ' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NOT NUMERIC AND LAST TOTAL IS NUMERIC
            ElseIf IsNumeric(cell.Value) And Not IsNumeric(cell.Offset(0, 2).Value) And IsNumeric(cell.Offset(0, -1).Value) Then
            
                'THEN
                
                'EPUISE = LAST TOTAL - INVENTAIRE
                cell.Offset(0, 1).Value = cell.Offset(0, -1).Value - cell.Value
                
                'TOTAL = "TEXTE dans COMMANDE"
                cell.Offset(0, 3).Value = "TEXTE dans COMMANDE"
                


                'IF INVENTAIRE IS NOT NUMERIC OR COMMANDE IS NOT NUMERIC
            ElseIf Not IsNumeric(cell.Value) Or Not IsNumeric(cell.Offset(0, 2).Value) Then
                        
        
                'THEN
                
                'EPUISE = "TEXT"
                cell.Offset(0, 1).Value = "TEXT"
                
                'TOTAL = "TEXT"
                cell.Offset(0, 3).Value = "TEXT"
          


            
End If


Next



 [COLOR=#333333]End Sub[/COLOR]


In this code right now it is not intergrated I am at work I dont have my workbook handy right now so I just copied from my other post

The essential route is to have the code working with a stable range defined.


Also wondering if I should change things up to only calculate one row at a time all the way to the right to not lock up the PC everytime a new entry is imputed in INV column or COMMANDE column

But I did try your range last night and I saw it working on Column L and not the others.


btw my other post is at

How to repeat VBA to the next 4th column on right if there is values on that column and how to highlight text and negative numbers
 
Upvote 0
Eh, I'm not seeing the code I posted in that code.:eek:

The only range referenced in the code is L2:L1000, here.
Code: 
Set R1 = ThisWorkbook.Sheets("INV").Range("L2:L1000")
 
Upvote 0
Norie said:
Eh, I'm not seeing the code I posted in that code.:eek:

The only range referenced in the code is L2:L1000, here.
Code: 
Set R1 = ThisWorkbook.Sheets("INV").Range("L2:L1000")
Click to expand...


\Sorry I was at work I didnt have my spreadsheet handy and On my phone it wasnt possible to cut and pase your code in, But last night I did have your code inserted in my macro...

and what i meant by it only acted on L
is that once I placed your range declaration in my macro it only wrote in Column L
although i had your range instead of what you see right now.

like this
and i just tried it again.. still same result

Code: 
Sub CALC2_()




Application.ScreenUpdating = False


Dim rng As Range


       With ThisWorkbook.Sheets("INV")
        Set rng = Intersect(.Range("L2:L1000"), .Range("L:L,P:P,T:T,X:X,AB:AB,AF:AF,AJ:AJ,AN:AN,AR:AR,AV:AV,AZ:AZ,BD:BD,BH:BH,BL:BL,BP:BP,BT:BT,BX:BX,CB:CB,CF:CF,CJ:CJ,CN:CN,CR:CR,CV:CV,CZ:CZ,DD:DD,DH:DH,DL:DL,DP:DP,DT:DT,DX:DX,EB:EB,EF:EF,EJ:EJ,EN:EN,ER:ER,EV:EV,EZ:EZ,FD:FD,FH:FH,FL:FL,FP:FP,FT:FT"))
  
    
    For Each cell In rng




        '  IF INVENTAIRE IS EMPTY AND COMMANDE IS EMPTY
        If IsEmpty(cell.Value) And IsEmpty(cell.Offset(0, 2).Value) Then
        
                'THEN
                
                'EPUISE = ""
                cell.Offset(0, 1).Value = ""
                
                'TOTAL = ""
                cell.Offset(0, 3).Value = ""
                
                                
                
        '  IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NUMERIC
        ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And IsNumeric(cell.Offset(0, -1).Value) Then
        
                'THEN
                
                'EPUISE = LAST TOTAL - INVENTAIRE
                cell.Offset(0, 1).Value = cell.Offset(0, -1).Value - cell.Value
                
                'TOTAL = INVENTAIRE + COMMANDE
                cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
                
        '  IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NUMERIC
        ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And (cell.Offset(0, -1).Value = "NOUVEAU" Or cell.Offset(0, -1).Value = "nouveau") Then
        
                'THEN
                
                'EPUISE = LAST TOTAL - INVENTAIRE
                cell.Offset(0, 1).Value = "NOUVEAU"
                
                'TOTAL = INVENTAIRE + COMMANDE
                cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value


        
         ' IF INVENTAIRE IS NUMERIC AND COMMANDE IS NUMERIC AND LAST TOTAL IS NOT NUMERIC
        ElseIf IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) And Not IsNumeric(cell.Offset(0, -1).Value) Then
        
                'THEN
                
                'EPUISE = "TEXT dans TOTAL"
                cell.Offset(0, 1).Value = "TEXT dans TOTAL"
                
                'TOTAL = INVENTAIRE + COMMANDE
                cell.Offset(0, 3).Value = cell.Value + cell.Offset(0, 2).Value
                
                
                
             ' IF INVENTAIRE IS NOT NUMERIC AND COMMANDE IS NUMERIC
            ElseIf Not IsNumeric(cell.Value) And IsNumeric(cell.Offset(0, 2).Value) Then
        
                'THEN
                
                'EPUISE = "TEXTE dans INV"
                cell.Offset(0, 1).Value = "TEXTE dans INV"
                
                'TOTAL =
                cell.Offset(0, 3).Value = cell.Offset(0, 2).Value
                
            
                
            ElseIf IsNumeric(cell.Value) And Not IsNumeric(cell.Offset(0, 2).Value) And IsNumeric(cell.Offset(0, -1).Value) Then
            
                'THEN
                
                'EPUISE = LAST TOTAL - INVENTAIRE
                cell.Offset(0, 1).Value = cell.Offset(0, -1).Value - cell.Value
                
                'TOTAL = "TEXTE dans COMMANDE"
                cell.Offset(0, 3).Value = "TEXTE dans COMMANDE"
                


                
            ElseIf Not IsNumeric(cell.Value) Or Not IsNumeric(cell.Offset(0, 2).Value) Then
                        
        
                'THEN
                
                'EPUISE = "TEXT"
                cell.Offset(0, 1).Value = "TEXT"
                
                'TOTAL = "TEXT"
                cell.Offset(0, 3).Value = "TEXT"
          
        Exit Sub
        End If


Next
  End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
My Aswer Is This said:
Try using this script:

It sets all the non empty cells to interior color of Red.
Change this to what you want. If not then exit script.
Code: 
Sub Select_Me()
Dim i As Long
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
    For Each c In ThisWorkbook.Sheets("INV").Range(Cells(2, i), Cells(1000, i))
        If c.Value <> "" Then
        c.Interior.ColorIndex = 3
        Else
        Exit Sub
        End If
    Next
    
    Next
Application.ScreenUpdating = True
End Sub
Click to expand...


THIS WORKED,
WITH A FRESH LOOK AT IT TODAY I WAS ABLE TO GET IT WOKRING

Code: 
Dim rng As Range

Dim i As Long
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
Set rng = ThisWorkbook.Sheets("INV").Range(Cells(2, i), Cells(20, i))


    For Each cell In rng
 
Upvote 0
rouzacct said:
With ThisWorkbook.Sheets("INV")
Set Rng = Union(.Range("L2:L1000,P2:P1000,T2:T1000,X2:X1000,AB2:AB1000,AF2:AF1000,AJ2:AJ1000,AN2:AN1000,AR2:AR1000"), _
.Range("AV2:AV1000,AZ2:AZ1000,BD2:BD1000,BH2:BH1000,BL2:BL1000,BP2:BP1000,BT2:BT1000,BX2:BX1000"), _
.Range("CB2:CB1000,CF2:CF1000,CJ2:CJ1000,CN2:CN1000,CR2:CR1000,CV2:CV1000,CZ2:CZ1000,DD2:DD1000"), _
.Range("DH2:DH1000,DL2:DL1000,DP2:DP1000,DT2:DT1000,DX2:DX1000,EB2:EB1000,EF2:EF1000,EJ2:EJ1000"), _
.Range("EN2:EN1000,ER2:ER1000,EV2:EV1000,EZ2:EZ1000,FD2:FD1000,FH2:FH1000,FL2:FL1000,FP2:FP1000,FT2:FT1000")) End With
Click to expand...
I tried this and I get an error highlighting the _ underscore saying its not a good character
Click to expand...
That is because you did not copy or replicate my code accurately. If you look back at the code in post 4 you will see that there is a space immediately before each "_" character. What you have posted in post 7 does not, hence the error. Give it a try with the spaces included.
 
Last edited:
Upvote 0
Well glad you have things working for you. My script worked for me. Not sure what your entire script loooks like.


My script looks very clean to me.
rouzacct said:
THIS WORKED,
WITH A FRESH LOOK AT IT TODAY I WAS ABLE TO GET IT WOKRING

Code: 
Dim rng As Range

Dim i As Long
Application.ScreenUpdating = False
    For i = 12 To 176 Step 4
    
Set rng = ThisWorkbook.Sheets("INV").Range(Cells(2, i), Cells(20, i))


    For Each cell In rng
Click to expand...
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Average sales for the previous 3 months for each product
Replies
2
Views
341
paun_shotts
P
M
  • Question Question
Improve Holiday Planner by using named ranges in an array?
Replies
1
Views
560
mrbenn
M
E
  • Solved
shorten 2 divide formulas
2 3
Replies
24
Views
1K
excelNewbie22
E
RAJESH1960
  • Solved
Conditional formatting
Replies
4
Views
634
Fluff
Fluff
PuntingJawa
  • Solved
Copying Sheet from one Workbook to another.
3 4 5
Replies
48
Views
3K
johnnyL
J

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top