vb programing match function

[Tomo]

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!

 

[Nigel]

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

 

[Tomo]

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
(e-mail address removed)

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..

 

[Tomo]

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
(e-mail address removed)

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"

 

[Nigel]

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
(e-mail address removed)

 

[Dave Peterson]

Check your other post, too.

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!

 

[Tomo]

yes that's it thnx

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
(e-mail address removed)