#16
|
||||
|
||||
Give me an example of one cell where the information is incorrect/missing and what it should be?
|
#17
|
|||
|
|||
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. " |
#18
|
|||
|
|||
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.
|
#19
|
||||
|
||||
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 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). |
#20
|
|||
|
|||
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. |
#21
|
|||
|
|||
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).
|
#22
|
|||
|
|||
The error evaluation say s=IF(FALSE,#N/A,(IF(FALSE,0,prjbymonth($A2,B$1))))))
These are the results of the error checking. |
#23
|
||||
|
||||
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. |
#24
|
|||
|
|||
No I'm still getting #VALUE!. I can't tell what I'm missing unless I'm missing a library or a .dll file.
|
#25
|
||||
|
||||
supply the workbook on a filesharing site that will allow bigger files, then link to it here.
|
#26
|
||||
|
||||
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? |
#27
|
|||
|
|||
Variable for Date not working
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.
|
#28
|
|||
|
|||
Variable for Date not working
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 |
|
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 |