Nested? For...Next statement in a loop???

[elcid113002]

Private Sub FixData2_Click()

Dim KNum
Dim LNum
Dim MNum
Dim PNum
Dim RNum
Dim TNum
Dim Counter
Dim Check

Check = True: Counter = 0
KNum = Range("N2").Value ' Should be equal to "2".
LNum = Range("N4").Value
RNum = Range("N12").Value

If KNum = 2 Then
Range("K" & RNum).Select
ActiveCell.Activate
ActiveCell.Value = Worksheets("Data").Range("J2").Value
End If
Do 'Outer Loop
Do While Counter < LNum ' Inner loop - runs until the number reaches the
' number equivalent to total number personnel on det.
Counter = Counter + 1 ' Increment Counter.
If Counter = LNum Then ' If condition is True.
For PNum = RNum To RNum + (LNum - 1)
If KNum = 2 Then
For MNum = 2 To LNum
If Sheets("Data").Range("J2") <> "" Then
Range("K" & PNum).Value = Sheets("Data").Range("J" & MNum)
End If
Next
End If
Next
Check = False
Exit Do ' Exit inner loop.
End If
Loop
Loop Until Check = False
Range("A1").Select
End Sub

I am no programmer, but I am the closest thing to it in my office.
My problem is: I need K58 = J2, K59 = J3, K60 = J4, etc... instead when I run the code I get K58 through K69 all equal "J13".
Can anyone help?

 

[SkipVought]

Hi,

Maybe instead, explain what you are attempting to accomplish, forgetting the code for now. What's the purpose for all of this?

There may be more than one good way to get from point A to point B.

Skip,

_[red][/red]

 

[SkipVought]

Just scanning your code, for each PNum, MNum loops to the value of LNum. LNum is the Ending ROW in EACH case.

LNum never changes. It's what is in N4.

Skip,

_[red][/red]

 

[elcid113002]

The groups of names will be paid overtime on the last day of a detachment

I have copied a group of names from "Data" worksheet (J2 - J13) to "Attach" worksheet for each day they will be on Detachment (ie, May 13, 14, 15, 16 etc...)
May 13 Name1 (base $$$)K54
May 13 Name2 (base $$$)K55
May 13 Name3 (base $$$)K56
K57
May 14 Name1 (base $$$)K58 (Last day of det)
May 14 Name2 (base $$$)K59
May 14 Name3 (base $$$)K60
Then to pay them overtime, I am trying to bring the base from (J2) starting at the last day of the det and copy it.

I hope this helps,

Elcid113002

 

[elcid113002]

Since (For MNum = 2 To LNum) why wouldn't LNum start at 2 then loop to 3, 4, 5, all the way to 12(N4 value)?

Thanks,

Elcid113002

 

[SkipVought]

LNum NEVER changes.

Put a break in your code and examine the VALUE of LNum.

For MNum = 2 To LNum

MNum is the ONLY value that changes.

I am still looking at your example.

Skip,

_[red][/red]

 

[SkipVought]

That REALLY is not much information.

Where do the dates come from?

Where are the hours?

What is the OT rate for each name?


Skip,

_[red][/red]

 

[elcid113002]

Sorry, I was trying to be as brief, but detailed as possible...To answer your questions, The dates are the dates of the detachment. Each day they rate OT.

The hours are as follows:
Total Pay
OT Hrs w/o OT Base Pay
H J K
58 1 $93.94 $46.97
59 1 $93.94 $46.97
60 1 $99.64 $46.97

KNum = Range("N2").Value ' Should be equal to "2".
LNum = Range("N4").Value ' Total number of pers on Det(12)
RNum = Range("N12").Value ' Start of "group" (Row 58)
Group = 12 personnel on det for 5 days. For each day the pers are on det they are placed on the spreadsheet. Row 58 (The last day) is where the last group starts. There is a break between days...refer back to the earlier thread.

(N4 is 12 in this case)(The number of detachment days)What I meant was how come MNum doesn't change? Shouldn't it do the following... For MNum = 2 To LNum
If Sheets("Data").Range("J2") <> "" Then
Range("K" & PNum).Value = Sheets("Data").Range("J" & MNum)

K58 = J2, K59 = J3, K60 = J4, etc...(Loop/Next 12 times)
MNum should change each time, Right???

Hope this helps.
Elcid113002

 

[SkipVought]

        For PNum = RNum To RNum + (LNum - 1)
           If KNum = 2 Then
                For MNum = 2 To LNum
                    If Sheets("Data").Range("J2") <> "" Then
                        Range("K" & PNum).Value = Sheets("Data").Range("J" & MNum)
                    End If
                Next
            End If
        Next

lets say that PNUM goes from 1 to 2
and MNum goes from 2 to 3

here's what happens to Range("K" & PNum).Value
[tt]
k1 = j2
k1 = j3
k2 = j2
k2 = j3
[/tt]
j3 is ALWAYS the value that ends up in the K column.

Skip,

_[red][/red]

 

[TaylorDavidI]

Why not do it all in one loop instead of a nested one? something to the effect of

Counter = 2
Do While Counter < LNum
    Sheets("Data").Cells(56 + Counter, 11).Value = _
        Sheets("Data").Cells(Counter, 10).Value
    Counter = Counter + 1
Loop

This assumes that you want to start the counter at 2 every time. That doesn't quite work if the gap between the two ranges is dynamic.

David I. Taylor
A+, Network+, MCP Windows XP

 

[TaylorDavidI]

I just realized you can do it for a dynamic gap if you use offset and find the starting cells for each range earlier in the code.

Dim startJ as Range, startK as Range

'some set of code to find the appropriate cells.

Counter = 0
Do While Counter < LNum
    startK.Offset(Counter, 0).Value = _
        startJ.Offset(Counter, 0).Value
    Counter = Counter + 1
Loop

I don't know how you determine what the first cell is but if it's something you can look for with code this might help.

David I. Taylor
A+, Network+, MCP Windows XP

 

[PHV]

Why not simply copy/paste J2:J13 to K58:K69 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[elcid113002]

I appreciate everyone's replies. Skip, Your replies have generated more questions. Is nesting of For..Next even possible?? If so, How do i do it without getting the results I am getting?

Another question about the copy/paste idea...If Range("J" & Variable) and Range("K" & Variable), How would I use the Copy/Paste function. It would really be nice to talk to someone about this, It would probably be easier to explain. Also, I have never had formal schooling in programming, so I'm not sure I'm even using the correct terminology. And I don't understand some of the terminology everyone is using. I have been doing this for several years, but again no formal training.

Elcid113002

 

[PHV]

How would I use the Copy/Paste function
Simply like this;
Sheets("Data").Range("J2:J" & someVar).Copy Range("K58:K" & anotherVar)


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886