CODEWITHSUNDEEP
excelvba
KokJoo89
KokJoo89

Reputation: 15

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

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.

Upvotes: 0

Views: 1877

Answers (2)

KokJoo89
KokJoo89

Reputation: 15

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.

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Related Questions