Write formula with code ...
 

Hi There,

Starting Cell = A20 (GBSUP)
Number of items = Variable but Starting at $B$21 ...B65000
Number of Periods = Variable C19 ...20 ...21 ....


DEC1403 DEC1204 DEC1105 .....
GBSUP
WHITE B.I.B1 1,9 1,8 2,1
WHITE B.I.B2 4,6 4 4,6
WHITE B.I.B3 4,1 4,4 5,3
WHITE B.I.B4 0 0 0
WHITE B.I.B5 0 0 0
.....

I would like to write via code a formula that multiplies my %shares
(the figures above) with my totals which I calculate as follows:
=INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATC H(C$19;INDEX(SOURCE;1;0);0))

In this example I would copy down and 3 cells to the right in F21:
=OFFSET($A$20;ROW()-20;COLUMN()-COUNTA($19:$19)-1;1;1)*INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0 );MATCH(C$19;INDEX(SOURCE;1;0);0))

The problem is that my starting point to plug this formula in varies:
It depends on the number of periods chosen (in row 19)
(and the number of items down).

Hope you understand,
Could you help me out please?

Best Regards Sige

Write formula with code ...
 

Sige,

How would your formula vary with different values? Post two example cases, and the cell values on
which you want the formula to depend.

HTH,
Bernie
MS Excel MVP


"Sige" wrote in message
ups.com...
Hi There,

Starting Cell = A20 (GBSUP)
Number of items = Variable but Starting at $B$21 ...B65000
Number of Periods = Variable C19 ...20 ...21 ....


DEC1403 DEC1204 DEC1105 .....
GBSUP
WHITE B.I.B1 1,9 1,8 2,1
WHITE B.I.B2 4,6 4 4,6
WHITE B.I.B3 4,1 4,4 5,3
WHITE B.I.B4 0 0 0
WHITE B.I.B5 0 0 0
.....

I would like to write via code a formula that multiplies my %shares
(the figures above) with my totals which I calculate as follows:
=INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATC H(C$19;INDEX(SOURCE;1;0);0))

In this example I would copy down and 3 cells to the right in F21:
=OFFSET($A$20;ROW()-20;COLUMN()-COUNTA($19:$19)-1;1;1)*INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0 );MATCH(C$19;INDEX(SOURCE;1;0);0))

The problem is that my starting point to plug this formula in varies:
It depends on the number of periods chosen (in row 19)
(and the number of items down).

Hope you understand,
Could you help me out please?

Best Regards Sige

Write formula with code ...
 

Hi Bernie,

My formula does not change with different values ...

It is just that:
If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0))
respectively.

To get a result in F21, G21, H21

IF I have 4 periods: C-D-E-F
Then I should multiply the corresponding product on row 21 with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0))
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (F$19;INDEX(SOURCE;1;0);0))
To get a result in G21, H21, I21,J21

and this down for the number of items in column B.

Does this make more sense??
If not, I'll try again ;o)

Sige

Write formula with code ...
 

If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

respectively.


To get a result in F21, G21, H21
==
F21= C21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

G21= D21*
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

H21= E21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

F22 =C22
**INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MAT CH(C$19;INDEX(SOURCE;1;0);0))
.... for the nr of items in column B
<==

HTH

Write formula with code ...
 

Sige,

Try the macro below.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myRow As Long
Dim myCol As Integer
Dim myOff As Integer
myRow = Range("B65536").End(xlUp).Row
myCol = Range("IV19").End(xlToLeft).Column
Range("C21", Cells(myRow, myCol)).FormulaR1C1 = _
"=INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0) " & _
";MATCH(R19C;INDEX(Source;1;0);0))"
myOff = myCol - 2
Range(Cells(21, myCol + 1), Cells(myRow, (myCol - 2) * 2 + 2)).FormulaR1C1 = _
"=RC[-" & myOff & "] *INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0)" & _
";MATCH(R19C[-" & myOff & "];INDEX(Source;1;0);0))"
End Sub




"Sige" wrote in message
oups.com...
If I have 3 periods (Column C, D, E -row19), as in the above example.
Then I should multiply the corresponding product
on row 21 (and down...) with
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

respectively.


To get a result in F21, G21, H21
==
F21= C21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (C$19;INDEX(SOURCE;1;0);0*))

G21= D21*
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (D$19;INDEX(SOURCE;1;0);0*))

H21= E21 *
INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATCH (E$19;INDEX(SOURCE;1;0);0*))

F22 =C22
**INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MAT CH(C$19;INDEX(SOURCE;1;0);0))
.... for the nr of items in column B
<==

HTH

Write formula with code ...
 

Hi Bernie,

It is bugging on the first Range.... :o(

XL97?
Sige

Write formula with code ...
 

Sige,

Try splitting that one line into two, like this, to help identify the cause error:

Range("C21", Cells(myRow, myCol)).Select

Selection.FormulaR1C1 = _
"=INDEX(Source;MATCH(R20C1;INDEX(Source;;1);0) " & _
";MATCH(R19C;INDEX(Source;1;0);0))"

Is it the first statement or the second that throws the error?

HTH,
Bernie
MS Excel MVP


"Sige" wrote in message
oups.com...
Hi Bernie,

It is bugging on the first Range.... :o(

XL97?
Sige

Write formula with code ...
 

The second ...

Write formula with code ...
 

Copy this formula

=INDEX(SOURCE;MATCH($A$20;INDEX(SOURCE;;1);0);MATC H(C$19;INDEX(SOURCE;1;0);0))

Then go to Excel, press record macro, paste that formula into cell C21, and press Enter. Then stop
the macro recorder, and post the code that was generated.

HTH,
Bernie
MS Excel MVP


"Sige" wrote in message
ps.com...
The second ...

Write formula with code ...
 

Hi Bernie,

I did what you asked .... but upon entering the formula,
I get "Cannot run Visual Basic Macro because of a syntax error."

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 6/02/2006 by guest
'

'
ActiveCell.FormulaR1C1 = _

"=INDEX(SOURCE,MATCH(R20C1,INDEX(SOURCE,,1),0),MAT CH(R19C,INDEX(SOURCE,1,0),0))"
End Sub

Write formula with code ...
 

Sige,

Try this version. I don't use ; as my seperator, and forgot that formulas entered in VBA need to
use US ,-based syntax rather than the localized syntax.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRow As Long
Dim myCol As Integer
Dim myOff As Integer
myRow = Range("B65536").End(xlUp).Row
myCol = Range("IV19").End(xlToLeft).Column
Range("C21", Cells(myRow, myCol)).FormulaR1C1 = _
"=INDEX(Source,MATCH(R20C1,INDEX(Source,,1),0) " & _
",MATCH(R19C,INDEX(Source,1,0),0))"
myOff = myCol - 2
Range(Cells(21, myCol + 1), Cells(myRow, (myCol - 2) * 2 + 2)).FormulaR1C1 = _
"=RC[-" & myOff & "] *INDEX(Source,MATCH(R20C1,INDEX(Source,,1),0)" & _
",MATCH(R19C[-" & myOff & "],INDEX(Source,1,0),0))"
End Sub


"Sige" wrote in message
oups.com...
Hi Bernie,

I did what you asked .... but upon entering the formula,
I get "Cannot run Visual Basic Macro because of a syntax error."

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 6/02/2006 by guest
'

'
ActiveCell.FormulaR1C1 = _

"=INDEX(SOURCE,MATCH(R20C1,INDEX(SOURCE,,1),0),MAT CH(R19C,INDEX(SOURCE,1,0),0))"
End Sub

Write formula with code ...
 

Hi Bernie,

That's my baby!!!
Sorry for all the trouble ...

It should only have been:
Sub TryNow()
Dim myRow As Long
Dim myCol As Integer
Dim myOff As Integer
myRow = Range("B65536").End(xlUp).Row
myCol = Range("IV19").End(xlToLeft).Column

'Range("C21", Cells(myRow, myCol)).FormulaR1C1 = _
' "=INDEX(Source,MATCH(R20C1,INDEX(Source,,1),0) " & _
' ",MATCH(R19C,INDEX(Source,1,0),0))"

myOff = myCol - 2
Range(Cells(21, myCol + 1), Cells(myRow, (myCol - 2) * 2 +
2)).FormulaR1C1 = _
"=RC[-" & myOff & "] *INDEX(Source,MATCH(R20C1,INDEX(Source,,1),0)"
& _
",MATCH(R19C[-" & myOff & "],INDEX(Source,1,0),0))"
End Sub


I've never seen the usefulness of the RC-notation (not that I am an
expert ;o))) ) but here it comes in handy!

Thanks a million Bernie

Write formula with code ...
 

Sige,

Glad to hear that it worked....

Bernie
MS Excel MVP


That's my baby!!!

<snip

Thanks a million Bernie