Variable for Date not working

p45cal · #16 04-05-2018, 07:41 AM

Give me an example of one cell where the information is incorrect/missing and what it should be?

Divinedar · #17 04-05-2018, 07:45 AM

I'm sorry. I figured just by reading the code it shows that the code is to fill in that particular worksheet.

"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. "

Divinedar · #18 04-05-2018, 07:53 AM

If you take the workbook that you have and change the totals in tabs "FY2019 to FY2023" and then execute the code and check the worksheet "PrjByMth". Let me know what you get.

p45cal · #19 04-05-2018, 08:36 AM

I see, it doesn't update the values. Even asking the sheet to recalculate doesn't update the values.
Ctrl+Alt+F9 on the keyboard seems to update the sheet (whole workbook I think).
Run this to update the sheet:

Code:

Sub ViewPrjByMth()
With Sheets("PrjByMth").Range("B2:BI15")
  .FormulaR1C1 = "=(IF(ISBLANK(RC1),0,(IF(ISBLANK(R1C),0,prjbymonth(RC1,R1C)))))"
  .Value = .Value 'if you disable this line by adding an apostrophe before it, the sheet will have all your formulae back in.
End With
End Sub

The problem lies with the function's arguments on the sheet not referring directly to the data on the FYnnnn sheets; Excel knows to recalculate a formula when any cell in its arguments changes, which is not the case here.
You could, instead of running this code, leave your formulae as they are in the sheets but you'd need to add Application Volatile directly after the Dim statement in the PrjByMonth function's code. This brings in its own set of problems (lots of time recalculating the sheeet unnecessarily which you could control by setting Calculation to manual).

Divinedar · #20 04-05-2018, 08:59 AM

Ok all I’m getting is #VALUE! errors but I notice that on this line:
“If sht.Cells(1, j) = pMonth Then 'This says ROW 1,COLUMNS C THRU N”
The code when debugged never goes pass this line. It won't let me attach a picture of the results.

Divinedar · #21 04-05-2018, 09:12 AM

I really believe that my problem is here "If sht.Cells(i, 2) = strField Then " because if I run error checking I get a #VALUE! I get an error ""=(IF(ISBLANK(RC1),0,(IF(ISBLANK(R1C),0,prjbymonth(RC1,R1C)))))"" here (red text color).

Divinedar · #22 04-05-2018, 09:15 AM

The error evaluation say s=IF(FALSE,#N/A,(IF(FALSE,0,prjbymonth($A2,B$1))))))
These are the results of the error checking.

p45cal · #23 04-05-2018, 09:56 AM

Those last two matter not a jot.
Are you getting the right results?
I am here - as far as I can tell.

If not, supply the workbook on a filesharing site that will allow bigger files, then link to it here.

Divinedar · #24 04-05-2018, 10:17 AM

No I'm still getting #VALUE!. I can't tell what I'm missing unless I'm missing a library or a .dll file.

p45cal · #25 04-05-2018, 10:23 AM

supply the workbook on a filesharing site that will allow bigger files, then link to it here.

p45cal · #26 04-05-2018, 10:36 AM

In the attached I've highlighted cell B10 on sheet PrjByMonth in blue.
This get's its value from cell C6 in sheet FY2019, also highlighted in blue. Change the value of this last cell, go back to sheet PrjByMonth and look at cell B10. No change. Click the button at cell A18. That cell should change.

Does it?

Divinedar · #27 04-05-2018, 11:42 AM

Thank you so much. That worked. It doesn't update automatically when they enter the amounts but I can fix that. Thank you again so much. I can work with this.

Divinedar · #28 04-06-2018, 11:08 AM

Wanted you to see my final code:

HTML Code:

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

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 '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
                        PrjByMonth = PrjByMonth + sht.Cells(i, j)
                        GoTo nxtSht
                    End If
                Next i
            End If
        Next j
    End If
 
nxtSht: Next
 
End Function

Public Sub ViewPrjByMth()

Application.ScreenUpdating = False

Sheets("PrjByMth").Range("B2:BI50").Clear

With Sheets("PrjByMth").Range("B2:BI50")
  .FormulaR1C1 = "=IF(ISBLANK(RC1),0,IF(ISBLANK(R1C),0,prjbymonth(RC1,R1C)))"
  .Value = .Value
  .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
End With
BoldHighHours

Application.ScreenUpdating = True
End Sub
Sub BoldHighHours()

    Application.ScreenUpdating = False
    Dim c As Range

    For Each c In Range("B2:BI50")

        If c.Value >= 10 Then
            c.Font.Bold = True
            c.Interior.ColorIndex = 3
        Else
            c.Font.Bold = False
            c.Interior.ColorIndex = False
        End If

    Next
    Application.ScreenUpdating = True

End Sub

This works perfectly. I have more to do and if you don't mind helping me I would love to reach out to you at anytime? Thank you again

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