Error 438

[chrisnash70]

Sorry I know this been asked before by other users so forgive me but I am getting the same error (Error 438), I am new to VBA so any help would be appreciated:


Sub ADDITUP()
Dim AAdd As Variant
Dim DAdd As Variant
DateRangeStart = InputBox("Please select start date (01/01/2018)")
DaterangeEnd = InputBox("Please select end date (01/01/2018)")
DAdd = Application.WorksheetFunction.SumIfs(Sheet1!AL, Sheet1!AH, ">=" & DateRangeStart, Sheet1!AH, "<=" & DaterangeEnd) <---Error is here
'MsgBox DAdd
'MsgBox DateRangeStart
'MsgBox DaterangeEnd
End Sub

[mancubus]

perhaps related with european data format vs us format in vba.

google search results re this issue:
https://www.google.com.tr/search?rlz=1C1GGRV_enTR751TR751&ei=IG8fW-j6K8PI6AT88LWwBA&q=excel+vba+european+date+format+issues&oq=excel+vba+european+date+format+issues&gs_l=psy-ab.3..33i22i29i30k1.13222.14580.0.15272.6.6.0.0.0.0.289.569.2-2.2.0....0...1c.1.64.psy-ab..4.2.569...33i160k1.0.Swsd2SV5poo

[Aflatoon]

Sheet1!AL and Sheet1!AH are not valid ranges in VBA (or anywhere else). You should use Sheets("Sheet1").Range("AL:AL") for example. Also, if you have Excel 2003 or earlier, there is no SumIfs available.

[chrisnash70]

No that doesn't seem to work:

I've changed the code but now I get the result as zero:


Sub ADDITUP()
Dim AAdd As Variant
Dim DAdd As Variant
Dim aFORange As Range
Dim aFODateRange As Range
Dim DaterangeEnd As Variant
Dim DateRangeStart As Variant
Dim AAdd As Variant
Dim DAdd As Variant
Dim fORange As Range
Dim fODateRange As Range
Dim DaterangeEnd As Variant
Dim DateRangeStart As Variant
Dim ws As Worksheet
Set ws = Sheet1
Set fODateRange = Sheets("Sheet1").Range("AL2:AL17")
Set fORange = Sheets("Sheet1").Range("AH2:AH17")
DateRangeStart = InputBox("Please select start date (01/01/2018)")
DaterangeEnd = InputBox("Please select end date (01/01/2018)")
DAdd = Application.WorksheetFunction.SumIfs(fODateRange, fORange, "=>" & DateRangeStart, fORange, "<=" & DaterangeEnd)
MsgBox DAdd
'MsgBox DateRangeStart
'MsgBox DaterangeEnd
End Sub

[Aflatoon]

You've changed the criteria for some reason. It should be ">=" and not "=>"

[SamT]

AH and AL are not valid range assignments
AH:AH and AL:AL are valid

Lose the spaces, except around the Ampersands

I'm not sure, but try

DAdd = Application.WorksheetFunction.SumIfs(Sheet1!AL:AL, Sheet1!AH:AH,">=" & DateRangeStart, Sheet1!AH:AH."<=" & DaterangeEnd)

Those Ranges might not work because a blank cell is less than DateRangeEnd

See what this does

Sub ADDITUP()
Dim DAdd As Variant
Dim DateRangeStart as Date
Dim DateRangeEnd As Date
Dim SumRange As Range
Dim CritRange As Range

With Sheets("Sheet1")
'AL1 with no headers. Use AL2 with headers
Set SumRange = Range(.Range("AL1"), .Cells(Rows.Count, "AL").End(xlUp))
Set CritRange = SumRange.Offset(0, -4)
End With

'The Returns from InputBoxes are Strings.
'Specifying the Month eliminates International Date Format confusion
'Format the converted Dates from the inputBoxes as desired
DateRangeStart = Format(CDate(InputBox("Please select start date (01 Jan, 2018)")), "dd/mm/yyyy")
DateRangeEnd = Format(CDate(InputBox("Please select end date (Jan 31, 2018)") "dd/mm/yyyy")

DAdd = Application.WorksheetFunction.SumIfs(SumRange, CritRange, ">=" & DateRangeStart, CritRange, "<=" & DaterangeEnd)

'MsgBox DAdd
'MsgBox DateRangeStart
'MsgBox DaterangeEnd
End Sub

Note: if Column AH is formatted As Dates of any display format, you should not need to Format DateRangeStart and DateRangeEnd, only CDate the InputBoxes returns.

[chrisnash70]

Thank you for your help so far SamT I am getting a "1004 error - Method 'ramge' of object'_Worksheet'Failed on line


Set SumRange = Range(.Range("AL2"), .Cells(Rows.Count, "AL").End(xlUp))

[chrisnash70]

You've changed the criteria for some reason. It should be ">=" and not "=>"

Yes that was me playing around with the code to see if made any difference to it. Thanks for pointing it out

[Aflatoon]

There are two missing full stops there:


Set SumRange = .Range(.Range("AL2"), .Cells(.Rows.Count, "AL").End(xlUp))

[Aflatoon]

I'd suggest you convert the date values to numbers like this:


DAdd = Application.WorksheetFunction.SumIfs(fODateRange, fORange, ">=" & CLng(DateRangeStart), fORange, "<=" & Clng(DaterangeEnd))

[chrisnash70]

Thank you to both of you it now works!!

You are both lovely people!!

[chrisnash70]

Completed Code:


Sub ADDITUP()
Dim DAdd As Variant
Dim DateRangeStart As Date
Dim DateRangeEnd As Date
Dim SumRange As Range
Dim CritRange As Range


With Sheets("Sheet1")
'AL1 with no headers. Use AL2 with headers
Set SumRange = .Range(.Range("AL2"), .Cells(.Rows.Count, "AL").End(xlUp))
Set CritRange = SumRange.Offset(0, -4)
End With


'The Returns from InputBoxes are Strings.
'Specifying the Month eliminates International Date Format confusion
'Format the converted Dates from the inputBoxes as desired
DateRangeStart = CDate(InputBox("Please select start date (01 Jan, 2018)"))
DateRangeEnd = CDate(InputBox("Please select end date (Jan 31, 2018)"))




DAdd = Application.WorksheetFunction.SumIfs(SumRange, CritRange, ">=" & CLng(DateRangeStart), CritRange, "<=" & CLng(DateRangeEnd))


MsgBox DAdd
'MsgBox DateRangeStart
'MsgBox DaterangeEnd
End Sub