Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write formula with code ...
Hi Bernie,
It is bugging on the first Range.... :o( XL97? Sige |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write formula with code ...
The second ...
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ... |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write formula with code ...
Sige,
Glad to hear that it worked.... Bernie MS Excel MVP That's my baby!!! <snip Thanks a million Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
could someone write me a VB code? thanks | New Users to Excel | |||
Write code to Write Code | Excel Programming | |||
Code to write out all lines of code | Excel Programming | |||
How do I write a formula to color code based on dates provided or. | Excel Worksheet Functions | |||
How can i write TSR code | Excel Programming |