Sleeper: Tricky For Loop Code

[steveburton1]

Hello World!

Been working on this one a while a can't quite seem to crack it- close though! I need some VBA code that produces the data below- which is just a sample. So the LS and PB data inputs on the right are the parameters (6 inputs in total.) And the results are basically all combinations of the different parameters. Note that LS2 must always be greater than LS1, and so on. I've written some nested For Loops that are getting me 80% there but I'm still off... Thanks for any help I can get. Good luck!

LS1	PB1	LS2	PB2	LS3	PB3
10	5					LS	INPUTS
10	10					Start #	10
10	15					Range	30
10	20					Interval	10
20	5
20	10					PB
20	15					Start	5
20	20					Range	20
30	5					Interval	5
30	10
30	15
30	20
10	5	20	5
10	5	20	10
10	5	20	15
10	5	20	20
10	10	20	5
10	10	20	10
10	10	20	15
10	10	20	20
10	15	20	5
10	15	20	10
10	15	20	15
10	15	20	20
10	20	20	5
10	20	20	10
10	20	20	15
10	20	20	20
10	5	30	5
10	5	30	10
10	5	30	15
10	5	30	20
10	10	30	5
10	10	30	10
10	10	30	15
10	10	30	20
10	15	30	5
10	15	30	10
10	15	30	15
10	15	30	20
10	20	30	5
10	20	30	10
10	20	30	15
10	20	30	20
20	5	30	5
20	5	30	10
20	5	30	15
20	5	30	20
20	10	30	5
20	10	30	10
20	10	30	15
20	10	30	20
20	15	30	5
20	15	30	10
20	15	30	15
20	15	30	20
20	20	30	5
20	20	30	10
20	20	30	15
20	20	30	20
10	5	20	5	30	5
10	5	20	5	30	10
10	5	20	5	30	15
10	5	20	5	30	20
10	5	20	10	30	5
10	5	20	10	30	10
10	5	20	10	30	15
10	5	20	10	30	20
10	5	20	15	30	5
10	5	20	15	30	10
10	5	20	15	30	15
10	5	20	15	30	20
10	5	20	20	30	5
10	5	20	20	30	10
10	5	20	20	30	15
10	5	20	20	30	20
10	10	20	5	30	5
10	10	20	5	30	10
10	10	20	5	30	15
10	10	20	5	30	20
10	10	20	10	30	5
10	10	20	10	30	10
10	10	20	10	30	15
10	10	20	10	30	20
10	15	20	15	30	5
10	15	20	15	30	10
10	15	20	15	30	15
10	15	20	15	30	20
10	20	20	20	30	5
10	20	20	20	30	10
10	20	20	20	30	15
10	20	20	20	30	20

[GTO]

Greetings Steve and welcome to VBAX :-)

Say now... this wouldn't be homework would it? As such, we really cannot give you working code, or you are simply cheated out of learning. That said, if you show your efforts thus far (your current code), you might receive feedback on what parts to look at, and/or questions on your current logic that can help break your current "block".

Mark

[steveburton1]

Hi Mark,

No, definitely not homework. I'm a full time employee and this is for work. Here is what I've started with:

1. LSStart = 10
2. LSRange = 30
3. LSi = 10
4. PBStart = 5
5. PBRange = 20
6. PBi = 5
7. UnitCount = ((LSRange - LSStart) / LSi) + 1
8. L = 1
9. S = 2
10. PB = 3
11. i = 1 'To print results on new row
12. CurrUnit = 1

Sub Unit1()
    Unit = 1
    For LSStart = LSStart To LSRange Step LSi
        Cells(1, L + ((3 * (Unit - 1)))).Value = LSStart
        Cells(1, S + ((3 * (Unit - 1)))).Value = -LSStart
            For PBStart = PBStart To PBRange Step PBi
                Cells(1, PB + ((3 * (Unit - 1)))).Value = PBStart
                    If CurrUnit > 1 Then Call Unit2
                    Worksheets("Test").Range("A" & i & ":AD" & i).Value = Range("P1:AS1").Value
                    i = i + 1
            Next PBStart
            PBStart = 5
    Next LSStart
    LSStart = 10
    'Cells(1, "16:18").Clear
    CurrUnit = 2
    Call Unit1

Using Option Explicit. As such, it's a little difficult to see where I'm going without having the sheet open but I was thinking maybe I should split the LS1, PB1 columns into units and somehow loop that way. Maybe using three nested loops OR having a sub routine for each unit. Thoughts?

Thanks,

Steve

[steveburton1]

And you can disregard

Cells(1, S + ((3 * (Unit - 1)))).Value = -LSStart

as that's not in the original data set I posted. It's the inverse values of the LS numbers.

[SamT]

Steve, I formatted your post for better readability. You can use the # icon on editor to enclose your code in Code tags, which will emulate the VBA editor in style. The number List icon is only available if you use the Go Advanced editor.

One thing about posting here, we will tell you if your code is easily understandable. You variable names are all made of acronyms which only make sense to a coder who is familiar with the entire project, which is not use, and in 6 months won't be you, and in a year, won't be your replacement.

The numbered list of Names appears to be a list of Constants, but isn't. LSRange is not a Range, (LSMax?) Some of those variables are used like they were Constants, but have to be reset to their "constant" value after being used as changeable variables. The character string "unit" is used in the names of Variables, Constants, and Subs. If you decide to follow these guidelines, you can carefully use Ctrl+H with "Find Whole Word Only," to edit all name instances at once.

The value of (3 * (Unit - 1)) is always = 0. Why use it?

"i" should be named NewRowNum, simply because i, j, and k are standard loop counters.
The following also applies to PBStart(j). You might want to rename PBi to PBj or even rename both to **step

    For LSStart = LSStart To LSRange Step LSi 
'
'
'
    Next LSStart 
    LSStart = 10

Should read

    For i = LSStart To LSMax Step LSstep 
'
'
'
    Next i

There is no clue or hint about Sub Unit2.

Sub Unit1 calls itself, but I don't see any exit point?!?!?

IOW, and in ending, I don't have a clue what your code snippet is doing.

I'm sorry if most of this is already known to you. We have to respond below the highest level of knowledge discernible from the post itself.