Macro for analysing complete block designs

[keerthi4786]

Hi I am new to excel vba and i am learning on my own to write vba in excel. I have some queries in vba.
I created data sheet matrix by entering number of treatments and blocks using vba and entered data into matrix. After i calculated row total and column total using sum function and finding last column and row. Then estimated grand total of data i e sum of column total or row total and find the cell having grand total using dim grandtotal as long. Now i wanted to calculate correction factor in another empty cell using range.formula function. But when i run macro it will give #Name instead of value. The following is code i used to run macro. Kindly solve my issue.

Private Sub CommandButton5_Click()
Dim lastcolumn As Long
lastcolumn = Sheet1.Cells(3, Columns.Count).End(xlToLeft).Column
Dim grandtotal As Long
grandtotal = Sheet1.Cells(Rows.Count, lastcolumn).End(xlUp).Copy


Range("O3").Value = total
Range("O4").Formula = "=grandtotal^2/(B2*B3)"
End Sub

Where B2 and B3 are cells having number of treatments and blocks

 

[gallen]

Hello
Your issue is with this line:

[COLOR=#333333]Range("O4").Formula = "=grandtotal^2/(B2*B3)"[/COLOR]

If you look at O4's formula you will see that instead of the value of the 'grandtotal' variable it has entered the text "grandtotal". Excel will assume this is the name of a cell.

Instead your code should be:

Range("O4").Formula = "=" & grandtotal & "^2/(B2*B3)"

 

[keerthi4786]

Even if i use above formula the result is wrong.

I have find last column having row sums then assigned grand sum of row sums into variable grandtotal (dim grandtotal as long) to next empty cell in that same column. Should i convert grandtotal variable into range to use it in formula. Even if i copy value of cell (324) assigned to grandtotal into anoother cell, it is pasted as -1 value instead of actual value 324.

 

[keerthi4786]

Hi Gallen

Thanks I got correct result.