Error Adding two cells in one worksheet-display in another 1

[dignityy]

Sorry for asking such an newbie question. I am trying to create a macro(VBA) that takes two cells in one worksheet and displays the result in another worksheet. The problem I am having is that when I am in the active worksheet that has the macro I just created see below:

Sub Wk2()
'
' Wk2 Macro
' Macro recorded 10/10/2006
'

'
ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-2]C:R[-1]C)"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-3]C[1]:R[-2]C[1])"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-4]C[3]:R[-3]C[3])"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-5]C[4]:R[-4]C[4])"
Range("C20").Select
ActiveWindow.SmallScroll Down:=9
Range("C32").Select
ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-24]C[6]:R[-23]C[6])"
Range("C33").Select
ActiveWindow.SmallScroll Down:=-18
Range("B33").Select
ActiveCell.FormulaR1C1 = "Period 1 WK 2"
Range("B4").Select



it sometimes won't put the result in the cell I selected in the sum function. I will get random data in cells that the cursor was in(only the first one). Can someone plese help me to understand how to tell the VBA to start and put the data in only the cells I have chosen. And yes I have the macro set to relative off.

 

[DirkStruan]

The first function will be entered into whichever cell you have highlighted at the time you run the macro (as you are working with ActiveCell).

It looks like you need

Range("C10").FormulaR1C1 = "=Sum('06 ROP'!R[-2]C:R[-1]C)"
Range("C11").FormulaR1C1 = "=sum('06 ROP'!R[-3]C[1]:R[-2]C[1])"
Range("C12").FormulaR1C1 = "=sum('06 ROP'!R[-4]C[3]:R[-3]C[3])"
Range("C13").FormulaR1C1 = "=sum('06 ROP'!R[-5]C[4]:R[-4]C[4])"
Range("C32").FormulaR1C1 = "=sum('06 ROP'!R[-24]C[6]:R[-23]C[6])"
Range("B33").FormulaR1C1 = "Period 1 WK 2"

D

 

[dignityy]

That definitely worked. Thanks! Can you use the record feature with active cell off? To clarify, do I have to write the code by hand to get the range function included?

 

[DirkStruan]

Not really - what you needed to do was start with your selected cell not being the first cell you were going to put a formula in. If you'd started anywhere else your recorded macro would have then become:

Sub Wk2()
'
' Wk2 Macro
' Macro recorded 10/10/2006 
'

'
    Range("C10").Select
    ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-2]C:R[-1]C)"
    Range("C11").Select
    ActiveCell.FormulaR1C1 = "=sum('06 ROP'!R[-3]C[1]:R[-2]C[1])"
    Range("C12").Select
    ActiveCell.FormulaR1C1 = etc

The recorded macro makes each formula entry a two stage process:
1) select the cell
2) enter formula

but you can then amend it to be more efficient by removing the selection process and simply entering the formula into the required cell.

Use the recorder to learn the necessary code to enter the functions and then play around with which of the extra bits you can strip out to make it quicker.

D

 

[MeGustaXL]

Just read this one in passing and had to give DirkStruan a STAR for that priceless piece of advice;

Use the recorder to learn the necessary code to enter the functions and then play around with which of the extra bits you can strip out to make it quicker.

Kudos, Dirk!

Chris

Rule Two: What's in it for me? - My Dad

 

[DirkStruan]

Thanks Chris - very kind.

"Crib, Hack, Go" was the basis of my (self taught) learning curve. And it still applies today with the things I learn from TT. (One of the earliest things I learned though was to include a "Continue", vbyesno msgbox in the shorter macros, as I was forever getting crippled by infinite loops! - ah, the ignorance of youth)

Dirk