Problem with Formula in a cell

[IanPikeBioquell]

Can anybody please help, I have a script that reads data in from an SQL database. At the end of a row of data I want to enter a formula that sums that row. (the row will not always have the same number of columns in it)

I genearate the formular as follows

formular = "=sum(RC[-" & Count - 1 & "]:RC[-1])"

this returns

=sum(RC[-19]:RC[-1]) (for example)

but when I use to populate the cell

.Cells(22, Count + 1).FormulaR1C1 = formular

the cells value becomes

=SUM(R[-1]C[217]:R[-1]C[235])


Hope this is clear and that someone can help. I am using Excel 2003 on a Vista PC.

 

[ck1999]

Not sure why this is happening

Are you looking at the value of cell M22. This according to the info posted above is where

.Cells(22, Count + 1).FormulaR1C1 = formular

is making the reference.

Also when i tested in xl07 the cell value is =SUM(B22:L22)

Can you check formula of cell m22?

Also is this code in a loop? if so, are you reseting count each time?

ck1999

 

[IanPikeBioquell]

The code is not in a loop, it is after a loop that has been incrementing, i use the count value so as i know what is the last column inserted into.

One thing i have noticed is if i put a break point in my code at

formular = "=sum(RC[-" & CStr(Count - 1) & "]:RC[-1])"

Then use F8 to step through the

.Cells(22, Count + 1).FormulaR1C1 = formular

the formular is inserted correctly (strnage but true!!!)

 

[ck1999]

That does not make alot of sense

Please post all or atleast more of the code.
ck1999

 

[IanPikeBioquell]

Complete SUB as requested.

Sorry notice i typed 22 instead of 2 for the row reference in my original post.

Public Sub DoGraph()
Dim MySQL As String
Dim MyCon As New Connection
Dim MyRec As New Recordset
Dim Count As Integer
Dim ufindate As Date

wks3.Activate

With wks3
.Cells(2, 1).Value = 9989
.Cells(3, 1).Value = 9990
.Cells(4, 1).Value = 9991
.Cells(5, 1).Value = 9992
.Cells(6, 1).Value = 9993
.Cells(7, 1).Value = 9994
.Cells(8, 1).Value = 9995
.Cells(9, 1).Value = 9996
.Cells(10, 1).Value = 9997
.Cells(11, 1).Value = 9998
.Cells(12, 1).Value = 9999
End With

If ConnectionString = "" Then
GetConnectionString
End If

'ClearData
'GetDates

MyCon.Open ConnectionString
MySQL = "select sum(uhours) uhours, uopnum, ufindate " & _
"from used, operators " & _
"where ufindate between " & TSStartDate & _
" and " & TSFinishDate & _
"and uoperator = oprid " & _
"and uopnum > 9000 " & _
"group by ufindate, uopnum " & _
"order by ufindate, uopnum"
MyRec.Open MySQL, MyCon

'Read each row of the record set
Count = 1
While Not MyRec.EOF
ufindate = MyRec!ufindate
uopnum = MyRec!uopnum
uhours = MyRec!uhours

If ufindate <> olddate Then
Count = Count + 1
End If

With wks3
.Cells(1, Count).Value = ufindate
.Cells((uopnum - 9989) + 2, Count).Value = uhours
End With

MyRec.MoveNext


olddate = ufindate
Wend

'Close efacs connections
MyRec.Close
MyCon.Close



MyRange = Range([A1], Cells(12, Count)).Address

Charts.Add
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range(MyRange)
ActiveChart.Location Where:=xlLocationAsNewSheet ', Name:="Sheet2"
ActiveChart.PlotBy = xlRows

formular = "=sum(RC[-" & CStr(Count - 1) & "]:RC[-1])"


With wks3
.Cells(2, Count + 1).FormulaR1C1 = formular
.Cells(3, Count + 1).Formula = formular
.Cells(4, Count + 1).Formula = formular
.Cells(5, Count + 1).Formula = formular
.Cells(6, Count + 1).Formula = formular
.Cells(7, Count + 1).Formula = formular
.Cells(8, Count + 1).Formula = formular
.Cells(9, Count + 1).Formula = formular
.Cells(10, Count + 1).Formula = formular
.Cells(11, Count + 1).Formula = formular
.Cells(12, Count + 1).Formula = formular
End With
End Sub

 

[ck1999]

What is the value of count at MyRange = Range([A1], Cells(12, Count)).Address. You can hit f9 and stop the code here to determine the vaue of count or place a msgbox count before this statement.

Why do you not use formular1c1 for the rest of the cells instead of only with .cells(2,count+1)?

What are the formulas in the cells after the code is run?

Delete the contents of the worksheet that the code is suppose to add before running the code again.

You should not use CStr

ck1999

 

[IanPikeBioquell]

Count at this point = 20

I was trying to see if formular1c1 would cure the problem.

Again I was using CStr to see if that was the problem.

Having run the code the formula in the cells reads'

=SUM(R[-1]C[217]:R[-1]C[235])

If i debug.print formula in VBA after it has been set it returns

=sum(RC[-19]:RC[-1])

Thanks for all your help.

 

[ck1999]

Sorry I mispoke the cell would be col U not col M

try this

formular = "=sum(B2:" & Cells(2, Count).Address() & ")"
Cells(2, Count + 1).Formula = formular

ck1999

 

[ck1999]

Having run the code the formula in the cells reads'

Which cell is this in?

ck1999

 

[IanPikeBioquell]

Great, thanks very much ck1999 that worked a treat.

I will now try and work out what you have done.

Again may thanks for your help much appreciated.

 

[ck1999]

I noticed that since your code starts in column B then you do not have to use the r1c1 type of reference

"=sum(B2:" & Cells(2, Count).Address() & ")"

b2 is the 1st cell in row 2 to start adding

cells(2,count) is the last cell in the row 2 to add

.address() returns "T2" in this example

so you formular = "=Sum(B2:T2)"

ck1999

 

[IanPikeBioquell]

Thanks for that.

It always seems to be the simplest of things that cause the biggest problems!

IanPikeBioquell

 

[AnotherHiggins]

I started reading this thread then got pulled away and am just now returning....

You seem content with the answer, IanPikeBioquell, but I don't think it is going to work for you. The formula, "formular", you started with was fine (the one in the first post without CStr).

Note that ck's suggested,
[tab]formular = "=sum(B2:" & Cells(2, Count).Address() & ")"
actually returns
[tab][COLOR=blue white]"=sum(B2:$G$2)"[/color blue white]

I don't think you don't want to hard code the "B2" (not given the way you are currently entering the formulas onto the sheet, anyway) and you don't want to use Absolute References for G2 (the dollar signs indicate Absolute Reference). If you continue through your code with that, I'm pretty sure you'll end up with the same result on every single row.

Try this instead - replace the last bit of your code, the part that actually inserts the formula into the worksheet, with this:

    Range(Cells(2, Count + 1), Cells(12, Count + 1)) = formular

(using your original formular)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[IanPikeBioquell]

Thanks for that, I will try this as well.

 

[IanPikeBioquell]

anotherhiggins,

Have tried what you suggested and after running the formula in the cells reads

=SUM(R[-1]C[217]:R[-1]C[235])

 

[ck1999]

John,

From my understanding of his code
He is only adding 1 line of data. and Data always starts in Col B
and if he decides to loop and add more he only needs to change
it to

vrow = 2' Variable for the Row of data
formular = "=sum(" & Cells(vrow, Count).Address() &":" & Cells(vrow, Count).Address() & ")"

ck1999

 

[ck1999]

Forgot to change count to 2
formular = "=sum(" & Cells(vrow, 2).Address() &":" & Cells(vrow, Count).Address() & ")

 

[ck1999]

IanPikeBioquell,
You code states

While Not MyRec.EOF
        ufindate = MyRec!ufindate
        uopnum = MyRec!uopnum
        uhours = MyRec!uhours
        
        If ufindate <> olddate Then
            Count = Count + 1
        End If
    
        With wks3
            .Cells(1, Count).Value = ufindate
            .Cells((uopnum - 9989) + 2, Count).Value = uhours
        End With
        MyRec.MoveNext       
        olddate = ufindate
    Wend

If ufindate <> olddate Then
Count = Count + 1
End If

when ufindate<>olddate you add 1 to count however, you are still using the record to update the current column. I am not sure if you want to do this or not since you are overwriting current data if ufindate=oldate

ck1999

 

[ck1999]

John,

I you are correct in I was only looking at the 1st row of data. My apologies for not completely understanding your post the 1st time through when I read it.

This should clarify

 With wks3
    for vrow= 2 to 12
     .Cells(vrow, Count + 1).Formula = "=sum(" & Cells(vrow, 2).Address() &":" & Cells(vrow, Count).Address() & ")"
next vrow
    End With

ck1999

 

[AnotherHiggins]

No problem.

IanPikeBioquell:

This doesn't make any sense: "the formula in the cells reads =SUM(R[-1]C[217]:R[-1]C[235])".

First, that tells me that you have R1C1 reference style turned on. Most folks turn that off (Tools > Options > General, uncheck the box beside "R1C1 reference style").

But regardless, I think ck is definitely on to something.... The only reason you would get "217" for "Count - 1" is if "Count" = 218. Even more confusing, though, is the "R[-1]". That means you are summing the row above where the formula resides. If you define formular as "=sum(RC[-" & Count - 1 & "]:RC[-1])" then where does the R[!][-1][/!]C[217] come from?

How about retooling that whole part of the module and use the following instead:

FormulaCol = _
    Worksheets("NameHere").UsedRange.Columns.Count + 1
Myformula = _
    "=sum(RC[-" & FormulaCol - 2 & "]:RC[-1])"
Range(Cells(2, FormulaCol), Cells(12, FormulaCol)) = Myformula

-FormulaCol finds how many columns are used on your worksheet and adds 1
-MyFormula is the same as your formular except I am using FormulaCol instead of Count
-I am putting the formula into all destination cells at once, replacing multiple lines of code with a single line

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.