Hi there

I've written a code to process my data but I have an error message which I
am not sure how to fix

qrow = 7

Range("qrow" & ",82" & ":" & "qrow" & ",86").FormulaR1C1 =
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

I need to reset the formula for the whole work book and some of them are the
same, I've looped and have if statement to do until a cell in "qrow" in
column A is empty, if not empty then in qrow and column "82" to column "86"
will be filled with the sum if formula.

Before I have written for each column but just thought I might be able to
cut down the code by almost half the size hence just made up this code.

Does it make sense?

Can someone please help urgently??

Thanks

Vivi

Try
qrow = 7
Range(Cells(qrow, 82), Cells(qrow, 86)).Formula = _
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

If this post helps click Yes
---------------
Jacob Skaria


"vivi" wrote:

Hi there

I've written a code to process my data but I have an error message which I
am not sure how to fix

qrow = 7

Range("qrow" & ",82" & ":" & "qrow" & ",86").FormulaR1C1 =
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

I need to reset the formula for the whole work book and some of them are the
same, I've looped and have if statement to do until a cell in "qrow" in
column A is empty, if not empty then in qrow and column "82" to column "86"
will be filled with the sum if formula.

Before I have written for each column but just thought I might be able to
cut down the code by almost half the size hence just made up this code.

Does it make sense?

Can someone please help urgently??

Thanks

Vivi

A couple of points about your approach. First, anything contained in quote
marks is seen by VB as a sequence of characters without any meaning; so,
when you put qrow inside those quote marks, VB will not see it is a
variable... to VB, "qrow" has no more meaning than "a&/y" does... they are
just characters. Hence, to get the 7 that has been assigned to qrow, you
just use qrow without any quote marks. Second, the string of text you are
trying to feed the Range property would be meaningless even if you left the
quote marks off. Why? Because you seem to be trying to form an address using
the colon delimiter of a range. Ranges formed that way would normally be
shown in A1:B2 type format... column letter first, row number second within
each section of the address. Since you know the column numbers, then you
know their letter designations as well. Give the above, you can form your
address like this....

Range("CD" & qrow & ":CH" & qrow)").FormulaR1C1 =
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

--
Rick (MVP - Excel)


"vivi" wrote in message
...
Hi there

I've written a code to process my data but I have an error message which I
am not sure how to fix

qrow = 7

Range("qrow" & ",82" & ":" & "qrow" & ",86").FormulaR1C1 =
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

I need to reset the formula for the whole work book and some of them are
the
same, I've looped and have if statement to do until a cell in "qrow" in
column A is empty, if not empty then in qrow and column "82" to column
"86"
will be filled with the sum if formula.

Before I have written for each column but just thought I might be able to
cut down the code by almost half the size hence just made up this code.

Does it make sense?

Can someone please help urgently??

Thanks

Vivi

Another:

cells(qrow, 82).resize(1,5).formular1c1 = "=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

(.resize(1,5) means 1 row by 5 columns.)


vivi wrote:

Hi there

I've written a code to process my data but I have an error message which I
am not sure how to fix

qrow = 7

Range("qrow" & ",82" & ":" & "qrow" & ",86").FormulaR1C1 =
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

I need to reset the formula for the whole work book and some of them are the
same, I've looped and have if statement to do until a cell in "qrow" in
column A is empty, if not empty then in qrow and column "82" to column "86"
will be filled with the sum if formula.

Before I have written for each column but just thought I might be able to
cut down the code by almost half the size hence just made up this code.

Does it make sense?

Can someone please help urgently??

Thanks

Vivi

--

Dave Peterson

Thanks everyone!!! I forgot to tick notify me of replies box so didn't know
so many of you had helped!!! Thanks very much for the codes Jacob, it works
perfectly!!

Thanks Dave ... I have tried this formula before and it didn't work for me
but now I know where I went wrong, thanks a lot for all your help!!

"Dave Peterson" wrote:

Another:

cells(qrow, 82).resize(1,5).formular1c1 = "=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

(.resize(1,5) means 1 row by 5 columns.)


vivi wrote:

Hi there

I've written a code to process my data but I have an error message which I
am not sure how to fix

qrow = 7

Range("qrow" & ",82" & ":" & "qrow" & ",86").FormulaR1C1 =
"=SUMIF(R4C22:R4C81,R6C,RC22:RC81)"

I need to reset the formula for the whole work book and some of them are the
same, I've looped and have if statement to do until a cell in "qrow" in
column A is empty, if not empty then in qrow and column "82" to column "86"
will be filled with the sum if formula.

Before I have written for each column but just thought I might be able to
cut down the code by almost half the size hence just made up this code.

Does it make sense?

Can someone please help urgently??

Thanks

Vivi

--

Dave Peterson