Type Mismatch

[WLMPilot]

Below is the code I am trying to use, but I keep getting a type mismatch
(Runtime Error 13) on the line indicated with "ERROR". The value I am
pulling into date2 is formatted as DATE within the spreadsheet.

What do I need to change?

'Display Estimated Pay on Part-time jobs for current payperiod
Private Sub CommandButton6_Click()
Dim date2, date3 As Date
Dim k, startrow, endrow, gotbrs As Integer
Dim brs, pas As Variant
Dim rng As Range
date3 = Date
Set rng = Worksheets("PT").Range("J8:J58")
startrow = 8
endrow = 58
Loop1:
For k = startrow To endrow Step 2
date2 = rng.Offset(0, 0).Value <=====ERROR
'MsgBox date2
If date3 <= date2 Then
If endrow <= 58 Then
brs = rng.Offset(k - 1, 3).Value
k = endrow
gotbrs = 1
startrow = 67
endrow = 117
rng = Range("J67:J117")
Else
pas = rng.Offset(k - 1, 3).Value
Set k = endrow
End If
End If
Next k
If gotbrs = 1 Then
gotbrs = 0
GoTo Loop1
End If
MsgBox "BRS: " & " brs" & " PAS: " & pas & " TOTAL: " & (brs + pas)
End Sub

Thanks,
Les

 

[Don Guillett]

Might be best to tell us what you are trying to do.

 

[WLMPilot]

I was able to fix the problem via trial and error. Thanks anyway.

Les

 

[Don Guillett]

The point is that there might be a better way.

 

[WLMPilot]

Well, I will lay out the chart for you and see what you come up with. Chart
is simply a spreadsheet, not a true CHART that is a diagram based on data.

General Description: Chart is 52 rows (ie 52 weeks) divided into two week
payperiods. Payday (date) starts in J8 and increase by 14 days every two
rows, ie J10 = J8+14, J12 = J10+14, etc. I have two charts, one for each
parttime job, therefore you will notice the startrow & endrow numbers change.


Basically, I click a command button that will execute the code to find the
pay for each job and display it along with a total.

Payday (date) range:
Job1 = J8:J58 (every other row)
Job2 = J67:J117 (every other row)

I have place the current code that works below, which is a little different
from first code I posted.

'Display Estimated Pay on Part-time jobs for current payperiod
Private Sub CommandButton6_Click()
Dim date2, date3 As Date
Dim k, startrow, endrow, gotbrs, ans As Integer
Dim brs, pas As Single
Const sfmt As String = "$##0.00"
'Const dfmt As Date = "##/##/##"
Dim answer, brsdate, pasdate As String
date3 = Date
startrow = 8
endrow = 58
Loop1:
For k = startrow To endrow Step 2
date2 = Cells(k, 10).Value
If date3 <= date2 Then
If endrow <= 58 Then
brs = Cells(k - 1, 13).Value
brsdate = date2
MsgBox brsdate
k = endrow
gotbrs = 1
startrow = 67
endrow = 117
Else
pas = Cells(k - 1, 13).Value
pasdate = date2
MsgBox Len(pasdate)
k = endrow
End If
End If
Next k
If gotbrs = 1 Then
gotbrs = 0
GoTo Loop1
End If
answer = " Belton (" & FormatDateTime(brsdate, vbShortDate) &
"): " &
Format(brs, sfmt) & _
vbLf & " Palmetto (" & FormatDateTime(pasdate, vbShortDate) & "):
" &
Format(pas, sfmt) & _
vbLf & " Total: " & Format(brs + pas, sfmt)
ans = MsgBox(answer, vbOKOnly, "Current Payperiod - Estimated Pay")
End Sub

Thanks for your time is looking at this.

Les

 

[WLMPilot]

I forgot to tell you one thing. Once I determine where to locate the amount
for the payperiod, the estimated pay is placed in brs and pas.

Les