Consolidate Multiple Loop Statements

[spartanjim]

I'm working in XL07 on Win7.

The workbook has two worksheets "Data" & "Crunch". "Data" contains the raw data-range B1:U50. "Crunch" is the processing worksheet. Row 1 is a header row. Columns A-D are empty. Column E (rows 2-81) contain the numbers 1-80.

The macro is intended to run in "Crunch". I need to add the CountIf function in every cell/row filling the range F2:BC81. A loop does the trick for me to fill the rows in each column but I don't know how to move the loop down each column without creating a new loop.

A section of the code is below. Notice a majority of the code is the same, the copy in red is variable from loop to loop. I need to repeat this loop through column "BC" (50 times in total). My guess is there is a way to write this code one time instead of 50 times adjusting the variable components.

'Find total # of records and then store in variable
    totalrecords = ActiveSheet.UsedRange.Rows.Count
'CountIf Statement
    For Row = totalrecords To 2 Step -1
        If Cells(Row, 5).Value <> "0" Then
            Cells(Row, [COLOR="Red"]6[/COLOR]).Value = "=COUNTIF(Data!$B$1:$U$[COLOR="red"]1[/COLOR], E" & Row & ")"
        End If
    Next Row
    
'Find total # of records and then store in variable
    totalrecords = ActiveSheet.UsedRange.Rows.Count
'CountIf Statement
    For Row = totalrecords To 2 Step -1
        If Cells(Row, 5).Value <> "0" Then
            Cells(Row, [COLOR="red"]7[/COLOR]).Value = "=COUNTIF(Data!$B$1:$U$[COLOR="red"]2[/COLOR], E" & Row & ")"
        End If
    Next Row

'Find total # of records and then store in variable
    totalrecords = ActiveSheet.UsedRange.Rows.Count
'CountIf Statement
    For Row = totalrecords To 2 Step -1
        If Cells(Row, 5).Value <> "0" Then
            Cells(Row, [COLOR="red"]8[/COLOR]).Value = "=COUNTIF(Data!$B$1:$U$[COLOR="red"]3[/COLOR], E" & Row & ")"
        End If
    Next Row

'Find total # of records and then store in variable
    totalrecords = ActiveSheet.UsedRange.Rows.Count
'CountIf Statement
    For Row = totalrecords To 2 Step -1
        If Cells(Row, 5).Value <> "0" Then
            Cells(Row, [COLOR="red"]9[/COLOR]).Value = "=COUNTIF(Data!$B$1:$U$[COLOR="red"]4[/COLOR], E" & Row & ")"
        End If
    Next Row

Thank you
Jim

 

[Smitty]

Welcome to the Board!

You can count the number of columns just like you do rows.

Dim LC as Long
LC = Cells(1,Columns.Count).End(xltoLeft).Column

Then you can add another loop inside the row loop:

For x = 1 to LC
Cells(Row, x)...

HTH,

 

[adam087]

Deleted - didn't read the question properly. Sorry.

Adam

 

[spartanjim]

Smitty, thank you for the quick response!

I'm not very good with excel and have no programming background but your lead was enough to clear my confusion.

Your lead, followed by finding this thread got me over the hump.

Here's the code I ended up using:

'Find the last filled row in column E
    LR = Cells(Rows.Count, 5).End(xlUp).Row
'Find the last filled column in row 1
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
'CountIf Statement
    For Row = LR To 2 Step -1
    For Col = LC To 6 Step -1
        Cells(Row, Col).Value = "=COUNTIF(Data!$B$1:$U" & Col - 5 & ", E" & Row & ")"
    Next Col
    Next Row

Again, thank you!
Jim