simora
10-06-2014, 06:00 PM
I'm doing a search like this;
Set rngFind = .Find(strValueToPick, LookIn:=xlValues, LookAt:=xlWhole)
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
From this I want to populate the listBox using 3 columns.
I can get the first 2 columns, but how do I get the 3rd column; that on the right to show the month derived from the address.
This is the relavant code with the notes etc..
a = Split(rngFind.Address, "$")
loc = (a(2))
e = Cells(loc, 1) 'Gives the Company Name in Col A
Col = rngFind.Column
' MsgBox Col but as a number
'MsgBox e 'Gives the Company Name in Col A
'MsgBox loc 'Gives the Row Number
How do I get this part to show that 3rd column
With ListBox1
.AddItem rngFind.Address ' Cell Address
.List(.ListCount - 1, 1) = (Cells(loc, 1))
The above shows 2 columns
I tried
.List(.ListCount 0, 1) = (Cells(1, loc))
.List(.ListCount-1, 2) = (Cells(1, loc))
Amoung other things
Set rngFind = .Find(strValueToPick, LookIn:=xlValues, LookAt:=xlWhole)
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
From this I want to populate the listBox using 3 columns.
I can get the first 2 columns, but how do I get the 3rd column; that on the right to show the month derived from the address.
This is the relavant code with the notes etc..
a = Split(rngFind.Address, "$")
loc = (a(2))
e = Cells(loc, 1) 'Gives the Company Name in Col A
Col = rngFind.Column
' MsgBox Col but as a number
'MsgBox e 'Gives the Company Name in Col A
'MsgBox loc 'Gives the Row Number
How do I get this part to show that 3rd column
With ListBox1
.AddItem rngFind.Address ' Cell Address
.List(.ListCount - 1, 1) = (Cells(loc, 1))
The above shows 2 columns
I tried
.List(.ListCount 0, 1) = (Cells(1, loc))
.List(.ListCount-1, 2) = (Cells(1, loc))
Amoung other things