Require suggestion...

[VBABEGINER]

Hey All,
I am trying to find to out and calculate some values, but i cant.
Following is the code, Please help on this code.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call FindnCalculate
End Sub

Public Function FindnCalculate()
Dim fn As Long
Dim cnt As Long
Dim mykW As Double
Dim mykVar As Double
Dim mykVA As Double

' cnt count total rows in col A, if col A found "Circuits Totals:"
cnt = Range("A" & Rows.Count).End(xlDown).Row

For fn = 1 To cnt
If Cells(fn, "A") = "Circuits Totals:" Then

' here in col J and in the same row where i will get this "Circuits Totals:" there is value there.
' I tryed, cells(fn, J) have value, then fn increament to row 15,
' after increament 15 row, cells(fn, B) = "mykW" value i want to given.
' now cells "mykW" [is nothing but cells(fn, B)] = Cells(fn, "J")+Cells(fn, "L")+Cells(fn, "N")
' where, Cells(fn, "L") & Cells(fn, "N") are the adjusent cells to Cells(fn, "J")

If Cells(fn, "J") <> "" Then
fn = fn + 15
' Now here fn value will be same
Cells(fn, "B") = "mykW"
mykW = Cells(fn, "J") + Cells(fn, "L") * 0.3 + Cells(fn, "N")
Cells(fn, "C") = mykVar
mykVar = Cells(fn, "K") + Cells(fn, "M") * 0.3 + Cells(fn, "O")
Cells(fn, "D") = mykVA
mykVA = SQRT(mykW ^ 2 + mykVar ^ 2)
End If
End If
Next
End Function

Is this is possible Experts?
Require great solution.

 

[SydneyGeek]

Do you want to do the whole calculation in VBA, or could you use a formula?
It's pretty simple to create a macro that writes a formula, then return those cells to values if you don't want to keep the formulas.

Denis

 

[VBABEGINER]

No, actually it should in VBA code only.

 

[VBABEGINER]

None of their.

 

[AlphaFrog]

Is there only one "Circuits Totals:" in column A, or more than one?

 

[AlphaFrog]

You should specify the sheet this is suppose to work on. This code assumes the sheet is "Sheet1"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call FindnCalculate
End Sub

Private Sub FindnCalculate()
    Dim fn     As Long
    Dim Found  As Range
    
    With Sheets([COLOR="Red"]"Sheet1"[/COLOR])
        Set Found = .Range("A:A").Find("Circuits Totals:", , xlValues, xlWhole, , xlNext, False)
        If Not Found Is Nothing Then
            fn = Found.Row + 15
            If .Cells(fn, "J").Value <> "" Then
                .Cells(fn, "B") = .Cells(fn, "J") + .Cells(fn, "L") * 0.3 + .Cells(fn, "N")
                .Cells(fn, "C") = .Cells(fn, "K") + .Cells(fn, "M") * 0.3 + .Cells(fn, "O")
                .Cells(fn, "D") = Sqr(.Cells(fn, "B") ^ 2 + .Cells(fn, "C") ^ 2)
            End If
        End If
    End With
End Sub