At A Loss: Numbers Out-of-order

PBMonkey

New Member
Joined
Feb 19, 2005
Messages
5
I'm at a loss as to why my text/numbers are coming out out-of-order. I'm sure it's something simple that will seem obvious once pointed out. I've included the whole Sub for review of the error, since I'm fairly new to macro programming and it could be just about anything.

Basically, on my UserForm I need things to look like this:

Code:
Text1...........Text2
Text3...........Text4
....Text5.......Text6
Text7...........Text8
....Text9......Text10
Text11.........Text12
....Text13.....Text14

And so on. I've already made sure my various Labels are in the proper order and positioned correctly, so I know it's not that. However, when run the UserForm looks like this:

Code:
Text1...........Text2
Text3...........Text4
....Text5.......Text6
Text7...........Text8
....Text9.......
Text11.........Text10
....Text13.....Text12

It shifts the captions for those labels one row down on the right side.

And, yes, I know my code is probably messy or overly complicated. I can only say I'm sorry.

Code:
Private Sub SkillLabels()
    Dim Srow01 As String
    Dim Srow02 As String
    Dim Srow03 As String
    Dim Srow04 As String
    Dim Srow05 As String
    Dim Srow06 As String
    Dim Srow07 As String
    Dim Srow08 As String
    Dim Srow09 As String
    Dim Srow10 As String
    Dim Srow11 As String
    Dim Srow12 As String
    Dim Srow13 As String
    Dim Srow14 As String
    Dim Srow15 As String
    Dim Srow16 As String
    Dim Srow17 As String
    Dim Srow18 As String
    Dim Srow19 As String
    Dim Srow20 As String
    Dim Srow21 As String
    Dim Srow22 As String
    Dim Srow23 As String
    Dim Srow24 As String
    Dim Srow25 As String
    Dim Rv01 As String
    Dim Rv02 As String
    Dim Rv03 As String
    Dim Rv04 As String
    Dim Rv05 As String
    Dim Rv06 As String
    Dim Rv07 As String
    Dim Rv08 As String
    Dim Rv09 As String
    Dim Rv10 As String
    Dim Rv11 As String
    Dim Rv12 As String
    Dim Rv13 As String
    Dim Rv14 As String
    Dim Rv15 As String
    Dim Rv16 As String
    Dim Rv17 As String
    Dim Rv18 As String
    Dim Rv19 As String
    Dim Rv20 As String
    Dim Rv21 As String
    Dim Rv22 As String
    Dim Rv23 As String
    Dim Rv24 As String
    Dim Rv25 As String
    Dim Row As Integer
    Row = TPListBox.ListIndex + 4
    Rv01 = Sheets("TPs").Cells(Row, 45)
    Rv02 = Sheets("TPs").Cells(Row, 47)
    Rv03 = Sheets("TPs").Cells(Row, 49)
    Rv05 = Sheets("TPs").Cells(Row, 51)
    Rv06 = Sheets("TPs").Cells(Row, 53)
    Rv07 = Sheets("TPs").Cells(Row, 55)
    Rv08 = Sheets("TPs").Cells(Row, 57)
    Rv09 = Sheets("TPs").Cells(Row, 59)
    Rv10 = Sheets("TPs").Cells(Row, 61)
    Rv11 = Sheets("TPs").Cells(Row, 63)
    Rv12 = Sheets("TPs").Cells(Row, 65)
    Rv13 = Sheets("TPs").Cells(Row, 67)
    Rv14 = Sheets("TPs").Cells(Row, 69)
    Rv15 = Sheets("TPs").Cells(Row, 71)
    Rv16 = Sheets("TPs").Cells(Row, 73)
    Rv17 = Sheets("TPs").Cells(Row, 75)
    Rv18 = Sheets("TPs").Cells(Row, 77)
    Rv19 = Sheets("TPs").Cells(Row, 79)
    Rv20 = Sheets("TPs").Cells(Row, 81)
    Rv21 = Sheets("TPs").Cells(Row, 83)
    Rv22 = Sheets("TPs").Cells(Row, 85)
    Rv23 = Sheets("TPs").Cells(Row, 87)
    Rv24 = Sheets("TPs").Cells(Row, 89)
    Rv25 = Sheets("TPs").Cells(Row, 91)
    If Sheets("TPs").Cells(2, 5) = " " Then
        Srow01 = Sheets("TPs").Range("AJ23").Value
        Srow02 = Sheets("TPs").Range("AJ24").Value
        Srow03 = Sheets("TPs").Range("AJ25").Value
        Srow04 = Sheets("TPs").Range("AJ26").Value
        Srow05 = Sheets("TPs").Range("AJ27").Value
        Srow06 = Sheets("TPs").Range("AJ28").Value
        Srow07 = Sheets("TPs").Range("AJ29").Value
        Srow08 = Sheets("TPs").Range("AJ30").Value
        Srow09 = Sheets("TPs").Range("AJ31").Value
        Srow10 = Sheets("TPs").Range("AJ32").Value
        Srow11 = Sheets("TPs").Range("AJ33").Value
        Srow12 = Sheets("TPs").Range("AJ34").Value
        Srow13 = Sheets("TPs").Range("AJ35").Value
        Srow14 = Sheets("TPs").Range("AJ36").Value
        Srow15 = Sheets("TPs").Range("AJ37").Value
        Srow16 = Sheets("TPs").Range("AJ38").Value
        Srow17 = Sheets("TPs").Range("AJ39").Value
        Srow18 = Sheets("TPs").Range("AJ40").Value
        Srow19 = Sheets("TPs").Range("AJ41").Value
        Srow20 = Sheets("TPs").Range("AJ42").Value
        Srow21 = Sheets("TPs").Range("AJ43").Value
        Srow22 = Sheets("TPs").Range("AJ44").Value
        Srow23 = Sheets("TPs").Range("AJ45").Value
        Srow24 = Sheets("TPs").Range("AJ46").Value
        Srow25 = Sheets("TPs").Range("AJ47").Value
    ElseIf Sheets("TPs").Cells(2, 17) = " " Then
        Srow01 = Sheets("TPs").Range("AJ49").Value
        Srow02 = Sheets("TPs").Range("AJ50").Value
        Srow03 = Sheets("TPs").Range("AJ51").Value
        Srow04 = Sheets("TPs").Range("AJ52").Value
        Srow05 = Sheets("TPs").Range("AJ53").Value
        Srow06 = Sheets("TPs").Range("AJ54").Value
        Srow07 = Sheets("TPs").Range("AJ55").Value
        Srow08 = Sheets("TPs").Range("AJ56").Value
        Srow09 = Sheets("TPs").Range("AJ57").Value
        Srow10 = Sheets("TPs").Range("AJ58").Value
        Srow11 = Sheets("TPs").Range("AJ59").Value
        Srow12 = Sheets("TPs").Range("AJ60").Value
        Srow13 = Sheets("TPs").Range("AJ61").Value
        Srow14 = Sheets("TPs").Range("AJ62").Value
        Srow15 = Sheets("TPs").Range("AJ63").Value
        Srow16 = Sheets("TPs").Range("AJ64").Value
        Srow17 = Sheets("TPs").Range("AJ65").Value
        Srow18 = Sheets("TPs").Range("AJ66").Value
        Srow19 = Sheets("TPs").Range("AJ67").Value
        Srow20 = Sheets("TPs").Range("AJ68").Value
        Srow21 = Sheets("TPs").Range("AJ69").Value
        Srow22 = Sheets("TPs").Range("AJ70").Value
        Srow23 = Sheets("TPs").Range("AJ71").Value
        Srow24 = Sheets("TPs").Range("AJ72").Value
        Srow25 = Sheets("TPs").Range("AJ73").Value
    ElseIf Sheets("TPs").Cells(37, 5) = " " Then
        Srow01 = Sheets("TPs").Range("AJ75").Value
        Srow02 = Sheets("TPs").Range("AJ76").Value
        Srow03 = Sheets("TPs").Range("AJ77").Value
        Srow04 = Sheets("TPs").Range("AJ78").Value
        Srow05 = Sheets("TPs").Range("AJ79").Value
        Srow06 = Sheets("TPs").Range("AJ80").Value
        Srow07 = Sheets("TPs").Range("AJ81").Value
        Srow08 = Sheets("TPs").Range("AJ82").Value
        Srow09 = Sheets("TPs").Range("AJ83").Value
        Srow10 = Sheets("TPs").Range("AJ84").Value
        Srow11 = Sheets("TPs").Range("AJ85").Value
        Srow12 = Sheets("TPs").Range("AJ86").Value
        Srow13 = Sheets("TPs").Range("AJ87").Value
        Srow14 = Sheets("TPs").Range("AJ88").Value
        Srow15 = Sheets("TPs").Range("AJ89").Value
        Srow16 = Sheets("TPs").Range("AJ90").Value
        Srow17 = Sheets("TPs").Range("AJ91").Value
        Srow18 = Sheets("TPs").Range("AJ92").Value
        Srow19 = Sheets("TPs").Range("AJ93").Value
        Srow20 = Sheets("TPs").Range("AJ94").Value
        Srow21 = Sheets("TPs").Range("AJ95").Value
        Srow22 = Sheets("TPs").Range("AJ96").Value
        Srow23 = Sheets("TPs").Range("AJ97").Value
        Srow24 = Sheets("TPs").Range("AJ98").Value
        Srow25 = Sheets("TPs").Range("AJ99").Value
    Else: MsgBox "You can not gain more than three Training Packages at once."
    End If
    SkillLabel1.Caption = Srow01
        RankLabel1.Caption = Rv01
    SkillLabel2.Caption = Srow02
        RankLabel2.Caption = Rv02
    SkillLabel3.Caption = Srow03
        RankLabel3.Caption = Rv03
    SkillLabel4.Caption = Srow04
        RankLabel4.Caption = Rv04
    SkillLabel5.Caption = Srow05
        RankLabel5.Caption = Rv05
    SkillLabel6.Caption = Srow06
        RankLabel6.Caption = Rv06
    SkillLabel7.Caption = Srow07
        RankLabel7.Caption = Rv07
    SkillLabel8.Caption = Srow08
        RankLabel8.Caption = Rv08
    SkillLabel9.Caption = Srow09
        RankLabel9.Caption = Rv09
    SkillLabel10.Caption = Srow10
        RankLabel10.Caption = Rv10
    SkillLabel11.Caption = Srow11
        RankLabel11.Caption = Rv11
    SkillLabel12.Caption = Srow12
        RankLabel12.Caption = Rv12
    SkillLabel13.Caption = Srow13
        RankLabel13.Caption = Rv13
    SkillLabel14.Caption = Srow14
        RankLabel14.Caption = Rv14
    SkillLabel15.Caption = Srow15
        RankLabel15.Caption = Rv15
    SkillLabel16.Caption = Srow16
        RankLabel16.Caption = Rv16
    SkillLabel17.Caption = Srow17
        RankLabel17.Caption = Rv17
    SkillLabel18.Caption = Srow18
        RankLabel18.Caption = Rv18
    SkillLabel19.Caption = Srow19
        RankLabel19.Caption = Rv19
    SkillLabel20.Caption = Srow20
        RankLabel20.Caption = Rv20
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,
I'm not sure about the movement of the labels.
Perhaps, the length of the text in the cell may affect it, but not really sure.

by the way, your code may rewritten like:
Code:
Private Sub SkillLabels()
    Dim Srow(1 To 25) As String, rv(1 To 25) As String
    Dim Row As Integer, i As Integer, ii As Integer
    Row = YourFormName.TPListBox.ListIndex + 4
With Sheets("TPs")
            ii = 43
            For i = 1 To 25: ii = ii + 2: rv(i) = .Cells(Row, ii).Text: Next
        If .Cells(2, 5) = " " Then
            ii = 22
               For i = 1 To 25: ii = ii + 1: Srow(i) = .Range(ii, "a").Text: Next
        ElseIf .Cells(2, 17) = " " Then
            ii = 48
                For i = 1 To 25: ii = ii + 1: Srow(i) = .Cells(ii, "aj").Text: Next
        ElseIf .Cells(37, 5) = " " Then
            ii = 74
                For i = 1 To 25: ii = ii + 1: Srow(i) = .Cells(ii, "aj").Text: Next
        Else: MsgBox "You can not gain more than three Training Packages at once."
        End If
            For i = 1 To 20
                YourFormName.Controls("SkillLabel" & i).Caption = Srow(i)
                YourFormName.Controls("RankLabel" & i).Caption = rv(i)
            Next
End With
Erase Srow, rv
End Sub
where YourFormName must be altered

hope this helps
jindon
 
Upvote 0
Thanks very much for the help.

When I use your code, I get an "Application-defined or object-defined error." pointing at the second ": Next". If I get rid fo the Next's, I get a "ElseIf without If" error; I've gotten that error before and managed to fix it with the patented "Flailing Around Until it Works" method that uses no ": Next". The way If...ElseIf...Else...EndIf works seems very obtuse and counterintuitive.

It can't be the length of the text, because the longest strings would be "3 (total)", and the vast majority are single digit numbers.
 
Upvote 0
Hi,

Is the condition, if clause, clear enough?
If 2 condition were met, it will happen.
because array is defined 1 to 25.

else, what module are you writing this code?

I think it is better to write in the form module like form Initialize, then
you can replace your form name with Me.

rgds,
jindon
 
Upvote 0
I understand almost nothing of what you just said.

I am picking up VBA from scratch, basically. Be prepared for dumb questions, and bonehead coding.

I created SkillLabels to be called on ListBoxChange, so that all the labels update appropriately when you select something from the ListBox.

Here's all my code.

You select something from the listbox, and it updates all the labels to match the information associated with your choice. All the information is in a table in the Excel sheet ("TPs").
 
Upvote 0
Hi,

Looked at your code.

Do you know how to use Local Window in VB editor?
You can check every single variables at real time.

In VB editor
go to View -> Local window

then click somewhere in Private Sub SkillLabels() and press F8.
as you press F8, code excute line by line and you can see the progress of each variables. then you will find what is causeing error.

Would like to try it? or send your filwe to me.

rgds,
jindon
 
Upvote 0
Ah, just figured out what was causing the latest error; fixed it and now everything's running smoothly. It was a simple mistype on your part, I think.

From the code of yours I copied, it was:

Code:
With Sheets("TPs")
            ii = 43
            For i = 1 To 25: ii = ii + 2: Rv(i) = .Cells(Row, ii).Text: Next
        If .Cells(2, 5) = " " Then
            ii = 22
               For i = 1 To 25: ii = ii + 1: Srow(i) = .RANGE(ii, "aj").Text: Next

When it should be:

Code:
With Sheets("TPs")
            ii = 43
            For i = 1 To 25: ii = ii + 2: Rv(i) = .Cells(Row, ii).Text: Next
        If .Cells(2, 5) = " " Then
            ii = 22
               For i = 1 To 25: ii = ii + 1: Srow(i) = .CELLS(ii, "aj").Text: Next

You have easily doubled my meager knowledge of VBA; thank you very much![/b]
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,121
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top