Excel 2003 - VBA - Indirect Addressing

[Craig Brandt]

It seems that I just get a tough problem behind me, and I am faced with what
should have been a no brainer, only to be stopped dead in my tracks. Here is
my problem:



I have a table with fixed consecutive dates (2 years) down the left side and
a strategy indicator for each of those days. The plan is to cycle through
this table, changing the account each pass, and mining the data looking for
accounts that have maintained a single strategy for the whole month.



CALC Sheet

A B C

1 Name Jones

2

3 Dates Value Strategy

4 08-01-06 112,123.01 IMDI

5 08-02-06 112,147.53 IMDI

6 08-03-06 112,165.72 IMDI

..

..

33 08-30-06 112,453.16 IMDI

34 08-31-06 112,432.76 IMDI

35 09-01-08 112,429.43 IMDI

36 09-02-06 112,456.92 IMDI



On another sheet I have the accounts names across the top and dates, one
month increments down the left. Every time I find a whole month with a
single strategy, I want to record that strategy at the Name/Month
intersection. I identify the row in CALC in which the corresponding date
occurs, in column A on the HIST sheet.



HIST Sheet

A B C D E

1 Dates Jones Smith Doe

2 4 08-1-06

3 35 09-1-06

4 65 10-1-06

5 96 11-1-06

6 126 12-1-06



This is the formula in HIST!C2:



=IF(COUNTIF(Calc!$C$4:$C$34,Calc!C4)=A2-A3,Calc!$B$4,"")



Which counts the number of days that have the same strategy as the first day
and if they are the same, I post the first day's strategy in C2.



I created this formula with the following VBA line of code:

Psuedo-Prep code:

Counter = 3 ' Points to column "C" first pass and changes for the next
account to column D.



Cells(2, Counter).FormulaR1C1 = "=IF(COUNTIF(Calc!R" & Cells(2,
1) & "C3:R" & Cells(3, 1)-1 & "C3,Calc!R" & Cells(2, 1) &
"C3)=R[1]C1-RC1,Calc!R" & Cells(2, 1) & "C3,"""")"



This does work for C2 , but when I copy it down as follows, NG.



Cells(2, Counter).AutoFill Destination:=Range(Cells(2, Counter),
Cells(118, Counter)), Type:=xlFillDefault

' the 118 gives me about 10 years of data (preparing for the future).





Can anyone figure out what I need to do to get it working.



Pivot Tables may have been the answer some time ago, but I have way to much
invested in this to start over. My next project will start with Pivot
Tables and I will see if I can get them to work.





Sorry for the long winded description,

Craig

 

[Jim Rech]

You can't copy that formula down unless you revise it to use INDIRECT in
referencing the row number in column A. You might want to enter every
formula in column C with the macro directly and forget about copying down.

--
Jim
| It seems that I just get a tough problem behind me, and I am faced with
what
| should have been a no brainer, only to be stopped dead in my tracks. Here
is
| my problem:
|
|
|
| I have a table with fixed consecutive dates (2 years) down the left side
and
| a strategy indicator for each of those days. The plan is to cycle through
| this table, changing the account each pass, and mining the data looking
for
| accounts that have maintained a single strategy for the whole month.
|
|
|
| CALC Sheet
|
| A B C
|
| 1 Name Jones
|
| 2
|
| 3 Dates Value Strategy
|
| 4 08-01-06 112,123.01 IMDI
|
| 5 08-02-06 112,147.53 IMDI
|
| 6 08-03-06 112,165.72 IMDI
|
| .
|
| .
|
| 33 08-30-06 112,453.16 IMDI
|
| 34 08-31-06 112,432.76 IMDI
|
| 35 09-01-08 112,429.43 IMDI
|
| 36 09-02-06 112,456.92 IMDI
|
|
|
| On another sheet I have the accounts names across the top and dates, one
| month increments down the left. Every time I find a whole month with a
| single strategy, I want to record that strategy at the Name/Month
| intersection. I identify the row in CALC in which the corresponding date
| occurs, in column A on the HIST sheet.
|
|
|
| HIST Sheet
|
| A B C D E
|
| 1 Dates Jones Smith Doe
|
| 2 4 08-1-06
|
| 3 35 09-1-06
|
| 4 65 10-1-06
|
| 5 96 11-1-06
|
| 6 126 12-1-06
|
|
|
| This is the formula in HIST!C2:
|
|
|
| =IF(COUNTIF(Calc!$C$4:$C$34,Calc!C4)=A2-A3,Calc!$B$4,"")
|
|
|
| Which counts the number of days that have the same strategy as the first
day
| and if they are the same, I post the first day's strategy in C2.
|
|
|
| I created this formula with the following VBA line of code:
|
| Psuedo-Prep code:
|
| Counter = 3 ' Points to column "C" first pass and changes for the next
| account to column D.
|
|
|
| Cells(2, Counter).FormulaR1C1 = "=IF(COUNTIF(Calc!R" & Cells(2,
| 1) & "C3:R" & Cells(3, 1)-1 & "C3,Calc!R" & Cells(2, 1) &
| "C3)=R[1]C1-RC1,Calc!R" & Cells(2, 1) & "C3,"""")"
|
|
|
| This does work for C2 , but when I copy it down as follows, NG.
|
|
|
| Cells(2, Counter).AutoFill Destination:=Range(Cells(2,
Counter),
| Cells(118, Counter)), Type:=xlFillDefault
|
| ' the 118 gives me about 10 years of data (preparing for the future).
|
|
|
|
|
| Can anyone figure out what I need to do to get it working.
|
|
|
| Pivot Tables may have been the answer some time ago, but I have way to
much
| invested in this to start over. My next project will start with Pivot
| Tables and I will see if I can get them to work.
|
|
|
|
|
| Sorry for the long winded description,
|
| Craig
|
|
|
|