PDA

View Full Version : Sleeper: Tricky For Loop Code



steveburton1
12-22-2013, 08:44 PM
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
12-22-2013, 09:10 PM
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
12-22-2013, 10:13 PM
Hi Mark,

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



LSStart = 10
LSRange = 30
LSi = 10
PBStart = 5
PBRange = 20
PBi = 5
UnitCount = ((LSRange - LSStart) / LSi) + 1
L = 1
S = 2
PB = 3
i = 1 'To print results on new row
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
12-22-2013, 10:15 PM
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
12-24-2013, 02:02 PM
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.