PDA

View Full Version : Sleeper: return the source of the input of a combobox



Regouin
03-02-2005, 03:33 AM
I am trying to get the cell number or actually only the row number but once I get it to show the source I can get it to obtain the row number of a certain input in a combo box. Here's what I have in VBA



Private Sub UserForm_activate()
Dim prob(14, 1)
With ComboBox1
.ColumnCount = 1
.ColumnWidths = 75
.Width = 100
.Height = 15
.ListRows = 15
End With
With Worksheets("blad1")
prob(0, 0) = .Range("A1")
prob(1, 0) = .Range("A2")
prob(2, 0) = .Range("A3")
prob(3, 0) = .Range("A4")
prob(4, 0) = .Range("A5")
prob(5, 0) = .Range("b1")
prob(6, 0) = .Range("b2")
prob(7, 0) = .Range("b3")
prob(8, 0) = .Range("b4")
prob(9, 0) = .Range("b5")
prob(10, 0) = .Range("c1")
prob(11, 0) = .Range("c2")
prob(12, 0) = .Range("c3")
prob(13, 0) = .Range("c4")
prob(14, 0) = .Range("c5")
ComboBox1.List() = prob
End With
End Sub

Private Sub CommandButton1_Click()
MsgBox (ComboBox1.Text)
End Sub


now I want the msgbox to return the rownumber of the source in the combobox. What it does now is return the value of the given cell. for practicing purposes i put numbers in the cells of the combobox.

tia
frank

Jacob Hilderbrand
03-02-2005, 03:39 AM
Try the ListIndex property.


ComboBox1.ListIndex

Note that the first item has a ListIndex of 0, second is 1 etc. No selection is -1.

Regouin
03-02-2005, 03:44 AM
ok that returns the number of the position in the list, but I want it to return the source cell so it should return a2 or r1k2 i need the row number in the excel sheet.

Jacob Hilderbrand
03-02-2005, 03:46 AM
You just need to add to the number to get them to match up. What is the row for the first entry? Add that number to the ListIndex.

Regouin
03-02-2005, 03:50 AM
Yes, I thought of that but the problem is going to be a little bit more complex, I am going to implement this in a sheet that doesnt need each row, and that would have a list index that looks like this:



rvwas2(0, 0) = .Range("a870")
rvwas2(1, 0) = .Range("a871")
rvwas2(2, 0) = .Range("a872")
rvwas2(3, 0) = .Range("a873")
rvwas2(4, 0) = .Range("a876")
rvwas2(5, 0) = .Range("a879")
rvwas2(6, 0) = .Range("a880")
rvwas2(7, 0) = .Range("a881")
rvwas2(8, 0) = .Range("a882")
rvwas2(9, 0) = .Range("a885")
rvwas2(10, 0) = .Range("a886")
rvwas2(11, 0) = .Range("a887")
rvwas2(12, 0) = .Range("a888")
rvwas2(13, 0) = .Range("a891")
rvwas2(14, 0) = .Range("a892")
rvwas2(15, 0) = .Range("a895")
rvwas2(16, 0) = .Range("a896")


notice that the cell number jumps every now and then, cos otherwise i would just have vba to add a certain number to the list index.

tia
frank


Or should I make a seperate index with the whole column A in the index, and let it look up from there, then i have to work around duplicate names but that is a different problem.

Jacob Hilderbrand
03-02-2005, 03:53 AM
In that case I would just use .Find to find where the text is.


With Sheets("Sheet1").Range("A:A")
Row = .Find(What:=ComboBox1.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With

Regouin
03-02-2005, 04:08 AM
Ok now I have the following setup in excel, started think about it and tell me if I am on the right track

1
1.1
1.1.1
1.1.2
1.1.3
1.1.4
1.2
1.2.1
1.2.2
1.2.3
1.2.4
1.3
1.3.1
1.3.2
1.3.3
1.3.4
1.4
1.4.1
1.4.2
1.4.3
1.4.4
2
2.1
2.1.1
2.1.2
2.1.3
etc.

Now the problem is that the numbers are text but since the text is in dutch i wouldnt bore you with that. The text strings are maintenance jobs (i.e. grease ball bearings) now with certain components the same maintenance jobs return. So in column A I would have 5 or 6 times the exact quote, so with a match it would always return the first value found.
now a setup would be to first check if it is 1,2,3 or 4, then check in a different range to see if it is 1,2,3,4,5 etc. and then finally check in the last range to find the text string. It would then return the right position, if I am correct.
I hope you can follow my train of thought here, if there is anything not clear, feel free to ask.

Jacob Hilderbrand
03-02-2005, 04:12 AM
Ok since you have diplicate matches try this instead. Make a second ComboBox. When you add text to the first ComboBox, add the Row number to the second. Make the second one Visible = False. Then get the row like this.


Row = ComboBox2.List(ComboBox1.ListIndex)

Regouin
03-02-2005, 04:22 AM
I think i got it worked out.

i have 3 comboboxes in which the values can be chosen, so first box gives choices 1,2,3,4 second 10,20,30,40 and third 100,200,300,400 (to better illustrate the duplicates) now i do a find on the first combobox, if the row number corresponds with 1 i goto a find on the second combobox which only has the range for 1, when the row number corresponds with 2 i go to a find on the second combobox which only has the range for 2 etc. etc. I am typing it out now, so i will let you know if it worked out allright.

thanx

Jacob Hilderbrand
03-02-2005, 04:27 AM
If it doesn't work, can you post an attachment of your workbook so I can get a better idea of what you are doing?

Regouin
03-02-2005, 06:23 AM
ok it works like it should, only problem is that it generates an awful lot of code, here is the code for determing the row of the last input box.



rijnummer:
With Sheets("onderhoud").Range("A:A")
Row = .Find(What:=cboxwasstraat.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
If Row = "1" Then GoTo linksv
If Row = "810" Then GoTo rechtsv
If Row = "1574" Then GoTo hoofdw1
If Row = "2205" Then GoTo hoofdw2
linksv:
With Sheets("onderhoud").Range("A1:A809")
Row = .Find(What:=cboxonderdeel.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
GoTo rijonderhoud
rechtsv:
With Sheets("onderhoud").Range("A810:A1573")
Row = .Find(What:=cboxonderdeel.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
GoTo rijonderhoud
hoofdw1:
With Sheets("onderhoud").Range("A1574:A2204")
Row = .Find(What:=cboxonderdeel.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
GoTo rijonderhoud
hoofdw2:
With Sheets("onderhoud").Range("A2205:A2840")
Row = .Find(What:=cboxonderdeel.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
End With
GoTo rijonderhoud
rijonderhoud:
If Row = "2" Then GoTo linksvcorr
If Row = "45" Then GoTo linksvkbaan
If Row = "90" Then GoTo linksvinweek
If Row = "135" Then GoTo linksvvborstel
If Row = "180" Then GoTo linksvpjet
If Row = "225" Then GoTo linksvrowi
If Row = "270" Then GoTo linksvfnswas
If Row = "315" Then GoTo linksvdakrol
If Row = "360" Then GoTo linksvhdmpower
If Row = "405" Then GoTo linksvrwas
If Row = "450" Then GoTo linksvrswas
If Row = "495" Then GoTo linksvdogen
If Row = "540" Then GoTo linksvwanner
If Row = "585" Then GoTo linksvhydrauholz
If Row = "630" Then GoTo linksvhydrauduijv
If Row = "675" Then GoTo linksviwaki10
If Row = "720" Then GoTo linksviwaki30
If Row = "765" Then GoTo linksviwaki35
If Row = "811" Then GoTo rechtsvcorr
If Row = "854" Then GoTo rechtsvkbaan
If Row = "899" Then GoTo rechtsvinweek
If Row = "944" Then GoTo rechtsvvborstel
If Row = "989" Then GoTo rechtsvpjet
If Row = "1034" Then GoTo rechtsvhwas
If Row = "1079" Then GoTo rechtsvfnswas
If Row = "1124" Then GoTo rechtsvdakrol
If Row = "1169" Then GoTo rechtsvhdmpower
If Row = "1214" Then GoTo rechtsvrwas
If Row = "1259" Then GoTo rechtsvrswas
If Row = "1304" Then GoTo rechtsvdogen
If Row = "1349" Then GoTo rechtsvwanner
If Row = "1394" Then GoTo rechtsvhydrauholz
If Row = "1439" Then GoTo rechtsviwaki10
If Row = "1484" Then GoTo rechtsviwaki30
If Row = "1529" Then GoTo rechtsviwaki35
If Row = "1575" Then GoTo hoofdw1corr
If Row = "1620" Then GoTo hoofdw1kbaan
If Row = "1665" Then GoTo hoofdw1sboog
If Row = "1710" Then GoTo hoofdw1rwas
If Row = "1755" Then GoTo hoofdw1fnswas
If Row = "1800" Then GoTo hoofdw1vborstel
If Row = "1845" Then GoTo hoofdw1dakrol
If Row = "1890" Then GoTo hoofdw1vsboog
If Row = "1935" Then GoTo hoofdw1rswas
If Row = "1980" Then GoTo hoofdw1nsboog
If Row = "2025" Then GoTo hoofdw1droger1
If Row = "2070" Then GoTo hoofdw1droger2
If Row = "2115" Then GoTo hoofdw1hdroger
If Row = "2160" Then GoTo hoofdw1dogen
If Row = "2206" Then GoTo hoofdw2corr
If Row = "2251" Then GoTo hoofdw2kbaan
If Row = "2296" Then GoTo hoofdw2sboog
If Row = "2341" Then GoTo hoofdw2rwas
If Row = "2386" Then GoTo hoofdw2fnswas
If Row = "2431" Then GoTo hoofdw2vborstel
If Row = "2476" Then GoTo hoofdw2dakrol
If Row = "2521" Then GoTo hoofdw2vsboog
If Row = "2566" Then GoTo hoofdw2rswas
If Row = "2611" Then GoTo hoofdw2nsboog
If Row = "2656" Then GoTo hoofdw2droger1
If Row = "2701" Then GoTo hoofdw2droger2
If Row = "2746" Then GoTo hoofdw2hdroger
If Row = "2791" Then GoTo hoofdw2dogen
linksvcorr:
With Sheets("onderhoud").Range("A3:A44")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvkbaan:
With Sheets("onderhoud").Range("A46:A89")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvinweek:
With Sheets("onderhoud").Range("A91:A134")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvvborstel:
With Sheets("onderhoud").Range("A136:A179")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvpjet:
With Sheets("onderhoud").Range("A181:A224")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvrowi:
With Sheets("onderhoud").Range("A226:A269")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvfnswas:
With Sheets("onderhoud").Range("A271:A314")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvdakrol:
With Sheets("onderhoud").Range("A316:A359")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvhdmpower:
With Sheets("onderhoud").Range("A361:A404")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvrwas:
With Sheets("onderhoud").Range("A406:A449")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvrswas:
With Sheets("onderhoud").Range("A451:A494")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvdogen:
With Sheets("onderhoud").Range("A496:A539")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvwanner:
With Sheets("onderhoud").Range("A541:A584")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvhydrauholz:
With Sheets("onderhoud").Range("A586:A629")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksvhydrauduijv:
With Sheets("onderhoud").Range("A631:A674")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksviwaki10:
With Sheets("onderhoud").Range("A676:A719")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksviwaki30:
With Sheets("onderhoud").Range("A721:A764")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
linksviwaki35:
With Sheets("onderhoud").Range("A766:A809")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvcorr:
With Sheets("onderhoud").Range("A812:A853")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvkbaan:
With Sheets("onderhoud").Range("A855:A898")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvinweek:
With Sheets("onderhoud").Range("A900:A943")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvvborstel:
With Sheets("onderhoud").Range("A945:A988")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvpjet:
With Sheets("onderhoud").Range("A990:A1033")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvhwas:
With Sheets("onderhoud").Range("A1035:A1078")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvfnswas:
With Sheets("onderhoud").Range("A1080:A1123")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvdakrol:
With Sheets("onderhoud").Range("A1125:A1168")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvhdmpower:
With Sheets("onderhoud").Range("A1170:A1213")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvrwas:
With Sheets("onderhoud").Range("A1215:A1258")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvrswas:
With Sheets("onderhoud").Range("A1260:A1303")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvdogen:
With Sheets("onderhoud").Range("A1305:A1348")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvwanner:
With Sheets("onderhoud").Range("A1350:A1393")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsvhydrauholz:
With Sheets("onderhoud").Range("A1395:A1438")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsviwaki10:
With Sheets("onderhoud").Range("A1440:A1483")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsviwaki30:
With Sheets("onderhoud").Range("A1485:A1528")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
rechtsviwaki35:
With Sheets("onderhoud").Range("A1530:A1573")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1corr:
With Sheets("onderhoud").Range("A1576:A1619")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1kbaan:
With Sheets("onderhoud").Range("A1621:A1664")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1sboog:
With Sheets("onderhoud").Range("A1666:A1709")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1rwas:
With Sheets("onderhoud").Range("A1711:A1754")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1fnswas:
With Sheets("onderhoud").Range("A1756:A1799")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1vborstel:
With Sheets("onderhoud").Range("A1801:A1844")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1dakrol:
With Sheets("onderhoud").Range("A1846:A1889")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1vsboog:
With Sheets("onderhoud").Range("A1891:A1934")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1rswas:
With Sheets("onderhoud").Range("A1936:A1979")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1nsboog:
With Sheets("onderhoud").Range("A1981:A2024")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1droger1:
With Sheets("onderhoud").Range("A2026:A2069")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1droger2:
With Sheets("onderhoud").Range("A2071:A2114")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1hdroger:
With Sheets("onderhoud").Range("A2116:A2159")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw1dogen:
With Sheets("onderhoud").Range("A2161:A2204")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2corr:
With Sheets("onderhoud").Range("A2207:A2250")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2kbaan:
With Sheets("onderhoud").Range("A2252:A2295")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2sboog:
With Sheets("onderhoud").Range("A2297:A2340")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2rwas:
With Sheets("onderhoud").Range("A2342:A2385")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2fnswas:
With Sheets("onderhoud").Range("A2387:A2430")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2vborstel:
With Sheets("onderhoud").Range("A2431:A2475")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2dakrol:
With Sheets("onderhoud").Range("A2520:A2565")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2vsboog:
With Sheets("onderhoud").Range("A2567:A2610")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2rswas:
With Sheets("onderhoud").Range("A2611:A2655")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2nsboog:
With Sheets("onderhoud").Range("A2657:A2700")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2droger1:
With Sheets("onderhoud").Range("A2702:A2745")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2droger2:
With Sheets("onderhoud").Range("A2747:A2790")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2hdroger:
With Sheets("onderhoud").Range("A2792:A2850")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind
hoofdw2dogen:
With Sheets("onderhoud").Range("A2:A44")
Row = .Find(What:=cboxonderhoud.Text, LookIn:=xlValues, _
LookAt:=xlWhole, MatchCase:=True).Row
MsgBox (Row)
End With
GoTo eind


its a lot of times practically the same info, but it is a rather large sheet.




typo in VBA with the ranges, fixed it in my own file






Jake, can I now use Row as a number in an equation (i.e. wkb.sheets("sheet1").columns("h").rows(row).formulaR1C1 = ........
and then it puts the formula into column H row corresponding to the freshly acquired row number.

Jacob Hilderbrand
03-02-2005, 07:59 AM
Yeah you can use Row.


Sheets("Sheet1").Range("H" & Row).Value = ...