Strange results finding last filled cell in a row

[anubis]

Hi all,

First, a little background info for everyone...

1. I am trying to teach myself VBA (via this board and a J. Walkenbach book). So the code may be messy... :wink:

2. Cells A6:D6 are merged and hardcoded with text.

3. The following cells are merged and filled with text via a userform...
a. E6:K6
b. N6:T6
c. W6:AC
d. AF6:AL6
e. AO6:AU6

4. The following cells are filled with numbers via the above mentioned userform...
a. L6
b. U6
c. AD6
d. AM6
e. AV6

I am trying to determine the last cell that has a number in it in the range of A6:AV6. There will never be an instance where the last cell is filled with text. I am using the following code to find the last filled cell (always a number) and depending on the result, call a module (routine? I don't know the correct term).

Private Sub CommandButton1_Click()

Dim Mk1 As String, Mk2 As String, MyColumn As Long

Mk1 = MkBox1.Text

If MkBox1.Text = "" Then
    MsgBox "You Must Select One From Each Level."
Exit Sub
End If
Range("P126") = Mk1

Mk2 = MkBox2.Text
If MkBox2.Text = "" Then
    MsgBox "You Must Select One From Each Level."
Exit Sub
End If
Range("P128") = Mk2

Unload MkAbilities1and2

MyColumn = Range("AW6").End(xlToLeft)

If Cells(6, MyColumn) = Range("A6") Then
    Call More_Classes
Else

If Cells(6, MyColumn) = Range("L6") Then
    Call Third_Classes
Else

If Cells(6, MyColumn) = Range("U6") Then
    Call Fourth_Classes
Else

If Cells(6, MyColumn) = Range("AD6") Then
    Call Fifth_Classes
Else

If Cells(6, MyColumn) = Range("AM6") Then
Demographics.Show

End If
End If
End If
End If
End If

End Sub

I am using this code in another userform, and it is working fine. However, in this userform it keeps returning U6 as the last cell with a number when the last cell is L6.

Any suggestions, as always, would be greatly appreciated.

EDIT: I forgot... I used =CELL("address",INDEX(E6:AV6,MATCH(BigNum,E6:AV6,1))) on the sheet and it returned the correct cell...L6 Just one more reason I'm going nuts with this.

 

[JimB]

MyColumn needs to hold a column index number, so let's add .Column to the end of .End(xlToLeft). We should also look all the way to the last column (IV) before "looking left", so we don't miss any data.

Using Select Case here will eliminate the multiple IF statements.

MyColumn = Range("IV6").End(xlToLeft).Column

Select Case Cells(6, MyColumn)
    Case Range("A6"): Call More_Classes
    Case Range("L6"): Call Third_Classes
    Case Range("U6"): Call Fourth_Classes
    Case Range("AD6"): Call Fifth_Classes
    Case Range("AM6"): Demographics.Show
    Case Else: MsgBox "Error" '<-- add your own message or command here
End Select

Hope this helps

 

[anubis]

Hey JimB,

Thanks for the reply. Your code looks much neater then mine :wink: . I will have to take a look at Select Case.

I'm at work and can't try it now, but I will try as soon as I get back home (hotel). Also, I only need to check the range E6:AV6. Should I still keep the MyColumn range at IV?

I will post back after trying your code. Again, thank you .

 

[Joe4]

Dave,

One piece of advice that might help also.

Avoid using Merged Cells at all costs! They wreak havoc with VBA code. Very few advanced programs will use them for that reason.

A better option which mimics merged cells is the Center Across Selection option (Format | Cells | Alignment | Text Alignment: Horizontal | Center Across Selection)

 

[JimB]

If you are planning on having other data in row 6 beyond column AV that you do not want your macro to look at, then change the IV to AV or AW or whatever suits your situation.

 

[anubis]

Hi JimB and jmiskey,

Thank you both for your information and help. The Select Case Code is cool, and I totally forgot about the Center Across Selection option.

I have changed:

MyColumn = Range("AW6").End(xlToLeft) to
MyColumn = Range("IV6").End(xlToLeft).Column

All of my IF's to the Select Case Code.

The merged cells to the Center Across Selection option.

Unfortunately, no dice.

To further explain...
I have a UserForm (Class) that has a ListBox and a TextBox. The ListBox fills E6 with text. The Textbox fills L6 with a number. If the text = "Monk" and the number = 1 then another UserForm is shown (MonkAbilitieslvl1). This form has a ListBox that fills another part of the sheet.

When MonkAbilitieslvl1 is unloaded, a macro is called asking the user if they want to continue filling in row 6. If yes, then SecondClass is shown. SecondClass is identical to Class, except that it fills the ranges N6 and U6 (with text and a number respectfully). The process repeats until the user does not want to fill any more of Row 6, or when 5 choices have been made.

If "Monk" is the first choice of the user, then there are no problems. However, when "Monk" is the second, third, fourth, or fifth choice MyColumn always determines the last filled cell as L6.

I know this is going to take up a lot of space, but here is the relevant code I am using...

Class UserForm:

Start = ClassBox.Text
If ClassBox.Text = "" Then
MsgBox "You Must Select A Class"
    Exit Sub
End If

Range("E6") = Start

lvl = Val(Level)
If Level = "" Then
MsgBox "You Must Enter A Starting Level"
    Exit Sub
End If
Range("L6") = lvl

If Start = "MONK" And lvl = 1 Then
    Unload Class
    MonkAbilitiesLvl1.Show
Else

If Start = "MONK" And lvl < 6 And lvl > 1 Then
    Unload Class
    MonkAbilities1and2.Show
    
Else

If Start = "MONK" And lvl > 5 Then
    Unload Class
    MonkAbilitiesLvl6.Show
    
Else

Unload Class
Call More_Classes
End If
End If
End If

End Sub

MonkAbilitieslvl1 UserForm:

Private Sub CommandButton1_Click()

Dim Monk As String, MyColumn As Long

Monk = MonkBox.Text
If MonkBox.Text = "" Then
    MsgBox "You Must Select One of the Two Bonus Feats"
Exit Sub
End If

Range("P126") = Monk

Unload MonkAbilitiesLvl1

MyColumn = Range("IV6").End(xlToLeft).Column

Select Case Cells(6, MyColumn)
    Case Range("L6"): Call More_Classes
    Case Range("U6"): Call Third_Classes
    Case Range("AD6"): Call Fourth_Classes
    Case Range("AM6"): Call Fifth_Classes
    Case Range("AV6"): Demographics.Show
    Case Else: MsgBox "Error"
End Select

End Sub

Macro:

Sub More_Classes()
    msg = "Do You Have More Starting Classes??"
    ans = MsgBox(msg, vbYesNo)
    If ans = vbYes Then
        SecondClass.Show
    Else
        Range("N6:AV6").Select
            Selection.ClearContents
        Demographics.Show
    End If
End Sub

SecondClass UserForm:

Start = ClassBox.Text
If Start = Range("E6") Then
    MsgBox "You Already Have Levels In " & Start & ".  Please Choose Another Class."
Exit Sub
End If

Range("n6") = Start
lvl = Val(Level)
If Level = "" Then
MsgBox "You Must Enter A Starting Level"
    Exit Sub
End If
Range("u6") = lvl

If Start = "MONK" And lvl = 1 Then
    Unload SecondClass
    MonkAbilitiesLvl1.Show
Else

If Start = "MONK" And lvl < 6 And lvl > 1 Then
    Unload SecondClass
    MonkAbilities1and2.Show
Else
If Start = "MONK" And lvl > 5 Then
    Unload SecondClass
    MonkAbilitiesLvl6.Show
Else
    Unload SecondClass
    Call Third_Classes
End If
End If
End If


End Sub

Again, I thank you for your help, but I have spent hours looking over, changing, and re-changing the code and I still can't "Git R Done". Any more suggestions are greatly appreciated.

 

[JimB]

I'm looking at the More_Classes subroutine... Is it possible this line is causing your trouble?

Range("N6:AV6").Select 
            Selection.ClearContents

 

[anubis]

Thanks Jim,

I'll look at it when I get back home (hotel) and post back tonight. That may be causing a problem.

 

[anubis]

Hi all again,

Sorry for the delay, but I haven't had much time this week. I am going to look at this area of the code now. I haven't determined if this is the problem yet. Again, thanks for the help.