#1
|
|||
|
|||
Variable for Date not working
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 |
#2
|
|||
|
|||
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 ? |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
Think you need to attach a sample workbook indicative of what you're working with and what your goal is.
|
#6
|
||||
|
||||
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. |
#7
|
|||
|
|||
Variable for Date not working
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.
|
#8
|
||||
|
||||
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 |
#9
|
|||
|
|||
I get this error "Wrong number of arguments (Error 450)" on this line "amt = PrjByMonth(strField, pMonth)"
|
#10
|
||||
|
||||
Have you changed this line at all?:
Function PrjByMonth(strField As String, pMonth As Date) (just 2 arguments (strField and pMonth)) |
#11
|
|||
|
|||
Variable for Date not working
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. |
#12
|
||||
|
||||
Attached, a slimmed down version of your file.
No errors. |
#13
|
|||
|
|||
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. |
#14
|
||||
|
||||
What's wrong with the worksheet formulae you're currently using?
|
#15
|
|||
|
|||
Quote:
|
|
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 |