Simple VBA looping question

[Dr_Ransom]

I am in the begining stages of learning vba coding for excel. I am trying to automate the creation of ~30 quarterly reports I have to generate each month. I have my basic code down that will generate a single report. What I cannot figure out is how to loop through this code, with each loop using a new value for 2 variables that will be pulled from a range on a spreadsheet.


I think the best way to ask this would be to work through a simple example using a message box populated by two variables. Lets say I have two columns of data in an excel worksheet (Column "A" = Divisions and Column "B" = CostCenters). There are 5 rows of data: division1, division2, ect.

I am looking for a VBA formula that will show a message box that says Division & "-" & CostCenter (assuming Division and CostCenter are my variables) for each of the five lines of data. So five message boxes would appear when the procedure is executed.

I think if I can get this simplified VBA procedure to work, I will be able to apply the concept to my more complex procedure that I already have written.

Thanks in advance. I have already learned tons from lurking on this board for awhile.

 

[Dr_Ransom]

I have figured out part of my code. This assumes that my data is in column A & B and starts on row 2.

Sub SimpleLooping()

    For i = 2 To 6
        Division = Cells(i, "A")
        CostCenter = Cells(i, "B")
        MsgBox Division & " - " & CostCenter
    Next i

End Sub

Is there a way to have a a variable in place of the 6. This variable would equal the number of rows of data less 1 (to account for the headers in row 1)?

 

[Marcelo Branco]

Hi and welcome to Mr Excel

See if this simple example can help you

Your data in Sheet1

A B (headers in row 1)
<TABLE style="WIDTH: 105pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=140><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Division</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=76>CostCenter</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Division1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>CC1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Division2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>CC2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Division3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>CC3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Division4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>CC4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Division5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>CC5</TD></TR></TBODY></TABLE>

Sub SimpleLoop()
    Dim firstRow As Long, lastRow As Long, i As Long
    Dim division As String, costCenter As String
    Dim wk As Worksheet
    
    Set wk = Sheets("Sheet1")
    
    With wk
        'Set the first row with data
        firstRow = 2
        
        'Get the last row with data
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        'Loop through data range
        For i = firstRow To lastRow
            division = .Range("A" & i)
            costCenter = .Range("A" & i).Offset(0, 1)
            MsgBox division & " - " & costCenter
        Next i
    End With
End Sub

M.

 

[Dr_Ransom]

I was able to use the LastRow variable defined as " Cells(.Rows.Count, "A").End(xlUp).Row" to make my number of message boxes dynamic based on the number of rows filled out with data.

On my list to learn soon is the benefits and reasons for defining the variables and their types (Long, String, ect)

Thanks for your help!

 

[Marcelo Branco]

You are welcome!

M.