formula to macro

[preseb]

I have a formula that I want to convert to a macro:=(H49*I49+H50*I50+H51*I51+H52*I52+H53*I53+H54*I54+H55*I55+H56*I56+H57 *I57+H58*I58+H59*I59+H60*I60+H61*I61+H62*I62+H63*I63+H64*I64)/SUM(I49:I64)

The colums will always be the same, but the range may vary.

any help would be greatly appreciated

[mikerickson]

Actualy that formula could also be written.
=SUMPRODUCT(H49:H64,I49:I64)/SUM(I49:I64)

One way to convert it to VBA is
[VBA]With Range("A49:A64").EntireRow
MsgBox Evaluate( "SUMPRODUCT(" & .Columns(8).Address & "," & .Columns(9).Address) & ")/SUM(" & .Columns(9).Address & ")")
End With[/VBA]

[preseb]

It looks like the With Range ("A49:A64") is setting the number of rows.
My concern is that the number of row will vary from the 49:64. sometimes it may be 49:62, other times 49:65

[mikerickson]

Change the number of rows in the range in the With statment and the result will be appropriate for that new range.

[Paul_Hossler]

Not as concise as Mike's but maybe if you have a function in the VBA you could re-use it easier

[VBA]
Option Explicit
'r is any cell in the 'top' row
Function GetData(r As Range) As Variant
Dim i As Long
Dim N1 As Double, N2 As Double
Dim rWorking As Range

Set rWorking = r.Cells(1, 1).EntireRow

N1 = 0#
N2 = 0#

With rWorking
For i = 49 To 64
N1 = N1 + .Cells(1, i).Value * .Cells(2, i).Value
N2 = N2 + .Cells(2, i).Value
Next i
End With

If N2 <> 0# Then
GetData = N1 / N2
Else
GetData = CVErr(xlErrDiv0)
End If
End Function


Sub test()
MsgBox GetData(Range("A4"))
MsgBox GetData(Range("D4"))
MsgBox GetData(Range("BA4"))
MsgBox GetData(Range("DD4"))
End Sub
[/VBA]


Also works from the worksheet side

[VBA]
=getdata(BD4)
[/VBA]

Paul

[Capungo]

Hello Paul,

I am a newbie at VBA. Reading all the posts as far as possible. I couldn't understand what you meant by the expression below. Thank you in advance...

[VBA] N1 = 0#
N2 = 0# [/VBA]

[Bob Phillips]

Assign the value of 0 to both variables N1 and N2.

[Paul_Hossler]

N1 and N2 were Dim-ed as a Double precision number (e.g. 12345.678)

I'm old-school (OK, really old school) and I was brought up thinking that if I were to do just assign N1 = 1 (the 1 is a Long integer), VBA would have to call a 'Long2Double' conversion. That takes time (OK, not much , but every little bit helps)

If I assign N1 = 1.0, VBA knows that 1.0 is a Double percision number to begin with and skips the conversion (or at least that what I tell myself)

However, VBA editor reformats the 1.0 as 1#. The # suffix just means that the 1 is not a integer 1, but a double precision number

Excel help, "Data Type Summary"

Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The type-declaration character for Double is the number sign (#).

Paul

[Capungo]

Thank you for the explanation. I did not know that...