loop through ranges

April 1, 2009 at 14:52:31
Specs: Windows XP
With help from others in the this forum (you know who you are) I've been able to do some transposing and formatting with Excel 2007 macros.

I now want to format 64 columns after transposing data. The formatting is below, and it works when drilling down a single column. But I'm having a hard time figuring out how to format the first column (Col H) loop to the second column over (Col J), format, then Col L, format, etc. down to (Col ED).

I just can't figure out the nesting loops to finish this.

Sub FormatAfterTranspose()
    
    'Still need to have a nested For or Do While loop that after formatting
    'the first column ("H" or myDataRng) the loop will move over two columns
    'and repeat the format to the end
      
    For rw = 1 To 26
    
        Dim myDataRng, myFlagRng As Range
        Set myDataRng = Range("H" & rw)  'this is the data result column
        Set myFlagRng = Range("I" & rw)  'this is the data flag column
        
        'Format and add a "<" sign to any numbers in Range "H"
        'that has a "U" flag in Range "I"
        
        If myDataRng < 1 And myFlagRng = "U" Then _
            myDataRng.NumberFormat = """< ""#,##0.00 "
        
        If myDataRng >= 1 And myDataRng <= 9.9 And _
                myFlagRng = "U" Then myDataRng.NumberFormat = """< ""#,###0.0 "
                
        If myDataRng >= 10 And myFlagRng = "U" Then _
            myDataRng.NumberFormat = """< ""#,### "
        
          
        'Format numbers in Range "H" that has an empty cell ("") in Range "I"
        
        If myDataRng < 1 And myFlagRng = "" Then _
            myDataRng.NumberFormat = "#,##0.00 "
        
        If myDataRng >= 1 And myDataRng <= 9.9 And _
            myFlagRng = "" Then myDataRng.NumberFormat = "#,###0.0 "
                         
        If myDataRng >= 10 And myFlagRng = "" Then _
            myDataRng.NumberFormat = "#,### "
      
      
        'Format numbers in Range "H" that has a "D" in Range "I"
        
        If myDataRng < 1 And myFlagRng = "D" Then _
            myDataRng.NumberFormat = "#,##0.00 "
        
        If myDataRng >= 1 And myDataRng <= 9.9 And _
            myFlagRng = "D" Then myDataRng.NumberFormat = "#,###0.0 "
        
        If myDataRng >= 10 And myFlagRng = "D" Then _
            myDataRng.NumberFormat = "#,### "
            
   Next
  
End Sub


See More: loop through ranges

Report •


#1
April 1, 2009 at 18:39:33
You need to broaden your horizons!

There are multiple ways to refer to a cell or range in VBA.

Range("H3") = Cells(3,8)

Range("H" & rw) = Cells(rw, 8)

Range("H3:K10") = Range(Cells(3, 8), Cells(10, 11))

The key point is that Columns have numbers just like Rows. The syntax for the Cells property is Cells(RowNum, ColNum).

RowNum and ColNum can be fixed numbers, variables or functions. You can even use values from cells in a spreadsheet as RowNum and ColNum:

Sub H3GoesGreen()
'Let's Turn H3 Green The Hard Way
  r = 4
  Range("A1") = 2
   Cells(r - 1, WorksheetFunction.Sum(Range("A1") * 2, 4)).Interior.ColorIndex = 4
End Sub

F8 through these 2 pieces of code and watch what happens:

Sub MyVerticalLoop()
 For myCol = 1 To 10
  For myRow = 1 To 10
   myVal = myVal + 1
   Cells(myRow, myCol) = myVal
  Next
 Next
End Sub


Sub MyHorizontalLoop()
 For myRow = 1 To 10
  For myCol = 1 To 10
   myVal = myVal + 1
   Cells(myRow, myCol) = myVal
  Next
 Next
End Sub


Report •

#2
April 7, 2009 at 12:29:09
Thanks, derbydad.

I didn't know about the F8 key stepping through each line of code. Pretty cool. Also thanks for expanding my horizons on the multiple ways for referring to rows/cols.

I get the concept of looping through the ranges and have played around with my code, but I get stuck with trying to loop through each column AND formatting a cell based on a value in the adjacent cell, then move on to the next column, to the end of the populated range.

I feel that I am close but it's the conditional formatting as part of the loop that causes my head to hurt...and walk away frustrated.


Report •

#3
April 7, 2009 at 14:53:03
If you didn't know about the F8 key, then you probably don't know about the Watch window or hovering over variables.

Right click a variable - or any expression within your code that will resolve to a value - and choose Add Watch. Use the Watch window to see the value and other info about the variable/expression as you step through the code.

Try myDataRng and myDataRng.NumberFormat

Hovering over some - but not all - variables and expressions will also show the value.

Now, back to your immediate issue...

Based on your OP, I think the trouble may be related to the fact that you are using Set myDataRng = Range("H" & rw) .

Since you've hard coded the column letter, there's nothing to loop on. Well, actually there is, but I don't know if I would do that way.

These 2 pieces of code both loop from H4:L4. The first piece of code starts with your Set method and offsets from there, the second doesn't bother to Set anything. You choose which method you like better.

Sub IncrementColumnSetMethod()
 rw = 4
 Set myDataRng = Range("H" & rw)
  For myCol = 0 To 4
   Cells(rw, myDataRng.Column + myCol) = myCol
  Next
End Sub

Sub IncrementColumnCellsMethod()
 rw = 4
  For myCol = 8 To 12
   Cells(rw, myCol) = myCol
  Next
End Sub

That is what I was hinting at when I said that RowNum and ColNum can be fixed numbers, variables or functions.

You can loop on the variable myCol, but you can change the actual Column you are looking while inside the loop at with things like myCol + 1 and myCol - 1.

Let's make H4 = I1

myRow = 4 'Row 4
myCol = 8 'Column H
Cells(myRow, myCol) = Cells(myRow - 3, myCol + 1)


Report •

Related Solutions

#4
April 9, 2009 at 07:23:38
Thanks derbydad.

FYI: I couldn't get back logged in as "russells" and had to re-register.

It must be pretty obvious by now that my VBA knowledge is lacking quite a bit. Hence, my reason for being in this forum. My limited background in coding stems back to the late 90's and was pretty much only C and Perl, with a little bit of ASP. I haven't done much coding for a long time and object oriented language syntax like VBA is still causing my head to spin.

So I appreciate your patience. And your help. The transposing macro and format change you showed me have helped immensely.

As for my current problem:

I still don't see how the loop works for checking a cell for a value and, based on the value of that cell, changing the format of the adjacent cell to the left.

In other words, the macro needs to check down two columns for 26 rows (H2:I27), execute the cell value check/change, then back to the top and run again (J2:K27) until it reaches (ED2:EE27). The value checking consists of several lines of If...Then statements to run inside of what I think should be two For.. loops.

I get how the loop can run on a single column and/or row, but not on two columns at a time before moving on to the end of a range.

I recall in another post (Tricky Transposing in Excel 2007, Response Number 13), that you said I could do the transposing and format change in one macro.

derbydad: BTW - these don't have to be separate macros. You could format the original cells, transpose the data and format the new cells all in one macro. Just combine everything and make sure the segments are in the correct order.

The problem is I haven't figured out how to do that either. Maybe that would be the best method since the format of the original data was changed prior to transposing, but the format was lost after transposing into the format of the new cells.



Report •

#5
April 9, 2009 at 10:07:26
I'm purposely not rewriting your code for you for 2 reasons:

1 - I'd never get exactly right without your exact data - which I don't want.
2 - If you give a man a fish, you feed him for a day, etc.

Let's take the easy question first - combining the macros.

Just like any other coding language, VBA code is just a bunch of lines of instruction, so you can easily combine sections from various macros. In some cases this is actually more efficient since you can assign variables once and use them throughout the code.

In a generic sense, the combined code would look like this - there's no magic here, we're just transposing the data and once that's done we loop through the "new" data and format it.

Sub TransposeAndFormat
'
'Run code to transpose data
'
  For Each DataPiece In Range("F2:F4")
         'Do all the things that transpose the data from F to H
  Next
'
'Run code to format the data now that it's in H
'
 For rw = 1 To 26
'
        If myDataRng < 1 And myFlagRng = "U" Then _
            myDataRng.NumberFormat = """< ""#,##0.00 "
'etc
'etc
End Sub

In many cases, it takes nothing more than a Copy/Paste to combine 2 macros into 1.

OK, as for your other question, looping on both Rows and Columns...

Re-read the last part of Response 3, starting with You can loop on the variable myCol

If you change the method you are using to refer to the cells it might make the understanding easier. Don't use Set. Use the Cells method.

We're going to format B1:B3, D1:D3 and F1:F3, based on the values in A1:A3, C1:C3 and E1:E3 respectively. I'm checking for a single condition, obviously you'll be checking for multiple conditions.

We'll do it in 3 different ways, eventually condensing it to a nested loop.

First we'll hardcode the Row and Column values.

Sub FormatNextColumn1()
'***No Loop, No Variables***
'
'This will format Columns 2,4,6 based on what is in 1,3,5
'
'Look at Column 1, Format Column 2, Row by Row
'
  If Cells(1, 1) > 2 Then _
     Cells(1, 2).NumberFormat = "#,##0.00 "
  If Cells(2, 1) > 2 Then _
     Cells(2, 2).NumberFormat = "#,##0.00 "
  If Cells(3, 1) > 2 Then _
     Cells(3, 2).NumberFormat = "#,##0.00 "
'
'Look at Column 3, Format Column 4, Row by Row
'
  If Cells(1, 3) > 2 Then _
     Cells(1, 4).NumberFormat = "#,##0.00 "
  If Cells(2, 3) > 2 Then _
     Cells(2, 4).NumberFormat = "#,##0.00 "
  If Cells(3, 3) > 2 Then _
     Cells(3, 4).NumberFormat = "#,##0.00 "
'
'Look at Column 5, Format Column 6, Row by Row
'
  If Cells(1, 5) > 2 Then _
     Cells(1, 6).NumberFormat = "#,##0.00 "
  If Cells(2, 5) > 2 Then _
     Cells(2, 6).NumberFormat = "#,##0.00 "
  If Cells(3, 5) > 2 Then _
     Cells(3, 6).NumberFormat = "#,##0.00 "
End Sub

Now we'll loop on the Rows only

Sub FormatNextColumn2()
'***Loop On Rows, But Not Columns***
'
'This will format Columns 2,4,6 based on what is in 1,3,5
'
'Look at Column 1, Format Column 2, Row by Row
  For rw = 1 To 3
   If Cells(rw, 1) > 2 Then _
     Cells(rw, 2).NumberFormat = "#,##0.00 "
  Next
'
'Look at Column 3, Format Column 4, Row by Row
 For rw = 1 To 3
   If Cells(rw, 3) > 2 Then _
     Cells(rw, 4).NumberFormat = "#,##0.00 "
  Next
'
'Look at Column 5, Format Column 6, Row by Row
 For rw = 1 To 3
   If Cells(rw, 5) > 2 Then _
     Cells(rw, 6).NumberFormat = "#,##0.00 "
  Next
End Sub

Now we'll loop on both Rows and Columns. Note that we have to increment the Column variable by 2 because we want to look at every other Column - 1, 3 & 5.

Sub FormatNextColumn3()
'***Loop On Rows and Columns***
'
'This will format Columns 2,4,6 based on what is in 1,3,5
'
'Increment Columns by 2, Rows by 1
'
 For col = 1 To 5 Step 2
  For rw = 1 To 3
   If Cells(rw, col) > 2 Then _
     Cells(rw, col + 1).NumberFormat = "#,##0.00 "
  Next
 Next
End Sub


Report •

#6
April 9, 2009 at 12:24:30
Believe me when I say I wasn't asking you to write the whole code (or maybe I was...somewhat unconsciously, now that I think about it).

I’ll work with this over the weekend and modify as needed to work with my data. Wish me luck and many thanks! Suffice it to say I’ve learned quite a bit at this point, more education to come!

I haven't learned to fish just yet, but at least I have the pole and some bait...



Report •

#7
April 11, 2009 at 10:05:04
That worked beautifully!

I made some modifications to fit my needs and will use what I've learned here to add borders, auto highlight cells based on certain criteria, etc.

Thanks derbydad! The bait is on the hook!

jrussells


Report •


Ask Question