VBA Userform for Paychecks -- ForEach/Next issue?

[katk]

Hi all,

I'm trying to set up a userform that calculates paychecks based on information already provided on sheet "Hours" regarding hours worked and wage rate. Column A contains the names -- that's what mrng refers to. After the user selects a name from a combobox on the form and enters a date range, the code is supposed to find each combobox match in column A with a date (column B) that's inside the range, and then add the number of hours worked (column H) to the gross total, gtot, multiplying by the wage rate (Column C).

The code runs through without an error, but gtot always ends up 0 in my tests.

Partial code:

Dim dte1 As Date
Dim dte2 As Date
Dim mrng As Range
Dim prng As Range
Dim wrng As Range
Dim gtot As Single [COLOR=#ff0000]'gross total[/COLOR]
dte1 = DateValue(CheckForm.DateBox1) [COLOR=#ff0000]'CheckForm = userform, DateBox1 & 2 = textboxes w/ 9/11/12 style dates[/COLOR]
dte2 = DateValue(CheckForm.DateBox2) [COLOR=#ff0000]'I've tried this without the datevalue too[/COLOR]
[COLOR=#ff0000]'Add up hours[/COLOR]
For Each mrng In Sheets("Hours").Range("A" & Rows.Count).End(xlUp)
        If mrng.Value = CheckForm.cbMoverList.Value Then [COLOR=#ff0000]'cbMoverList is combobox[/COLOR]
            If dte2 >= mrng.Offset(0, 1) >= dte1 Then [COLOR=#ff0000]'Problem might be here -- tried putting DateValue around middle value, to no avail[/COLOR]
                [COLOR=#ff0000]'payrange in column H -- tried the following with and without adding ".value" after offset[/COLOR]
                prng = mrng.Offset(0, 7)
                [COLOR=#ff0000]'wagerange in column C[/COLOR]
                wrng = mrng.Offset(0, 2)
                [COLOR=#ff0000]'gross total = (gross total + payrange) * wagerate [/COLOR]
                gtot = (gtot + prng.Value) * wrng.Value
            End If
        End If
Next mrng

I've tried googling the problem several different ways, but even when I found similar issues, I couldn't identify the problem. If anyone can see what's wrong, I'd be really appreciative. I've been teaching myself coding and I've got a lot of the basics, but without knowing all the nuances it can be hard to troubleshoot.

Additionally, it occurred to me that I could complete this part of the code (which ultimately contains a lot of calculations, hence why I'm not just using formulas and saving myself a headache) using some sort of vba filtering -- but I wouldn't be sure how to filter a date range.

Anyway, any suggestions help.

Thanks,
Katherine

 

[MickG]

Try changing the these 4 lines of code, Its a start there may be more !!!.

For Each mrng In Sheets("Hours").Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
If dte2 >= mrng.Offset(0, 1) And mrng.Offset(0, 1) >= dte1 Then
Set prng = mrng.Offset(0, 7)
Set wrng = mrng.Offset(0, 2)

 

[katk]

Thanks, Mick. I try to check over and over for stupid errors before I ask for help, but... sometimes they slip through. The range was the primary problem... I haven't had to use xlUp in a while and I forgot how to format it. Thanks again!

 

[MickG]

Your welcome
Regrds Mick