Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Macro
I am having trouble executing a macro. I get the following error:
COMPILE ERROR: Sub or Function not defined (Location of error noted below in 2nd macro.) Can someone tell me what I need to do? Below are two macros I am using with explanation of what I am trying to do. 'The user will click a commandbutton to begin process. Private Sub CommandButton11_Click() Dim TextBox1 As Double 'accepts date mm/dd/yy Dim TextBox2, TextBox3 As Double 'accepts time hh:mm TimeCalcFm.Show End Sub __________________________________________________ ___ This macro shown below is executed when user clicks ENTER button on userform. NOTE: Time entered is based on 24-hr time. User enters Date Worked (textbox1), Time-In (textbox2), and Time-Out(textbox3). Spreadsheet has the date of every Monday (beginning of week) in cells B125 - B176. Columns C-G (for rows 125-176) are Mon, Tue, Wed, Thur, Fri. The purpose of below macro is: 1) Calculate time in hours based on time in/out: Example: In = 8:00 Out = 17:23 (entered by user) ttltime1=out-in (9:23) ttltime2 = HOUR(ttltime1)+(MINUTE(ttltime1)/60) (9.38 hrs) 2) Determine correct cell to place ttltime2 in, ie what day worked that particular week. Based on description of layout above, I want to compare the date entered (datework) against each date in B125:B176. When I find the first occurance of datework being < date in spreadsheet (B125:B176), then I know I need to backup one row and then offset appropriately to place ttltime2 in correct spot. Private Sub CommandButton1_Click() Dim i As Single Dim ttltime1, ttltime2 As Double ttltime1 = TextBox2 - TextBox1 ttltime2 = Hour(ttltime1) + (Minute(ttltime1) / 60) For i = 125 To 176 If TextBox1 < .Cell(i, 2).Value Then <<<< POINT OF ERROR = Cell ' next line figures offset and places ttltime2 in correct cell .Cell(i-1, (TextBox1 - .Cell(i, 2).Value) + 3).Value = ttltime2 i = 176 End If End Sub I really appreciate your help with this!!! Les |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Macro
Les, I'm not seeing a parent reference to qualify the ".Cell" object. Looks
like you forgot a "With" statement: With Worksheets("Sheet1") For whatever to blah If something < .Cell Otherstuff End If Next End With -- Charles Chickering "A good example is twice the value of good advice." "WLMPilot" wrote: I am having trouble executing a macro. I get the following error: COMPILE ERROR: Sub or Function not defined (Location of error noted below in 2nd macro.) Can someone tell me what I need to do? Below are two macros I am using with explanation of what I am trying to do. 'The user will click a commandbutton to begin process. Private Sub CommandButton11_Click() Dim TextBox1 As Double 'accepts date mm/dd/yy Dim TextBox2, TextBox3 As Double 'accepts time hh:mm TimeCalcFm.Show End Sub __________________________________________________ ___ This macro shown below is executed when user clicks ENTER button on userform. NOTE: Time entered is based on 24-hr time. User enters Date Worked (textbox1), Time-In (textbox2), and Time-Out(textbox3). Spreadsheet has the date of every Monday (beginning of week) in cells B125 - B176. Columns C-G (for rows 125-176) are Mon, Tue, Wed, Thur, Fri. The purpose of below macro is: 1) Calculate time in hours based on time in/out: Example: In = 8:00 Out = 17:23 (entered by user) ttltime1=out-in (9:23) ttltime2 = HOUR(ttltime1)+(MINUTE(ttltime1)/60) (9.38 hrs) 2) Determine correct cell to place ttltime2 in, ie what day worked that particular week. Based on description of layout above, I want to compare the date entered (datework) against each date in B125:B176. When I find the first occurance of datework being < date in spreadsheet (B125:B176), then I know I need to backup one row and then offset appropriately to place ttltime2 in correct spot. Private Sub CommandButton1_Click() Dim i As Single Dim ttltime1, ttltime2 As Double ttltime1 = TextBox2 - TextBox1 ttltime2 = Hour(ttltime1) + (Minute(ttltime1) / 60) For i = 125 To 176 If TextBox1 < .Cell(i, 2).Value Then <<<< POINT OF ERROR = Cell ' next line figures offset and places ttltime2 in correct cell .Cell(i-1, (TextBox1 - .Cell(i, 2).Value) + 3).Value = ttltime2 i = 176 End If End Sub I really appreciate your help with this!!! Les |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |