Variable for Date not working

Divinedar · #1 04-03-2018, 08:24 AM

I have the following codes

HTML Code:

Function PrjByMonth(strField As String, pMonth As Date)
 
Dim sht As Worksheet, i As Long, j As Integer

PrjByMonth = 0
 
 
For Each sht In ActiveWorkbook.Sheets
    If Mid(sht.Name, 1, 2) = "FY" Then
        For j = 3 To 14 'The row of dates. Starts at C1 to N1
        
            If sht.Cells(1, j) = pMonth Then
                For i = 2 To 30 'update with max row
                    If sht.Cells(i, 2) = strField Then
                        PrjByMonth = PrjByMonth + CDbl(sht.Cells(i, j))
                        GoTo nxtSht
                    End If
                Next i
            End If
        Next j
    End If
 
nxtSht: Next
 
End Function

Public Sub ViewPrjByMth()
Dim strField As String, pMonth As Date
Worksheets("PrjByMth").Visible = True
Sheets("PrjByMth").Activate
PrjByMonth strField, pMonth
End Sub

I attached a picture of the results that I'm getting that I just can't figure out why this line "If sht.Cells(1, j) = pMonth Then" is not giving me the correct information. Please help.

NoSparks · #2 04-03-2018, 09:44 AM

You're showing us code that doesn't do what you desire.
Can you tell us in words what you're trying to achieve with this ?

Divinedar · #3 04-03-2018, 01:21 PM

Ok so in a workbook I have other worksheets. I'm trying consolidate them into one worksheet. When reading the code I need to do as follows:

For j = 3 To 14 'The row of dates. Starts at C1 to N1

If sht.Cells(1, j) = pMonth Then 'This says ROW 1,COLUMNS C THRU N
For i = 2 To 30 'This says ROWS 2 THRU 30
If sht.Cells(i, 2) = strField Then ' This is ROWS 2 THRU 30, COLUMN B

Refers to each worksheet (5 years (1 year each worksheet) within the workbook. I can't use consolidation because they are not exactly the same. The list is different on each worksheet in column 2 and the years are different on each worksheet (1 year on each worksheet for up to 5 years) so 5 worksheets to consolidate. So I have a sheet that I consolidated all the countries (column 2 of worksheets) into one worksheet.

What I need the code to do is to collect each year total for each country from each worksheet into one worksheet.

I hope you understand.

Divinedar · #4 04-03-2018, 01:23 PM

I believe that my "dMonth" is not being recognized. This part sht.Cells(1, j) gives me a date but the "dMonth" only gives me a time. So it's not matching. It seems like when I make a sudden move on something it works but if I run the macro for the function it doesn't working within the worksheet.

NoSparks · #5 04-03-2018, 08:24 PM

Think you need to attach a sample workbook indicative of what you're working with and what your goal is.

p45cal · #6 04-04-2018, 01:16 AM

In the code which calls PrjByMonth, you're Dimming pMonth, then using it in the arguments without having assigned any value to it. Since it's Dimmed as a date its starting value is 0, or midnight on 1/1/1900(?).
[edit post posting: it seems if pMonth is assigned a value less than 1, only a time element is assigned to it.]
You need to assign something to pMonth before calling the function.

Even if you have a global variable pMonth, this version of pMonth will be local; if you want a global version of pMonth to be used, remove the Dim statement for it in ViewPrjByMth.

BTW, the same applies to strField.

Attaching a workbook would be good.

Divinedar · #7 04-04-2018, 04:21 AM

Ok I attached a workbook. I also made the variable global and it still didn't work. I had to zip the file. Waiting on your responses. thank you so much.

p45cal · #8 04-04-2018, 06:48 AM

The function seems to work fine when called from a worksheet; the arguments are supplied.
When you call it from another macro you need to supply valid arguments too.
Try the likes of:

Code:

Public Sub ViewPrjByMth()
Dim strField As String, pMonth As Date, amt
pMonth = DateValue("1-Feb-2019")
strField = "Army"
'Worksheets("PrjByMth").Visible = True
'Sheets("PrjByMth").Activate
amt = PrjByMonth(strField, pMonth)
MsgBox amt & ", " & strField & ", " & pMonth
End Sub

The variables don't need to be global.

Divinedar · #9 04-04-2018, 07:05 AM

I get this error "Wrong number of arguments (Error 450)" on this line "amt = PrjByMonth(strField, pMonth)"

p45cal · #10 04-04-2018, 10:39 AM

Have you changed this line at all?:
Function PrjByMonth(strField As String, pMonth As Date)
(just 2 arguments (strField and pMonth))

Divinedar · #11 04-05-2018, 04:22 AM

If you look at the code that is what I have

PrjByMonth strField, pMonth
PrjByMonth(strField As String, pMonth As Date)

I just can't get pMonth to recognize the date in the headers of the pages. It only gives me a time and not a date.

I'm going to rewrite and try and see where I went wrong. If you have any clues please let me know. Check in later. Thank you.

p45cal · #12 04-05-2018, 06:59 AM

Attached, a slimmed down version of your file.
No errors.

Divinedar · #13 04-05-2018, 07:13 AM

Ok I understand with the exception of 2 things

Public Sub ViewPrjByMth()
Dim strField As String, pMonth As Date, amt
pMonth = DateValue("1-Mar-2019")
strField = "Army"
amt = PrjByMonth(strField, pMonth)
MsgBox amt & ", " & strField & ", " & pMonth
End Sub

This part "pMonth = DateValue("1-Mar-2019")" only searches for that one particular date, I need it to do all the dates

It's not filling in the table it only gives me a message of what it finds, I need it to fill in the tab "PrjByMth" with the information in the tabs FY2019 thru FY2013.

I don't need the message box.

p45cal · #14 04-05-2018, 07:19 AM

What's wrong with the worksheet formulae you're currently using?

NoSparks · #15 04-05-2018, 07:21 AM

Quote:

It's not filling in the table ... I need it to fill in the tab "PrjByMth" with the information in the tabs FY2019 thru FY2013.

and that's what the answer to post #2 should have been.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
30+ days Variable Day Date Calculations via Fields	ztag	Word	2	01-06-2012 11:12 AM