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

 

[montypython]

I think at this rate its going to be better to start the sheet from scratch, things were going well and then sheet started going horrible wrong, I really thought I was doing well. It was a table but the desired effect got lost along the way, I just can't have the data capturer changing any formulas thats one of the reasons I also went for pivot slicers. I will get there at my age my brain needs stimulating but today was not a good day for brain stimulation

 

[montypython]

Hi there

Any idea why this is happening, it is not showing the worksheet to run the macro, I have the Column A populating perfectly but when I try and run my next macro to work out the days and populate automatically, this is what pops up?

Cheers

Keith

 

[Anthony47]

I lost you on the previous message: did you decide to restart your workbook from zero or what?

As far as your problem with starting the macro, well it seems there is no any macro available to be started. Which Subs are in your vba project, and in which vba module are they hosted? (the name displayed in the window header, thus "Sheet1(Code)", in the image you attached)

 

[montypython]

I lost you on the previous message: did you decide to restart your workbook from zero or what?

As far as your problem with starting the macro, well it seems there is no any macro available to be started. Which Subs are in your vba project, and in which vba module are they hosted? (the name displayed in the window header, thus "Sheet1(Code)", in the image you attached)

I have been trying over again just to see

 

[Anthony47]

Good to know

 

[montypython]

Good to know

I got the 1st column to give the date automatically, battling a bit with the days outstanding, keep getting

Ambiguous name detected​

 

[montypython]

Bellrossview - Keith verson 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Date Logged onto spreadsheet	Description	Street Address	Suburb	Name	Contact No	Additional info or ref	Date reported to Municpality	Days Outstanding	Status	Comment
2	25/02/2023 09:03	Street Light	20 Bertie Blogs Street	Seaview	Joe Ordinary	083 594 2851	F/9121512	23/02/2022	367
3	25/02/2023 09:03	Pothole	21 Pluto Street	Seaview	Daffy Duck	031 555 5555	F/9121513	01/02/2022	389
4	25/02/2023 09:25	Street Light	61 Fast Car Street	Bellair	Jim Bob	031 123 1234	F/9121514	01/03/2021	726
5	25/02/2023 09:26	Sewerage	12 I don't know Street	Rossburgh	Bobby Burger	087 444 4444	F/9121515	05/07/2019	1331
6	25/02/2023 09:42	Pothole	13 Pothole Street	Bellair	Tina Turner	111 111 1111	F/9121516	22/09/2022	156
7	25/02/2023 10:31	Burst Pipe	9 Downing Steet	Seaview	Bob Geldorf	222 222 2222	F/9121517	21/05/2014	3202
8	25/02/2023 10:31	Street Light	The Whithouse	Bellair	Potis	333 333 3333	F/9121518	31/12/2017	1882
Data Sheet

Cells with Data Validation
Cell	Allow	Criteria
D1:D8	List	='List Sheet'!$A$2:$A$4
B1	List	='List Sheet'!$C$2:$C$5
B2:B1048576	List	='List Sheet'!$C$2:$C$6