VBA Sumifs Help

[rockchalk33]

Sub text2()
Dim userDate As String
Sheets("Sheet11").Select
Range("Z1").Select


userDate = InputBox("Please type a date to search in MM/DD/YYYY format")
If userDate = vbNullString Then Exit Sub


Sheet11.Range("Z1").Value = Application.WorksheetFunction.SumIfs(Range("O:O"), Range("B:B"), userDate, Range("A:A"), "Shop")




End Sub

When this is manually entered using the Excel window, I am obtaining the correct answer...below is the Excel forumula...in this particular example I am using 1/2/2016 as the example for the userDate:

=SUMIFS(O:O,B:B,"1/2/2016",A:A,"Shop")

When the VBA code is ran, there is no value displayed in cell Z1.....can someone please help me with this!

 

[tygrrboi]

are your dates in column B formatted as text or as dates? If they're dates, they will not be able to match a string as the actual value of a date is a decimal...

Perhaps convert your userDate string into a date before using it in the sumifs function?

 

[rockchalk33]

are your dates in column B formatted as text or as dates? If they're dates, they will not be able to match a string as the actual value of a date is a decimal...

Perhaps convert your userDate string into a date before using it in the sumifs function?

Sub text2()
Dim userDate As Date
Sheets("Sheet11").Select
Range("Z1").Select


userDate = InputBox("Please type a date to search in MM/DD/YYYY format")
If userDate = vbNullDate Then Exit Sub


Sheet11.Range("Z1").Value = Application.WorksheetFunction.SumIfs(Range("O:O"), Range("A:A"), "Shop", Range("B:B"), userDate)




End Sub

I tried the above code and still did not receive an answer. The values in Column B are in the "m/d/yyyy" format. I tried entering both "01/02/2016" as well as "1/2/2016" into the inputbox and neither returned an answer.

What's really throwing me off is if I leave out the input box and rather search exactly for "1/2/2016" I still don't receive an answer:

Sub text2()
'Dim userDate As Date
Sheets("Sheet11").Select
Range("Z1").Select


'userDate = InputBox("Please type a date to search in MM/DD/YYYY format")
'If userDate = vbNullDate Then Exit Sub


Sheet11.Range("Z1").Value = Application.WorksheetFunction.SumIfs(Range("O:O"), Range("A:A"), "Shop", Range("B:B"), "1/2/2016")




End Sub

 

[tygrrboi]

So you mean your values in column B are SHOWN as m/d/yyyy.... but they're probably FORMATTED as a date... To see what I mean, select any of your dates in column B and change the number formatting from Date to "General" You will see a strange number.

Dates in excel are not strings of text that can be compared to a user-input string....


Try this.

Sub text2()
Dim userDate As String
Sheets("Sheet11").Select
Range("Z1").Select


userDate = InputBox("Please type a date to search in MM/DD/YYYY format")
If userDate = vbNullString Then Exit Sub


Sheet11.Range("Z1").Value = Application.WorksheetFunction.SumIfs(Range("O:O"), Range("B:B"), [COLOR=#ff0000]CDate(userDate)[/COLOR], Range("A:A"), "Shop")




End Sub

 

[rockchalk33]

So you mean your values in column B are SHOWN as m/d/yyyy.... but they're probably FORMATTED as a date... To see what I mean, select any of your dates in column B and change the number formatting from Date to "General" You will see a strange number.

Dates in excel are not strings of text that can be compared to a user-input string....


Try this.

Sub text2()
Dim userDate As String
Sheets("Sheet11").Select
Range("Z1").Select


userDate = InputBox("Please type a date to search in MM/DD/YYYY format")
If userDate = vbNullString Then Exit Sub


Sheet11.Range("Z1").Value = Application.WorksheetFunction.SumIfs(Range("O:O"), Range("B:B"), [COLOR=#ff0000]CDate(userDate)[/COLOR], Range("A:A"), "Shop")




End Sub

Column B is formatted as a Date and not "General"....Hmmmmmm....Put exactly what you had posted and still striking out.

 

[RoryA]

Are you saying you don't see anything at all in Z1 not even a 0 value?

 

[rockchalk33]

Are you saying you don't see anything at all in Z1 not even a 0 value?

Correct...totallyblank

 

[RoryA]

Assuming your settings are not set to suppress the display of zero values, there should be a value of some sort in the cell. Are you sure you're looking at the correct sheet? The code name Sheet11 does not necessarily refer to a sheet whose tab says Sheet11.

 

[tygrrboi]

Are you running the macro? Just because the macro exists does not mean the cell will automatically be updated. You need to run it from the macro window or create a button and assign the macro to that button.