Find Multiple texts through VBA

[Raghava@rock]

Hi

I have three different text which needs to find in entire row 16 and set that colummn as variable.


Exmaple: TEXT "RecAmount" is in cell (C16) the i need code to identify the column # which is 3 in this case column c is Third column Set the same as = X

TEXT "DocAmount" is in cell (G16) the i need code to identify the column # which is 7 in this case column G is Seventh column Set the same as = Y


Thanks

 

[SirJB7]

Hi, Raghava@rock!


Give a try to this code:

-----

[pre]

Option Explicit

Sub X()
' constants
Const ksToFind1 = "XXX"
Const ksToFind2 = "YYY"
Const ksToFind3 = "ZZZ"
Const kiRow = 16
Const kiColumnMax = 100
Const kbFindFirst = True
' declarations
Dim X As Integer, Y As Integer, Z As Integer
Dim I As Integer, A As String
' start
X = 0
Y = 0
Z = 0
' process
For I = 1 To kiColumnMax
Select Case ActiveSheet.Cells(kiRow, I).Value
Case ksToFind1
If X = 0 Or Not (kbFindFirst) Then X = I
Case ksToFind2
If Y = 0 Or Not (kbFindFirst) Then Y = I
Case ksToFind3
If Z = 0 Or Not (kbFindFirst) Then Z = I
End Select
Next I
' end
Debug.Print X, Y, Z
End Sub

[/pre]
-----


Just:

a) set proper values to be found (you mentioned 3 but stated 2): first 3 constants

b) adjust maximum column number to be tested: 5th constant

c) set last constant to True if you want the first occurrence's column to be retrieved or to False if last one

d) replace the last "Debug.Print..." statement with whatever you need.


Regards!

 

[kchiba]

Hi,


This is correct, however, you could use the MATCH function without VBA


or you could replace the loop with


Sub test()

inRange = Worksheets("Sheet1").Range("A2:D100")

X = Application.WorksheetFunction.Match("XXX", inRange, 0)

End Sub