Do/Loop Until using variables declared

How will VBA be able to tell when the data for a state ends and the data for the next state starts?

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

Hi,


I'm not able to upload workbook, but hopefully this may look a little better to show the structure of my code...Sorry for the inconvenience

I have declared all of my variables but I just have an issue of how to use them with a Do Loop Until statement

intState = 14
intLast = 17

Do

For A = intState to intLast
'activate workbook

   For intPlan = 1 To 8
      'variables defined in this manner: Sheets(strSheet).Cells(intState, 3).Value
      'fill in template

      If A = intState And intPlan = 1 Then
        'open and activate template
        'fill in template

      End If

   Next intPlan

Next A

Do Until Sheets(strSheet).Cells(intState, 3).Value <> Sheets(strSheet).Cells(intState + 1, 3).Value
'Save File

Repeat loop for next set of rows

intState to intLast are looking at rows 14 to 17, but is there a way to make it loop through all of my rows using a Do Until Loop with having to define the rows I want it to look at (so essentially getting rid of the variables, intState and intLast)? Or is there a better way for me to look at this? The bold in my code shows what I would like to perform.

Thanks!

You could use a loop to go through all the rows but without seeing how your data is structured it's hard to give specifics.

Even knowing what column the state is in would help.

Sorry, the states are listed in the 3rd column, and the rows are sorted by state. So ideally, I'd like the code to loop until the state changes, save the template, and then go through loop again until state changes, save the template, etc. I hope that helps... Initially I had an input box which was only able to loop one state at a time, but I hit an obstacle when I tried to loop through multiple states because the code couldn't recognize when the state changes.

All you really need is a loop to go through all the rows.

For Each cl In Range("C3", Range("C" & Rows.Count).End(xlUp).Row

    ' code to copy to template

    If cl.Value<>cl.Offset(1).Value Then
        ' new state is encountered, save template
    End If

    ' continue looping

Next cl

Sorry I'm not really sure what you are asking - for example, what's rState?

In the code I posted cl is the cell that has the state, you can get it's row using cl.Row.

That would do it, though you could also use this.

dtEnd = rState.Offset(,6).Value

This tells VBA to put the value that is offset 6 columns to the right of rState into dtEnd.

So if rState was referencing C10, rState.Offset(,6) would reference I10.

By the way, when using Cells you can use either the letter or number to specify the column.

So you could use this.

dtEnd =Sheets(strSheet).Cells(rState.Row, "I").Value

If that gives you the right columns then that should be fine.

The error is my fault, the parentheses aren't closed properly, Row shouldn't be there and no sheet is referenced.

I'm not quite sure which sheet you are working with but if it's Sheets(strSheet) then you could try something like this.

With Sheets(strSheet)
    Set colStates = .Range("C3", .Range("C" & Rows.Count).End(xlUp)) ' set reference to state column
End With

For Each rState In colStates.Cells

The loop works great for the first row, but is there a way to make it not open my template again when its populating for the other rows?
Also, the loop for "intPlan" also keeps recognizing that intRowKnt = 14 when its reading a new row, but it should be continuing where it left off.

            
For Each rState In Range("C3", Range("C" & Rows.Count).End(xlUp))    
   Windows(strIDFile).Activate

           For intPlan = 1 To 8
                 Windows(strIDFile).Activate
                 strPlanID = rState.Offset(, (intPlan * 3) + 9).Value
                 dblAdult = rState.Offset(, (intPlan * 3) + 10).Value
                 dblChild = rState.Offset(, (intPlan * 3) + 11).Value
                 
                     Windows(strTemplate).Activate
                     Sheets("Rate Table").Activate
                         
                     Range("B6").Select
                     Selection.FormulaR1C1 = strH
                     Range("B7").Select
                     Selection.FormulaR1C1 = strT
                     Range("B8").Select
                     Selection.FormulaR1C1 = dtEff
                     Range("B9").Select
                     Selection.FormulaR1C1 = dtEnd
                     
                     intRowKnt = 14
                     
                 Windows(strTemplate).Activate
                 Sheets("Table").Activate
                 
                 strRange = "A" & intRowKnt
                 Range(strRange).Select
                 Selection.FormulaR1C1 = strPlanID
                 strRange = "B" & intRowKnt
                 Range(strRange).Select
                 Selection.FormulaR1C1 = strArea
                 strRange = "C" & intRowKnt
                 Range(strRange).Select
                 Selection.FormulaR1C1 = strTobacco
                 strRange = "D" & intRowKnt
                 Range(strRange).Select
                 Selection.FormulaR1C1 = strAge
                 strRange = "E" & intRowKnt
                 Range(strRange).Select
                 Selection.FormulaR1C1 = dblChildRate
                 strRange = "E" & intRowKnt + 1 & ":E" & intRowKnt + 45
                 Range(strRange).Select
                 Selection.FormulaR1C1 = dblAdultRate
                 
                 intRowKnt = intRowKnt + 46
          
             Next intPlan
Next rState

There's no code that's 'opening' the template, but there is this,

Windows(strTemplate).Activate

which activates the template.

There's no need to activate a workbook/worksheet to copy/paste data.

I would have something like this before the loop, perhaps even right at the top of the code.

Dim wbTemplate As Workbook

    Set wbTemplate = Workbooks(strTemplate)

Then you can use something like this.

  
    For intPlan = 1 To 8

        strPlanID = rState.Offset(, (intPlan * 3) + 9).Value
        dblAdult = rState.Offset(, (intPlan * 3) + 10).Value
        dblChild = rState.Offset(, (intPlan * 3) + 11).Value

        With wbTemplate.Sheets("Rate Table")
            .Range("B6").Value = strH
            .Range("B7").Value = strT
            .Range("B8").Value = dtEff
            .Range("B9").Value = dtEnd
        End With

        intRowKnt = 14

        With wbTemplate.Sheets("Table")
            strRange = "A" & intRowKnt
            .Range(strRange).Value = strPlanID
            strRange = "B" & intRowKnt
            .Range(strRange).Value = strArea
            strRange = "C" & intRowKnt
            .Range(strRange).Value = strTobacco
            strRange = "D" & intRowKnt
            .Range(strRange).Value = strAge
            strRange = "E" & intRowKnt
            .Range(strRange).Value = dblChildRate
            strRange = "E" & intRowKnt + 1 & ":E" & intRowKnt + 45
            .Range(strRange).Value = dblAdultRate
        End With
        
        intRowKnt = intRowKnt + 46

    Next intPlan

By the way, I removed this because, as far as I can see, it wasn't really doing anything.

Windows(strIDFile).Activate

It seems like

Dim wbTemplate As Workbook

    Set wbTemplate = Workbooks(strTemplate)

is giving me a mismatch error for some reason..

strTemplate = "File.xls"

Definitely a type mismatch error and not a subscript out of range?

Yep, definitely Type mismatch.

Sub Testing4()

Application.EnableCancelKey = xlDisabled

Dim strDirectory As String, strTemplate As String, strIDFile As String, strSheet As String
Dim strState As String, strH As String, strT As String
Dim dtEff As Date, dtEnd As Date
Dim strArea As String, strTob As String, strAge As String, strPlanID As String, strRange As String
intPlan As Integer, intRowKnt As Integer
Dim dblAdult As Double, dblChild As Double
Dim strFilename As String
Dim strProd As String

Dim rState As Range

Dim wbTemplate As Workbook
    Set wbTemplate = Workbooks(strTemplate)

strDirectory = "K:\Users\Desktop\"
strTemplate = "Template.xls"
strIDFile = "IDFile.xlsm"
strSheet = "All"


    For Each rState In Range("C3", Range("C" & Rows.Count).End(xlUp))
    
            strState = rState.Offset(, 0).Value
            strH = rState.Offset(, 3).Value
            strT = rState.Offset(, 4).Value
            dtEff = rState.Offset(, 5).Value
            dtEnd = rState.Offset(, 6).Value
            strTob = rState.Offset(, 7).Value
            strAge = rState.Offset(, 8).Value
            strProd = rState.Offset(, 1).Value
            strArea = rState.Offset(, 2).Value
              
             If rState.Value <> rState.Offset(1).Value Then
             
                 With wbTemplate.Sheets("Rate Table")

                 
                 Range("A14:A25000").Select
                 Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp
                 
                 strFilename = "Template_" & strState & "_" & strH & "_" & strProd & "_" & Format(Date, "yyyymmdd") & ".xls"
                 ActiveWorkbook.SaveAs Filename:="K:\Users\Desktop\" & strFilename _
                 , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
                 ReadOnlyRecommended:=False, CreateBackup:=False
                 ActiveWorkbook.Close False
                 Windows(strIDFile).Activate
                 
                 End With        
             End If
         

             
             For intPlan = 1 To 8
                 Windows(strIDFile).Activate
                 strPlanID = rState.Offset(, (intPlan * 3) + 9).Value
                 dblAdultRate = rState.Offset(, (intPlan * 3) + 10).Value
                 dblChildRate = rState.Offset(, (intPlan * 3) + 11).Value
                     
                  With wbTemplate.Sheets("Rate Table")

       
                     Range("B6").Select
                     Selection.FormulaR1C1 = strHIOS
                     Range("B7").Select
                     Selection.FormulaR1C1 = strTIN
                     Range("B8").Select
                     Selection.FormulaR1C1 = dtEff
                     Range("B9").Select
                     Selection.FormulaR1C1 = dtEnd
                  End With
                  
                     intRowKnt = 14
                     
                With wbTemplate.Sheets("Rate Table")
                 
                strRange = "A" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strPlanID
                strRange = "B" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strArea
                strRange = "C" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strTobacco
                strRange = "D" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strAge
                strRange = "E" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = dblChildRate
                strRange = "E" & intRowKnt + 1 & ":E" & intRowKnt + 45
                Range(strRange).Select
                Selection.FormulaR1C1 = dblAdultRate
                
                intRowKnt = intRowKnt + 46
                
                End With
             Next intPlan

    Next rState
    
End Sub

This is the code that I have so far. Hope this helps.

Sorry I can't see why that's happening, it works fine for me.

How have you declared strTemplate?

The problem could be that strTemplate doesn't actually have a value until a little bit further down in the code.

By the way, this won't work - see the code in post #17.

With wbTemplate.Sheets("Rate Table")

       
                     Range("B6").Select
                     Selection.FormulaR1C1 = strHIOS
                     Range("B7").Select
                     Selection.FormulaR1C1 = strTIN
                     Range("B8").Select
                     Selection.FormulaR1C1 = dtEff
                     Range("B9").Select
                     Selection.FormulaR1C1 = dtEnd
                  End With

No, more like this.

Dim wbTemplate As Workbook

    strTemplate = "Template.xls"
    
    Set wbTemplate = Workbooks(strTemplate)

Or even this.

Dim wbTemplate As Workbook

     Set wbTemplate = Workbooks("Template.xls")

By the way, which worksheet/workbook is column C in here?

For Each rState In Range("C3", Range("C" & Rows.Count).End(xlUp))

Is the template workbook open?

This code will refer to column C in the active sheet, whatever that is.

For Each rState In Range("C3", Range("C" & Rows.Count).End(xlUp))

As will all the ranges in this section of code.

 Range("B6").Select
                     Selection.FormulaR1C1 = strHIOS
                     Range("B7").Select
                     Selection.FormulaR1C1 = strTIN
                     Range("B8").Select
                     Selection.FormulaR1C1 = dtEff
                     Range("B9").Select
                     Selection.FormulaR1C1 = dtEnd

The code should open the template workbook.

My issue was that when it loops the plans, it would keep trying to reopen the template.
How do I condition the code so that it only opens once per state?

The code used to be:

For intPlan = 1 To 8
             
                 Windows(strIDFile).Activate
                 strPlanID = rState.Offset(, (intPlan * 3) + 9).Value
                 dblAdultRate = rState.Offset(, (intPlan * 3) + 10).Value
                 dblChildRate = rState.Offset(, (intPlan * 3) + 11).Value
                     
                    Workbooks.Open Filename:=strDirectory & strTemplate
       
                    Range("B6").Select
                    Selection.FormulaR1C1 = strHIOS
                    Range("B7").Select
                    Selection.FormulaR1C1 = strTIN
                    Range("B8").Select
                    Selection.FormulaR1C1 = dtEff
                    Range("B9").Select
                    Selection.FormulaR1C1 = dtEnd
                  
                  
                     intRowKnt = 14
                     
                 
                strRange = "A" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strPlanID
                strRange = "B" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strArea
                strRange = "C" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strTobacco
                strRange = "D" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = strAge
                strRange = "E" & intRowKnt
                Range(strRange).Select
                Selection.FormulaR1C1 = dblChildRate
                strRange = "E" & intRowKnt + 1 & ":E" & intRowKnt + 45
                Range(strRange).Select
                Selection.FormulaR1C1 = dblAdultRate
                
                intRowKnt = intRowKnt + 46
                
             Next intPlan

That wasn't in the earlier code where you mentioned the template being opened again.

Try putting this at the top of the code.

strDirectory = "K:\Users\Desktop\"
strTemplate = "Template.xls"
strIDFile = "IDFile.xlsm"
strSheet = "All"

Set wbTemplate = Workbooks.Open(Filename:=strDirectory & strTemplate)

It might also be an idea to create a reference to the IDFile workbook

For example, if the IDFile is open.

Set wbIDFile = Workbooks(strIDFile)

Then you can make sure you are referring to the correct workbook and worksheet here.

For Each rState In wbIDFile.Sheets(strSheet).Range("C3", wbIDFile.Sheets(strSheet).Range("C" & Rows.Count).End(xlUp))

Is there no way you could mock up an example data workbook and template and attach them here?

That would make it a lot easier to give help with the code.

As it is I'm going on the code you are posting.

PS If the whole point of the code is to split up the data by state then there could be other(better?) methods.:l

This should actually be a very straightforward task and you'll find plenty of examples if you search the forum.