Range.End(xlToLeft)/(xlToRight) not working as .RowSource

0
votes
Private Sub ComboBox8_Change()
Dim vRow As Double
Dim rPICRange As Range
Dim rComRange As Range

Set rComRange = dbComWB.Worksheets("CustomerList").Range("B2")
Set rComRange = Range(rComRange, rComRange.End(xlDown))
vRow = Application.WorksheetFunction.Match(Me.ComboBox8.value, rComRange, 0)
Set rPICRange = dbComWB.Worksheets("CustomerList").Range(Cells(vRow + 1, 14).Address)
Set rPICRange = Range(rPICRange, rPICRange.End(xlToRIght))
Me.ComboBox9.RowSource = rPICRange.Address(external:=True)
End Sub

Above are my code that want to fill a combobox but the "rPICRange" set to Rowsource as a single range instead of a list.

I do tried printout individual value of "rPICRange" & "rPICRange.end(xlToRight)" before assign to RowSource, it is correct value i want.

I also debug by changing .End(xlToRight) to other direction. Seen to me .End(xlUp) & .End(xlDown) work fine but Right & left is mess up.

Edit:

Is that because of ComboBox.RowSource only accept range in row (xlIp/xlDown), but not range in column (xlToRight/xlToLeft). If yes, how can i "Transpose" the range?

Set rPICRange = Application.WorksheetFunction.Transpose(Range(Cells(vRow + 1, 14).Address, rPICRange.End(xlToRight)))

Code above not working for me.

2
excelvba
As far as I can see rPICRange should be being calculated as everything from column N to the last non-blank cell on the row. (Or, if column N itself is the last non-blank cell on the row, everything from column N to the right-hand side of Excel.) How many columns do you have in your data? – YowE3K
"everything from column N to the last non-blank cell on the row." is exactly what i need. Sorry for my poor english. I get around 9 column. – KokJoo89
So, just to confirm, you have data in columns N to V? – YowE3K
What is the value of rPICRange.Address(external:=True) that your code is calculating? – YowE3K
Yes, $N to $V. those cells contain string. – KokJoo89

2 Answers

0
votes

You cannot use Range without a parent worksheet reference even if you are defining it with range objects that have parent worksheet objects in a private sub or any sub in a worksheet code page. See Is the . in .Range necessary when defined by .Cells? for an extended discussion on this.

Option Explicit

Private Sub ComboBox8_Change()
    Dim vRow As Double
    Dim rPICRange As Range
    Dim rComRange As Range

    With dbComWB.Worksheets("CustomerList")
        Set rComRange = .Range("B2")
        Set rComRange = .Range(rComRange, rComRange.End(xlDown))
    End With

    vRow = Application.WorksheetFunction.Match(Me.ComboBox8.Value, rComRange, 0)

    With dbComWB.Worksheets("CustomerList")
        Set rPICRange = .Cells(vRow + 1, 14)
        Set rPICRange = .Range(rPICRange, rPICRange.End(xlToRight))
    End With

    Me.ComboBox9.RowSource = rPICRange.Address(external:=True)
End Sub

I'm not entirely sure what you were trying to accomplish with the Range.Address property but I believe I've rectified it.

0
votes
Private Sub ComboBox8_Change()
Dim vRow As Double
Dim Rng As Range
Dim rPICRange As Range
Dim rComRange As Range

Set rComRange = dbComWB.Worksheets("CustomerList").Range("B2")
Set rComRange = Range(rComRange, rComRange.End(xlDown))
Me.ComboBox9.Clear
vRow = Application.WorksheetFunction.Match(Me.ComboBox8.value, rComRange, 0)
Set rPICRange = dbComWB.Worksheets("CustomerList").Range(Cells(vRow + 1, 14).Address)
Set rPICRange = Range(rPICRange, rPICRange.End(xlToRight))

'code below add each range value into the list
For Each Rng In rPICRange
Me.ComboBox9.AddItem Rng.value
Next Rng

End Sub

Thank to YowE3K. I finally manage to get it working.

Lesson Learned:

RowSource indeed for Row range only, when input Column range will only get the first data.