Assign Dynamic Colmumn Reference

[NDayave]

How Do,

I have a rather simple user form that I want to save data on the worksheet. I want the column in a list to be compared to a list on the worksheet and if it matches add a "1" in the appropriate box.

So far I have

For x = 1 To 20
If lstCurrentOps.Column(0, x) = (Range("OptionsCost").Cells(x, 1)) Then
Range("OptionsCar" & lblFactory.Caption & "1").Cells(x) = "1"
Else
For y = 1 To 20
If lstCurrentOps.Column(0, x) = (Range("OptionsCost").Cells(y, 1)) Then
Range("OptionsCar" & lblFactory.Caption & "1").Cells(y) = "1"
End If
Next y
End If
Next x

Where lstCurrentOps has one column holding the Options chosen, OptionsCost being a named table with the Otions available and their respective cost and lblFactory a label holding which Factory the car is produced in, allowing the form to know what car to assign the options to (eg: OptionsCar23 for car 3 in factory 2).

I hit an error with the column property though, saying that it "could not get the column property. Invalid Property array index". It works fine with numbers (eg .column(0,1)) but not the variable reference (0,x).

Any help is much appreciated.


Cheers,

NDayave

[mikerickson]

How many rows are in the listbox lstCurrentOps?
Listbox lists are 0 based, yet x is going from 1 to 20, Would 0 to 19 work?

Also, the arguments for Column are (colIndex, rowIndex), not the usual (row,column).
Might that be a part of the problem?

[NDayave]

As it would turn out, it was because there were less rows than 20, and the whole x starting at 1 doesn't matter.

How do you get the number of rows in a list box? There is a columncount but no rowcount, and the number of rows changes depending on the Options selected.

And since when was (Row,Column) the usual? In every application I have ever used it is always (Column, Row).

Cheers,


NDayave

[NDayave]

Apparently ListCount will do that for me.

Looks to be sorted, cheers on the Number of Rows shout

[mikerickson]

Glad it helped.

In many of Excel properties. (eg. .Cells, .List, the structure of R1C1 formulae) row numbers come before column numbers.
Although when one adds a shape, the .Left argument precedes the .Top argument.

The .Column property of ListBoxes and ComboBoxes seems to me to be an odd ball in that respect.