PDA

View Full Version : [SOLVED] Error 438



chrisnash70
06-11-2018, 11:48 PM
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
06-12-2018, 12:48 AM
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
06-12-2018, 01:20 AM
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
06-12-2018, 01:29 AM
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
06-12-2018, 01:34 AM
You've changed the criteria for some reason. It should be ">=" and not "=>"

SamT
06-12-2018, 01:47 AM
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
06-12-2018, 02:30 AM
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
06-12-2018, 02:37 AM
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
06-12-2018, 02:45 AM
There are two missing full stops there:


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

Aflatoon
06-12-2018, 02:46 AM
I'd suggest you convert the date values to numbers like this:


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

chrisnash70
06-12-2018, 03:04 AM
Thank you to both of you it now works!!

You are both lovely people!!

chrisnash70
06-12-2018, 03:04 AM
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