How do you insert a macro into a formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish to write a formula that goes to the end value in a column and uses
that value for multiplying another cell.

I am trying to multiply cell a7 by the last entry in the range c3:c20. I
have the macro that goes to that entry but now need the steps to insert it
into the formula. At the start of the month only cell c3 has data. On the
second day c4 has data and so on.

Thanks
 
=A7*LOOKUP(2,1/(C3:C20<>""),C3:C20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
if all you want to do is always multply a number by the last entry in a range
you dont need a macro....
something like =A7*INDEX(C3:C20,COUNT(C3:c20))
 
The answer to this question will also solve my problem so if there is a way
to place a macro into a formula please what is it?
 
If you create a UDF, it can be called directly from a worksheet formula.

For example

Function myFunc(rng As Range)
myFunc = rng.Cells.Count
End Function

and use like

=IF(myFunc(A1:A10)>1, "Y","N")

these UDFs can only return a value, they cannot alter the worksheet/cells.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
This is from Chip Pearson's site, specifically:

http://www.cpearson.com/excel/differen.htm

" ...
Generally, you cannot call a macro directly from a worksheet cell. For
example, the following code does not work: =IF(A1>10,MyMacro).

You can simulate the statement =IF(A1>10,MyMacro) by using the
worksheet's Change event.

Private Sub Worksheet_Change (ByVal Target As Excel.Range)
If Target = [A1] Then
If Target.Value > 10 Then
MyMacro
End If
End If
End Sub

...."

Hope this helps.

Pete
 
Back
Top