Help with an Excel Macro

[Dan R.]

I'm new to programming and I'm stuck...

Here's what I'm trying to do:

I'm using the Days360() formula to populate a time interval in column
6, in column 7 I'm using this formula to calculate a dollar value:

=IF(B3="Data1",ROUND(F3/30,2)*15,IF(B3="Data2",ROUND(F3/30,2)*11.5,IF(B3="Data3",ROUND(F3/30,2)*14,"")))

I'm trying to create a macro that will do this b/c my spreadsheet is
already 1MB+.

This is the code I've come up with so far but its probably not right. I
also want the values to populate automatically (without
Worksheet_Change) but I'm not sure how to do that.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Long
R = Target.Row
If Target.Column = 2 Then
Select Case Cells(R, 2)
Case "Data1"
Cells(R, 7) = ???
Case "Data2"
Cells(R, 7) = ???
Case "Data3"
Cells(R, 7) = ???
End Select
End If
End Sub


Thanks in advance.

 

[Guest]

try

public function mycalc(test as string, number as double) as double
application.volatile
select case test
case "Data1"
t=15
case "Data2"
t=11.5
case "Data3"
t=14
case else
t=1 ' decide how you treat this
end select
mycalc = round(number/30,2)*t
end function

and put this in a module and in a cell enter in your spreadsheet:

=mycalc(b3,f3)

 

[Dan R.]

Very clever, thanks a lot Martin it works perfectly!