Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with an Excel Macro
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. -- Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with an Excel Macro
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) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Dan R." wrote: 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. -- Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with an Excel Macro
Very clever, thanks a lot Martin it works perfectly!
-- Dan Martin Fishlock wrote: 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) -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Dan R." wrote: 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. -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |