The reel address from a combobox ( bob phillips)

[alvin Kuiper]

Hi

I use this to get some value in 2 comboboxe:
FIRST:
Private Sub combo_sedatoer_DropButtonClick()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
Dim dtePrev As Date
Dim iArray As Long
Dim ary
dtePrev = 0: iArray = 1
ReDim ary(1 To 2, 1 To 1)
With Worksheets("Ark1")
Me.combo_sedatoer.Clear
For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If .Cells(i, "A").Value <> dtePrev Then
If i <> 1 Then
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
iArray = iArray + 1
End If
iStart = i
iEnd = i
dtePrev = .Cells(i, "A").Value
Else
iEnd = i
End If
Next i
ReDim Preserve ary(1 To 2, 1 To iArray)
ary(1, iArray) = dtePrev
ary(2, iArray) = Range("A" & iStart & ":A" & iEnd).Address
Me.combo_sedatoer.List = Application.Transpose(ary)
End With
End Sub
Private Sub combo_sedatoer_Click()
Dim hvorb As String
Dim s1 As String

hvorb = Me.combo_sedatoer.List(Me.combo_sedatoer.ListIndex, 1)
s1 = Replace(hvorb, "A", "B")

Dim rng3 As Range
Sheets("Ark1").Select
Set rng3 = Range(Range("Ark1!" & s1), Range("ark1!" & s1))
Me.combo_timer.List = rng3.Value
End Sub

What i need i to see the cell value there are in a cell right to the address
from
value i choose in Me.combo_timer.

Like all value in Me.combo_timer.list comes from collumn B and when i selct
a calue in Me.combo_timer then i want to get the value from collumn C from
the same row as selcted in Me.combo_timer
I have try with lisindex but dosn't work

regards
alvin

 

[Toppers]

Hi
Somthing like this should work ....

Private Sub combo_Timer_Change()
' Assume first item in this combobox(.list) is from row 2 in column B
'the ....
row=combo_timer.listindex+2
set ws1.worksheets("Sheet1") ' <== change as required
myValue=ws1.cells(row,"c") ' Value from column C
End Sub


HTH

 

[eseitomer]

Try adding 1 to Me.combo_sedatoer.ListIndex and use that value as your
row.

CurrentRow = Me.combo_sedatoer.ListIndex + 1

Remember ListIndex only specifies where in the list it is, not the row
number. Depending on how many rows you have before where you start
counting, you might have to add more than one.

-E

 

[alvin Kuiper]

Hi toppers
Thanks
But i get a eroor in
set ws1.worksheets("Sheet1")
it say i shall have = after set ws1.worksheets("Sheet1")
regards
alvin

 

[alvin Kuiper]

Hi
I have try
this
Row = combo_timer.ListIndex + 2
Sheets("Ark1").Select
myValue = Cells(Row, "c") ' Value from column C
txtvaerdi.Value = myValue

But again it is not the right value
but the value from the index

regards
alvin

 

[alvin Kuiper]

No i dosn't work
because the listindex is not the real Address
but the Adress from the index

I have i Collumn A the same date many times
Maybe from A1 to A21 I have the same Date
and again From
A22 to A30 i have the same date
But in my combebox i only see to dates
Then i get the range where the dates are and use this
whit replace A to B in my second combobox
combo_timer

Here i get all the value from B But it can bee from B22 to B31
or B1 to B21 and so on

So listindex give me maybe $B$1
But teh real address can bee $Bb$22
Because it the first value in combo_timer
If i have choose the second calue of dates in my firstcombobox

Regards

Alvin

 

[eseitomer]

Hmm.. Have you tried storing the real address in the array?

 

[alvin Kuiper]

Now i use this in my first combobex
Dim rng3 As Range
Sheets("Ark1").Select
Set rng3 = Range(Range("Ark1!" & s1), Range("ark1!" & s1))
For Each c In rng3
combo_timer.AddItem c.Value
its working

s1 = $b$1:$b$22 or $b1:$b21 -- can bee many value just you can see

so the vlaue in combo_timer are from the range in S1

Now i just have to know where the real address are when i choose
a value.

regards
alvin