PDA

View Full Version : formula to macro



preseb
06-17-2011, 06:44 AM
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
06-17-2011, 06:57 AM
Actualy that formula could also be written.
=SUMPRODUCT(H49:H64,I49:I64)/SUM(I49:I64)

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

preseb
06-17-2011, 07:02 AM
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
06-17-2011, 07:59 PM
Change the number of rows in the range in the With statment and the result will be appropriate for that new range.

Paul_Hossler
06-18-2011, 07:34 AM
Not as concise as Mike's but maybe if you have a function in the VBA you could re-use it easier


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



Also works from the worksheet side


=getdata(BD4)


Paul

Capungo
10-13-2011, 08:04 AM
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...

N1 = 0#
N2 = 0#

Bob Phillips
10-13-2011, 09:14 AM
Assign the value of 0 to both variables N1 and N2.

Paul_Hossler
10-13-2011, 02:07 PM
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 : pray2: , 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) :think:

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 (http://vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.12.1033/EXCEL.DEV/content/HV01204190.htm)"



Double (double-precision floating-point) variables (http://vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.12.1033/EXCEL.DEV/content/HV01200929.htm) 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 (http://vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.12.1033/EXCEL.DEV/content/HV01200929.htm) for Double is the number sign (#).




Paul

Capungo
10-15-2011, 06:41 AM
Thank you for the explanation. I did not know that...