VBA loop / do until

Question

I'm trying to configure data in a very specific format in order for it to be uploaded to a software.

I have a count sheet that has the amount of lines needed to be entered on my upload template sheet. The values on the count sheet range between 3, 5, 7, and 9.

I want my VBA program to be able to enter values 1, 2, 3, (on 3 separate rows) on the upload template sheet if the value is "3" on the count sheet.

If the next value is "5" on the count sheet I want my program to be able to enter values 1, 2, 3, 2, 3 (on 5 separate rows on the upload sheet).

If a value is "9" on the count sheet, I want my program to enter values 1, 2, 3, 2, 3, 2, 3, 2, 3 (on 9 separate rows) and so on.

The first three values will always be 1, 2, 3, consecutively and then alternating between 2 and 3 based on the value on the count sheet.

Can anyone help me out here?

What *specific* problem are you facing with this? You should at least be able to make a start and post the code you're having a problem with. — Tim Williams, Jun 10 '21 at 16:29

score 0 · Accepted Answer · answered Jun 10 '21 at 16:52

There aren't enough details to really write anything. But here's a tutorial/example of how to access data from seperate workbooks and how to write values into cells inside a loop.

The equation you were looking for where the output row # follows the pattern 1,2,3,2,3,2,3... is a little complicated with normal math, but it can be accomplished in programming math by using a Boolean expression as part of the equation.

Sub RowNumberer()
    Dim CountWB As Workbook
    Set CountWB = Workbooks("Your Count Sheet Workbook's Name (plus file extension)")
    
    Dim CountSheet As Worksheet
    Set CountSheet = CountWB.Worksheets("Your Count Sheet's Name")
    
    Dim CountSheetValue As Long
    CountSheetValue = CountSheet.Range("Cell Address of your Count result (3, 5, 7, 9)")
    
    Dim UploadWB As Workbook
    Set UploadWB = Workbooks("Your Upload Template Workbook's Name (plus file extension)")
    
    Dim OutputSheet As Worksheet
    Set OutputSheet = UploadWB.Sheets("The Name of the Sheet you want to number the rows on")
    
    Dim i As Long
    For i = 1 To CountSheetValue
        OutputSheet.Cells(i, 1) = i Mod 2 - 2 * (i > 1)
    Next i
End Sub

To explain the formula: i Mod 2 - 2 * (i > 1)

i Mod 2 is taking the iterating value i and retrieving the remainder after dividing by 2. This alternates between 1 and 0.

We would just need +2 in order to move that from 1 and 0 to 3 and 2. But we only want +2 after the first number since we want the first number to be 1.

So I added the boolean expression (i > 1) which is 0 when False and -1 when True. I don't know why True = -1 in VBA. So the term - 2 * (i > 1) is 0 when i = 1 and +2 when i > 1.

As a thought experiment, the regular mathematical equation to get this pattern would be `2.5 - 0.5 * (-1) ^ x - 2 * 0 ^ (x - 1)` As { x ∈ ℤ ∣ x > 0 }. This equation makes use of exponents to alternate between 2 and 3 by subtracting or adding 0.5 and another term to subtract 2 when `x=1` — Toddleson, Jun 10 '21 at 17:08
Thank you SO much! This does exactly what I want it to do, now I just need to loop it down the count sheet. — Adam Dyer, Jun 11 '21 at 15:59

VBA loop / do until

1 Answers1