Problem with my "If" Statement. (VBA)

[Cuylaerts]

Hello,

i would like my vba to check Dates on my worksheet if they are far away from each other but i seem to get an error in my formula.
this is what it looks like:

If ((Workday(Range("$E$"),1)-Workday(Range("$G$"),1))*Worksheet("ignore").Range("C20"))>Range("$F$") then
msgbox("test")
end if

what this would do is:

substract 2 dates in my worksheet and multiply it by "c20" on my ignore sheet (now i have the time in hours instead of days), then it compares those hours to "Range("$F$")" , if i have more hours then "Range("$F$") then i want a messagebox to appear.

thank you.

 

[mikerickson]

Range("$F$") is missing a row number

 

[Rick Rothstein]

...but i seem to get an error in my formula.
this is what it looks like:

If ((Workday(Range("$E$"),1)-Workday(Range("$G$"),1))*Worksheet("ignore").Range("C20"))>Range("$F$") then
msgbox("test")
end if

Two things...

1) You do not need to use $ signs for the argument to the Range object in VBA (they only apply to formulas on the worksheet).

2) The red highlighted addresses do not have row numbers.

 

[Cuylaerts]

Update:
formula is now:
If ((WorksheetFunction.WorkDay(Range("$E$"), 1) - WorksheetFunction.WorkDay(Range("$G$"), 1)) * ActiveWorkbook.Worksheets("ignore").Range("C20")) > Range("$F$") Then

this removed a error, but stil not working.

 

[Cuylaerts]

Hi Rick,

i deleted the $ signs.
i want the formula to work on the active row.

ty.

 

[Rick Rothstein]

Hi Rick,

i deleted the $ signs.
i want the formula to work on the active row.

Then I would place the row number for the active cell in a variable and use Cells instead of Range to specify the cell references. Assuming your code line is correctly constructed, something like this...

[table="width: 500"]
[tr]
	[td]RowNum = ActiveCell.Row
If ((WorksheetFunction.WorkDay(Cells(RowNum, "E"), 1) - WorksheetFunction.WorkDay(Cells(RowNum, "G"), 1)) * ActiveWorkbook.Worksheets("ignore").Range("C20")) > Cells(RowNum, "F") Then[/td]
[/tr]
[/table]

 

[Cuylaerts]

Worked like a charm!

ty Rick!