finding string with wildcard in for each loop

[Roshan.Shakya]

Hi gurus and experts,

I have written the following code to find out the specific string "currency" in a workbook controlled by another user.

Option Compare Text

Sub findtext()

Dim cll as range, endrow as integer, cl_name, rw_name

endrow = ActiveSheet.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For each cll in range(A:AZ" & endrow)
    If InStr(cll.Value, "currency") > 0 Then
        cl_name = Split(cll.Address, "$")(1)
        rw_name = Split(cll.Address, "$")(2)
    End If
Next cll

End Sub

However, the string "currency" is sometimes written as "cy", "ccy", or "cncy" by another user. To accommodate all possible 4 cases, I tried to find the cell and its address by using the wildcard "*", however, it did not work. Only the string value with exact match worked.

The reason I used "For Each" loop is because the user, at times, adds columns on adhoc basis and I cannot specify code to a particular column. The structure of report generally remains same.

Is there any means to solve this issue? I appreciate for your suggestions.

Thank you

[nankw83]

Hi Roshan,

Couple of questions, what do you want to do with the variables cl_name & rw_name ? Do the cells contain other data or just the currency word ? Also, is there any formulas in those cells ?

[Roshan.Shakya]

Thank you nankw83 for your queries.

Once I find the column where the "currency" head is located, I will do similar search for another head "exposure amount" (that will probably be next to the currency column). Since user adds columns for various reconciliation purposes, the only thing that is certain is - currency and it's respective amount will be in the same row.

The identification of columns enables me to pick the desired (amount) data based on the currency using conditional statements in For loop. The cells containing heads ("currency" and "exposure amount") are value data and do not contain formula while other than that there could be many formulas.

Thank you

[Norie]

Are you trying to locate the currency column?

If so which row are the headers in?

[Roshan.Shakya]

Thank you Norie for your question.

The rows are also uncertain that is why I am trying to find the row and column address. By the way, I used the following line to resolve the issue:

Option Compare Text

Sub findtext()

Dim cll as range, endrow as integer, cl_name, rw_name

endrow = ActiveSheet.Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For each cll in range(A:AZ" & endrow)
    If cll.Value Like "*c*c*y*" Then
        cl_name = Split(cll.Address, "$")(1)
        rw_name = Split(cll.Address, "$")(2)
    End If
Next cll

End Sub

I have ruled out searching "cy" for the time being after taking stock of the number of reports.