VBA search for numbers within a string of text

[MonkeyTrainee]

My client sends me a spreadsheet every month and I am trying to run a macro where it searches for a number within a cell.
In the below example I have labeled the headers as (Type - Acct #), and then there is data below the headers. Sometimes the client changes the account name. For example, Tax Advantage may be Tax Exempt next month, but the account number (11111) won't change.

Tax Advantage - 11111	US All Cap - 22222	Commodities - 33333	Global Equity - 44444
-50,000	20,000	30,000	-10,000
-75,000	50,000	10,000	50,000

<tbody>
</tbody>

I want to run a macro in VBA where it will search for the account number in the headers, and they copy the data below.

Below is my code so far, I am trying to use the InStr function and getting no results. The goal is to select the headers everytime based on the account number, since this won't be changing.

Sub rebalancing()


Dim c As Range, nr As Long

For Each c In Range("A1:J7")
    If InStr(c.Value, "11111") = 0 Then
    ActiveCell.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    End If
Next

End Sub

Any help would be appreciated.

Thanks,

 

[hateme28]

how about this:

Sub acct()
Dim x As Integer, finalcol, accountnum As Long
accountnum = InputBox("Please enter the account number you want to find", "Account Search", , 9500, 5500)
x = 1
finalcol = cells(1, Columns.Count).End(xlToLeft).Column
 Do
    If InStr(cells(1, x).Value, accountnum) Then
       finalrow = cells(Rows.Count, x).End(xlUp).Row
       Range(cells(1, x), cells(finalrow, x)).Copy
       Exit Do
    End If
    x = x + 1
 Loop Until x > finalcol
End Sub

 

[MonkeyTrainee]

awesome just what i needed, thank you very much