Consulting

Results 1 to 5 of 5

Thread: Assign Dynamic Colmumn Reference

  1. #1
    VBAX Newbie
    Joined
    Nov 2008
    Posts
    3
    Location

    Assign Dynamic Colmumn Reference

    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

    [VBA]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[/VBA]

    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

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,777
    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?

  3. #3
    VBAX Newbie
    Joined
    Nov 2008
    Posts
    3
    Location
    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

  4. #4
    VBAX Newbie
    Joined
    Nov 2008
    Posts
    3
    Location
    Apparently ListCount will do that for me.

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

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,777
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •