Perform Action on 2nd instance count of For Statement

Chrome46 · Mar 29th 2011

Below is my code. I am trying to generate labels. The length of the object dictates how many objects need to be labeled, and depending on the object position, it could be labeled differently. My code works except for when I try to label the second last object. The second, and second last object need an "S" appended to the end of the label.

The code is part of a user form and I think I altered it so that it should work in a normal worksheet.

The second last member is labeled as V7, but it should be V7S. (It should generate labels W2 - W15, and V1S, V2 - V6, V7S)

Any help is appreciated.

Code

sub trial()
Depth = 28 / 12
span = 30.75
deadload = 106
liveload = 261


totalload = deadload + liveload
tcp = 2
bcp = 4


BCFP = 1.5 * Depth
n = Abs(((span - (2 * BCFP)) / tcp) - 2)
WorksheetFunction.Even (n)
k = (n + 4) * 3 / 2 + 1
g = k - 3
taken = n * tcp
remaining = (span - taken) / 2
xtc1 = remaining / 2
xtc2 = xtc1
xct3 = tcp
xbc1 = BCFP
xbc2 = xbc1 - xtc1
xbc3 = (remaining / 2) - xbc1
xbc4 = tcp


Range("A1") = "Member"
v = 2
w = 2
For x = 1 To k
    Select Case True
        Case x = 1
            member = "W" & w
            w = w + 1
        Case x = 2
            member = "V1S"
        Case x = k - 1                        ' <----- This is not happening
            member = "V" & v & "S"      
        Case (x - 5) Mod 3 = 0
            member = "V" & v
            v = v + 1
        Case Else
            member = "W" & w
            w = w + 1
    End Select
    Sheets("Sheet1").Select
    Cells(x + 1, 1) = member
Next
end sub

Display More

Chrome46 · Mar 29th 2011

Re: Perform Action on 2nd instance count of For Statement

I was able to write a work around (for those interested). I reduced the For count by 3 and wrote in a second For with a count of 3 for the three remaining labels.

I'd still be interested to know why it wouldn't work the way I originally had it.

Thanks for looking.

added immediately below "Next"

Code

For y = 1 To 3
    Select Case True
        Case y = 1
            member = "W" & w
            w = w + 1
            horz = xbc3
        Case y = 2
            member = "V" & v & "S"
            horz = xbc2
            v = v + 1
        Case y = 3
            member = "W" & w
            w = w + 1
            horz = xbc1
    End Select
    Sheets("JCS").Select
    Cells(y + x, 1) = member
    Cells(y + x, 2) = horz
    Cells(y + x, 3) = Depth
    Cells(y + x, 4) = Sqr(horz ^ 2 + Depth ^ 2)

Display More

Herbds7 · Mar 29th 2011

Re: Perform Action on 2nd instance count of For Statement

N = WorksheetFunction.Even(N)

Perform Action on 2nd instance count of For Statement

Participate now!

Share