R1C1 seems to effect listbox fill in VBA

[Steve]

Hi Folks

I have a spreadsheet application that uses a few advanced filters and
populates some worksheet listboxes and Dropdown boxes with the
results.
Works fine when the sheet is NOT in R1C1 mode, but when the sheet was
changed to R1C1 reference the listboxes no longer populated!
Has anyone else ever had this issue?
I know its avoidable by setting the R1C1 reference first but I have
not come across this porblem before.
Here is an "example" of the code (yes i know it can be written more
efficiently).


Sub lists_populate()
check1 = Worksheets("bench").Range("b65000").End(xlUp).Row + 1
check2 = Worksheets("bench").Range("e65000").End(xlUp).Row + 1
check3 = Worksheets("bench").Range("g65000").End(xlUp).Row + 1

set1 = "bench!a2:b" & check1
set2 = "bench!d2:e" & check2
set3 = "bench!g2:n" & check3

ListBox1.ListFillRange = set1
ListBox2.ListFillRange = set2
ListBox3.ListFillRange = set3

End Sub

TIA
Steve Picton

 

[Tim Zych]

set1 = Application.ConvertFormula( _
"bench!a2:b" & check1, xlA1, xlR1C1, True)