MonkeyTrainee
New Member
- Joined
- Aug 14, 2014
- Messages
- 5
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.
Any help would be appreciated.
Thanks,
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.
Code:
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,