I am having trouble pulling a value in a cell into a macro for calculations
The macro is below. The is executed when ENTER is clicked on userform.
It is suppose to pull a date in from the spreadsheet (B125:B176) and
evaluate it against date entered in userform.

Private Sub CommandButton1_Click()
Dim i As Double
Dim date1 As Date
Dim ttltime1, ttltime2 As Double
ttltime1 = Val(TextBox2) - Val(TextBox1)
ttltime2 = Hour(ttltime1) + (Minute(ttltime1) / 60)
With Worksheets("PAS")
For i = 125 To 176
date1 = .Cell(i, 2).Value <<< Problem code
If TextBox1 <= date1 Then
.Cell(i, (TextBox1 - date1) + 3).Value = ttltime2
i = 176
End If
Next
End With
End Sub

Thanks,
Les

Without looking at anything else, it's:

..cells(i, ...
(note the S at the end of Cells)

WLMPilot wrote:

I am having trouble pulling a value in a cell into a macro for calculations
The macro is below. The is executed when ENTER is clicked on userform.
It is suppose to pull a date in from the spreadsheet (B125:B176) and
evaluate it against date entered in userform.

Private Sub CommandButton1_Click()
Dim i As Double
Dim date1 As Date
Dim ttltime1, ttltime2 As Double
ttltime1 = Val(TextBox2) - Val(TextBox1)
ttltime2 = Hour(ttltime1) + (Minute(ttltime1) / 60)
With Worksheets("PAS")
For i = 125 To 176
date1 = .Cell(i, 2).Value <<< Problem code
If TextBox1 <= date1 Then
.Cell(i, (TextBox1 - date1) + 3).Value = ttltime2
i = 176
End If
Next
End With
End Sub

Thanks,
Les

--

Dave Peterson

Did you read my response in your other post here "Insert date in correct
cell"? I evaluated the date entered in the userform as to what day of the
week it is. With that I can use math to obtain the date of Monday for that
week. I can now use Cells.Find to find that date on the worksheet, and put
the hours calculation in the correct Offset cell because I already know what
day of the week the date entered is.

I offered to email you my test copy.
Mike F
"WLMPilot" wrote in message
...
I am having trouble pulling a value in a cell into a macro for calculations
The macro is below. The is executed when ENTER is clicked on userform.
It is suppose to pull a date in from the spreadsheet (B125:B176) and
evaluate it against date entered in userform.

Private Sub CommandButton1_Click()
Dim i As Double
Dim date1 As Date
Dim ttltime1, ttltime2 As Double
ttltime1 = Val(TextBox2) - Val(TextBox1)
ttltime2 = Hour(ttltime1) + (Minute(ttltime1) / 60)
With Worksheets("PAS")
For i = 125 To 176
date1 = .Cell(i, 2).Value <<< Problem code
If TextBox1 <= date1 Then
.Cell(i, (TextBox1 - date1) + 3).Value = ttltime2
i = 176
End If
Next
End With
End Sub

Thanks,
Les