Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb programing match function
SPRING/SUMMER JANUARY Red Tab Guys 005010234 30 2
SPRING/SUMMER DECEMBER Red Tab Guys 005040037 32 4 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 34 6 How to find with visual basic code in witch row and column is data with match 005040037 and 34, or in this case 3rd row?? Tnx in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb programing match function
Assume your values are stored in range a1 to a3 then try this....
Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("A1:A3") For Each c In r If InStr(1, c.Value, "005040037 34") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub -- Regards, Nigel "Tomo" wrote in message ... SPRING/SUMMER JANUARY Red Tab Guys 005010234 30 2 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 32 4 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 34 6 How to find with visual basic code in witch row and column is data with match 005040037 and 34, or in this case 3rd row?? Tnx in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb programing match function
"Nigel" wrote in message ... Assume your values are stored in range a1 to a3 then try this.... Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("A1:A3") For Each c In r If InStr(1, c.Value, "005040037 34") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub -- Regards, Nigel "Tomo" wrote in message ... SPRING/SUMMER JANUARY Red Tab Guys 005010234 30 2 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 32 4 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 34 6 How to find with visual basic code in witch row and column is data with match 005040037 and 34, or in this case 3rd row?? Tnx in advance! assume first few columns are meanless, we have column model size quantity 005010234 30 2 005040037 32 4 005040037 34 6 find model size formula 005040037 34 =??? i need to match row number for specific combination, model 005040037 and size 34, result is 3 to find only one value, eg 005040037 formula would be =match("005040037 ";A1:A3;0), but for two values i'm stupid.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb programing match function
"Nigel" wrote in message ... Assume your values are stored in range a1 to a3 then try this.... Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("A1:A3") For Each c In r If InStr(1, c.Value, "005040037 34") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub -- Regards, Nigel "Tomo" wrote in message ... SPRING/SUMMER JANUARY Red Tab Guys 005010234 30 2 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 32 4 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 34 6 How to find with visual basic code in witch row and column is data with match 005040037 and 34, or in this case 3rd row?? Tnx in advance! model size quantity 005010234 30 2 005040037 32 4 005040037 34 6 Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("a1:e3") For Each c In r If InStr(1, c.Value, "005040037") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub that works fine, but i need match for 2 values "005040037" and "34" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb programing match function
yes that's it thnx
"Nigel" wrote in message ... Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("a1:a3") For Each c In r If InStr(1, c.Value, "005040037") 0 And _ InStr(1, c.Offset(0, 1).Value, "34") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub -- Regards, Nigel "Tomo" wrote in message ... "Nigel" wrote in message ... Assume your values are stored in range a1 to a3 then try this.... Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("A1:A3") For Each c In r If InStr(1, c.Value, "005040037 34") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub -- Regards, Nigel "Tomo" wrote in message ... SPRING/SUMMER JANUARY Red Tab Guys 005010234 30 2 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 32 4 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 34 6 How to find with visual basic code in witch row and column is data with match 005040037 and 34, or in this case 3rd row?? Tnx in advance! model size quantity 005010234 30 2 005040037 32 4 005040037 34 6 Sub test() Dim r As Range, c As Range, myRow As Long, myCol As Integer Set r = Range("a1:e3") For Each c In r If InStr(1, c.Value, "005040037") 0 Then myRow = c.Row myCol = c.Column End If Next c MsgBox myRow MsgBox myCol End Sub that works fine, but i need match for 2 values "005040037" and "34" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb programing match function
Check your other post, too.
Tomo wrote: SPRING/SUMMER JANUARY Red Tab Guys 005010234 30 2 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 32 4 SPRING/SUMMER DECEMBER Red Tab Guys 005040037 34 6 How to find with visual basic code in witch row and column is data with match 005040037 and 34, or in this case 3rd row?? Tnx in advance! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find the second match using the match function | Excel Worksheet Functions | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |