Assistance needed - Please be gentle

[montypython]

Hi Some advice needed

Is there wa way to optimise these macro's I am still learning so please bare with me

Dim dateRange As Range
Set dateRange = Range("A2:H" & Cells(Rows.Count, "H").End(xlUp).Row)

If Application.CountIf(dateRange, "") = 0 And Application.CountIf(dateRange, "=ISNUMBER(value)") = dateRange.Cells.Count Then
Range("I2:I" & lastRow).FormulaR1C1 = "=INT(RC[-7]-RC[-2])"
End If



Sub FormatColumnI()
Dim lastRow As Long

' Determine the last row of data
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Set the number format for cells in Column I
Range("I2:I" & lastRow).NumberFormat = "0"
End Sub

Sub CalculateDaysDifference()
Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Get last row in column A

For i = 1 To lastRow 'Loop through each row in column A
If IsDate(Cells(i, "A").Value) And IsDate(Cells(i, "H").Value) Then 'Check if both values in columns A and H are dates
Cells(i, "I").Value = DateDiff("d", Cells(i, "A").Value, Cells(i, "H").Value) 'Calculate the number of days between the two dates and put the result in column I
End If
Next i
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I:I")) Is Nothing Then
Call CalculateDaysDifference
End If
End Sub

on the last one I am getting a compile error, I need it to run the macro live when updating in my worksheet or perhaps a suggestion for another solution


Thanks Keith

 

[Anthony47]

Which is the complete Error message? Is there any line of code highlightedt? Is Sub CalculateDaysDifference inserted in a "Standard vba module"? Try moving it (Sub CalculateDaysDifference) to the same module where Private Sub Worksheet_Change is inserted

 

[montypython]

Which is the complete Error message? Is there any line of code highlightedt? Is Sub CalculateDaysDifference inserted in a "Standard vba module"? Try moving it (Sub CalculateDaysDifference) to the same module where Private Sub Worksheet_Change is inserted

Hi there here is one error message that popped up

 

[Anthony47]

The 5 lines on top of the module cannot be there, need to be removed

 

[montypython]

The 5 lines on top of the module cannot be there, need to be removed

Hi

it turned out that there were a few more lines of other code I had to delete, unfortunately it hasn't solved my problem of making the macro run live instead of refeshing it all the time, but that you for you advice and assitance.

 

[Anthony47]

The Sub Worksheet_Change is associated with a specific sheet (Sheet2, I seem); whenever you change a cell value in Column I of that sheet the macro starts. But I see that CalculateDaysDifference itself modifies column I, thus resulting into an endless loop and possible excel crash...
Modify the Sub Worksheet_Change as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
'Stop
If Not Intersect(Target, Range("I:I")) Is Nothing Then
    Application.EnableEvents = False
    Call CalculateDaysDifference
    Application.EnableEvents = True
End If
End Sub

(the added lines will prevent endless loop)

Save the file and close Excel; then restart Excel, reopen the file and test for its behaviour.
If it still don't behave as you expect:
-remove that apostrophe in front of the Stop instruction
-try your operation and verify that the macro starts and halt on that Stop (the line will be highlighted)

If the line is highligted then restore the apostrophe (this will put the instruction "offline") and complete the macro by hitting F5
-describe how you operate on the workbook, what you do, what you expect should be done by the macro and what happens instead

If the Stop is not highlighted then make sure that the Sub Worksheet_Change is associated with the right worksheet: from Excel, rightclick on the tab with the name of the sheet, select Display code, check that your code is in the vba module that get opened with the selection.

 

[montypython]

The Sub Worksheet_Change is associated with a specific sheet (Sheet2, I seem); whenever you change a cell value in Column I of that sheet the macro starts. But I see that CalculateDaysDifference itself modifies column I, thus resulting into an endless loop and possible excel crash...
Modify the Sub Worksheet_Change as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
'Stop
If Not Intersect(Target, Range("I:I")) Is Nothing Then
    Application.EnableEvents = False
    Call CalculateDaysDifference
    Application.EnableEvents = True
End If
End Sub

(the added lines will prevent endless loop)

Save the file and close Excel; then restart Excel, reopen the file and test for its behaviour.
If it still don't behave as you expect:
-remove that apostrophe in front of the Stop instruction
-try your operation and verify that the macro starts and halt on that Stop (the line will be highlighted)

If the line is highligted then restore the apostrophe (this will put the instruction "offline") and complete the macro by hitting F5
-describe how you operate on the workbook, what you do, what you expect should be done by the macro and what happens instead

If the Stop is not highlighted then make sure that the Sub Worksheet_Change is associated with the right worksheet: from Excel, rightclick on the tab with the name of the sheet, select Display code, check that your code is in the vba module that get opened with the selection.

Hi there

Not sure what I am doing wrong, I have taken all the code out and now going to start from scratch, this is a fault logging sheet for say jobs that have not been done in our municpality and give a report back with various filters which have already been added by pivot slicers

So the capturer will be entering all the info

1. The capturer logging date with be capture in column A, and would like it automatically populate when data is filled in column B
2. Column H would be the date the fault was actually logged and column I should in theory populate with number of days the job is still outstanding, doing this without refreshing the macros all the time, the aim of this is not to allow the capturer to fiddle with any settings if that makes sense

 

[Anthony47]

I seem that my technical information cannot be decoded (nor I am able to decode yours: for example what is a "capturer")
If you still need some help then let's start from zero:
-try to explain which is the layout of your data; which are the information that someone type in; which are the information you would like to fill automatically, and when that should occour. You might support the description using at least a "picture" of your sheet /sheets, but using the XL2BB addin whould be better and sharing a sample file would be much much better (but I understand this is sometime impossible)

 

[montypython]

Hi there please see mini board

Charlene.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date Logged	Description	Street Address	Suburb	Name	Contact No	Additional info or ref	Date reported	Days Outstanding	Status	Reason
2	22/05/2022	Street Light	14 Bertie Blog Street	Seaview	Joe Blog	1234	11111111111	01/01/23	224	Open	unclear
3	31/03/2022	Burst Pipe	15 Picadilly Circus	Bellair	Count Dracula	5678	22222222222	02/01/23	277	Open	unclear
4	22/02/2023	Burst Pipe	12 Cat Alley Street	Rossburgh	Snoopy	91011	33333333333	03/01/23	-50	Closed	unclear
5	22/02/2023	Pothole	12 Blue Marlin Street	Seaview	Smokey Joe	90210	4444444444	04/01/23	-49	Closed	unclear
6	22/02/2023	Pothole	5 Shark Street	Bellair	Cheeky Chops	4096	5555555555	04/01/23	-49	Closed	unclear
7	22/02/2023	Street Light	10 beach road	Seaview	Desperado	4001	66666666666	05/01/23	-48	Open	unclear
8	25/03/2022	Street Light	15 Wilmslow Drive	Seaview	Donald Duck	5901	77777777777	05/02/70	-19041	Open	Closed
9	23/02/2023	Street Light	15 Bridlington	Seaview	Daffy duck	10111	8888888888	20/03/22	-340	Closed	unclear
10	29/02/2023	Street Light	21 Medway	Seaview
Data

Cells with Data Validation
Cell	Allow	Criteria
D1:D10	List	='Data lists'!$A$2:$A$4
J1:J10	List	='Data lists'!$B$2:$B$3
B1:B10	List	='Data lists'!$C$2:$C$4

Hope this makes more sense, the list sheet is hidden, that is where I have my drop down data

So the person capturing the data would call a reason from the fist drop down list, as the reason is entered the the current date should auto populate, the rest of the info is pretty straight forward, up until the "date reported" in column H, In column I, it should autmatically work out the number of days since first reported in column H and date captured in Column A. This should automatically happen when the date in column H is entered.

 

[Anthony47]

Wouldn't be sufficient inserting a formula into column I?
For example, if that is a table named Table1, in I2:

'=IF([@[Date Logged]]<>"",[@[Date reported]]-[@[Date Logged]],"")

If you clear I3:Ixx (to bottom of table) then the formula should automatically propagate till the last row

If tat is not a table I recommand to transform it to a table:
Select A1:Kxx (ie till the last populated row, end even some spare rows), Menu /Insert /Table; check "Table with headers", Ok
Now you may clear the data (not the header) in column I and insert the formula I suggested

The table extends automatically when you hit Tab from the last cell in table, or when you drag the "handle" (in the red square; see the image) down, and the formula will propagate on the new rows.

If you cannot use a Table then the formula in I2 should be

=IF(A2<>"",H2-A2,"")

Need to be manually copied down

If you cannot use a formula and still prefer a macro then tell me if the range is or can be converted to a Table and we shall try to write a Worksheet_Change macro that do the job