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.


[TABLE="width: 1000"]
<tbody>[TR]
[TD]Tax Advantage - 11111
[/TD]
[TD]US All Cap - 22222[/TD]
[TD]Commodities - 33333[/TD]
[TD]Global Equity - 44444[/TD]
[/TR]
[TR]
[TD]-50,000[/TD]
[TD]20,000[/TD]
[TD]30,000[/TD]
[TD]-10,000[/TD]
[/TR]
[TR]
[TD]-75,000[/TD]
[TD]50,000
[/TD]
[TD]10,000[/TD]
[TD]50,000[/TD]
[/TR]
</tbody>[/TABLE]

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,

 

[pgc01]

Hi
Welcome to the board

InStr() returns zero when it does not find the value.

I think you meant:

If InStr(c.Value, "11111") [SIZE=3][FONT=arial black][B][COLOR=#B22222]>[/COLOR][/B][/FONT][/SIZE] 0 Then

 

[MonkeyTrainee]

great thank you very much

 

[pgc01]

You're welcome. Thanks for the feedback.