Getting columns numbers of cells with particular string/substring using VBA

[smide]

Hello.

I have a report in row1 Sheet2 (columns A-EQ).
I need to extract number of column(s) which contains strings/substrings: lines and shop. Column A = 1, Column B = 2 ...

Results should be placed only in column B in Sheet1 (starting from B3 cell).

Example.

Sheet 2

	A	B	C	D	E	F	G	H	I
1	home	moneylines	pattern	shoping	search	lines	linesid	petshop	.....

<tbody>
</tbody>

Sheet 1 (after macro run)

	A	B
1
2
3		2
4		4
5		6
6		7
7		8
8		....

<tbody>
</tbody>

 

[Skiff]

Something here is not right. Did you mean you have multiple rows with data in Sheet2 and then count how many times those strings are in each column?
If not result is just one single number - in your example 5. 5 times in your example you have cells with strings containing "lines" or "shop".

So what is it supposed to be?

 

[Roderick_E]

Option Compare Text 'ignore text case
Sub countlinesshop()
cnt = 3
For x = 1 To 147 ' a-eq
If InStr(Sheet2.Cells(1, x), "lines") Or InStr(Sheet2.Cells(1, x), "shop") Then
Sheet1.Cells(cnt, 2) = x
cnt = cnt + 1
End If
Next x
MsgBox "done"
End Sub

 

[mumps]

Try:

Sub FindText()
    Application.ScreenUpdating = False
    Dim x As Long
    x = 3
    Dim foundString As Range
    For Each foundString In Sheets("Sheet2").Range("A1:EQ1")
        If foundString Like "*lines*" Or foundString Like "*shop*" Then
            Sheets("Sheet1").Cells(x, "B") = foundString.Column
            x = x + 1
        End If
    Next foundString
    Application.ScreenUpdating = True
End Sub

 

[smide]

Something here is not right. Did you mean you have multiple rows with data in Sheet2 and then count how many times those strings are in each column?

Maybe my opening post was a bit vague (sorry for that).
I have a data output only in one row (row1 Sheet2) and I need to find cell references (or column numbers as I said) which contains those strings/substrings and return those cell references transferred into column(s) number in output in Sheet1.

 

[mumps]

Did you look at the posts made by Roderick_E and myself?

 

[smide]

Wow, while I was typing my previous post I got two answers!
Thank you Roderick_E and mumps.

 

[mumps]

Glad to help.