index match or something like that

[quintin]

I hope somebody is able to help me on this one.
I have a data base in a worksheet which has town names (a2:a22) and the same town names across (b1:u1) Then the range b2:u22 is the km's.
I have manage to put this together in the normal sheet index match, however what I'm now trying to do is put it into a userform.
So in my userform I have 4 comboboxes - cb1 = from cb2 and 3 are via and cb4 is to - So you'll enter townX in cb1 and in the other two via (those two are normally the same) town Y then in cb4 would be town X again to give you a round trip.
What I am wanting it to do is to put the total number of KM's in tb1.
This is some of the code that I have done...also not sure if the code should be on start up or in tb1.
Any help would be great, thanks

<code>Dim towndown As String
Dim townacc As String
Dim km As Integer
Dim finalrow As Integer
Dim finalcol As Integer


towndown = Worksheets("Data").Range("a2:a22").Value
townacc = Worksheets("Data").Range("b1:u1").Value
km = Worksheets("Data").Range("b2:u22").Value
finalrow = Worksheets("Data").Range("a30").End(x1up).Row
finalcol = Worksheets("Data").Range("x1").End(x1left).col

ComboBox1 = towndown
ComboBox2 = townacc
ComboBox3 = townacc
ComboBox4 = towndown</code>

 

[Andrew Poulsom]

Not an answer to your question yet, but shouldn't it be B1:V1 rather than B1:U1?

 

[Andrew Poulsom]

Pending an answer to my question does this work for you?

Dim AllData As Variant
Dim Towns As Range

Private Sub UserForm_Initialize()
    With ActiveSheet
        Set AllData = .Range("A1:V22")
        Set Towns = .Range("A1:A22")
    End With
    ComboBox1.List = Towns.Value
    ComboBox2.List = Towns.Value
    ComboBox3.List = Towns.Value
    ComboBox4.List = Towns.Value
End Sub

Private Sub CommandButton1_Click()
    Dim km As Long
    km = AllData.Cells(ComboBox1.ListIndex + 1, ComboBox2.ListIndex + 1)
    km = km + AllData.Cells(ComboBox2.ListIndex + 1, ComboBox3.ListIndex + 1)
    km = km + AllData.Cells(ComboBox3.ListIndex + 1, ComboBox4.ListIndex + 1)
    TextBox1.Value = km
End Sub

 

[quintin]

Yeah, it should be B1:V1, I'm going to give the code a try....just got home
Will let you know...many thanks

 

[quintin]

I placed the code into the userform and got an error code - object required

km = AllData.Cells(ComboBox1.ListIndex + 1, ComboBox2.ListIndex + 1)

 

[Andrew Poulsom]

I'm not sure how you would get that error with the code I posted. I tested it before posting.

Did you select something from all the ComboBoxes before clicking the CommandButton? There is no error checking in the code.

 

[quintin]

Got it, Thank you so very much