Time Value Adjustment

[kanelonis]

Hi to all
I would like to kindly ask for your help.

I WANT TO FILTER TIME FROM 1:00 TO 24:00 AND WHEN A TIME VALUE IS MISSING
I WANT TO INSERT A ROW IN THE MISSING TIME VALUE

see attachment example

[Alf]

Perhaps this macro could be of help? Based on your uploaded file it checks the range in the B column and inserts rows starting by checking the last row and if this value is not 2400 will insert 2400 on the row below. It then checks if cell B3 is 100 before it starts checking the all the other rows in the B column.

Please Login or Register  to view this content.

Alf

Ps When posting don't use capital letter as this is considered as shouting a thing to avoid.

[kanelonis]

Sub time()
Option Explicit

Sub InsertRow()
Dim j As Integer
Dim cell As Range

j = Cells(Rows.Count, 2).End(xlUp).Row

If Cells(j, 2).Value < 2400 Then
Cells(j + 1, 2).Value = 2400
End If

If Range("B3").Value <> 100 Then
Range("B3").EntireRow.Insert
Range("B3").Value = 100
End If

j = 100

For Each cell In Range("B3:B" & Range("B" & Rows.Count).End(xlUp).Row)

If cell.Value = j Then

Else

cell.EntireRow.Insert
cell.Offset(-1, 0).Value = j

End If

j = j + 100

If j = 2500 Then
j = 100
End If
End Sub


Dear Alf thank you for your help
Please excuse my noobness i tried to run the macro but it shows "compile error invalid inside procedure"

I have the time values in "E" or "B" as the attached file example and i want to fill the missing time values from 1:00 to 24:00 because i have noticed that a lot among those 57130 values are missing so i want to be able to insert an empty row for every missing value in order to bring the time balance in the 1:00 to 24:00. so if between 1and24, 4 and 17 are missing i want to add it as an empty row so it wont ruin the balance between the second row values (see attachment).

I apologize for the caps but i am a noob in the forum logic and in excel

Please help this could save me a lot of tyring time for my eyes to spot the missing 57130 time values

[kanelonis]

Dear Alf
i tried to run the macro but it wont work

[Alf]

Relax this is not your fault but mine. Have rewritten the code a bit.

As you have quite a number of rows starting at 55990 going to 57094 where all the values are 2400. Can these rows be deleted or do you wish insert new rows between all the 2400 rows?

Tested code and it seems to run ok but take quite a long time 10 - 15 minutes so I'm not so sure this is the right way to solve your problem

To test run macro "InsertRow" on uploaded file.

Alf

[kanelonis]

i have measurements from 2001 to 2009 in one excel thats why the 50.000 something values.
i want to take for spesific months of every year the 24h values i have the thing is tha i noticed that some time values are missing so if i could add a blanc row for every missing time valuse then its balanced again.
could you help me please?

[Alf]

Hi

I think you must split the values in smaler portions. Running your original file I ran out of rows in the spreadsheet. Do you thin you could upload a file where you had two columns for each year 2001 to 2009?

Alf

[kanelonis]

yes of course i can split the years ok i shall upload a new excel
I hope this will work thank you Alf

[kanelonis]

Dear Alf i have attached the new excel in the first post of mine
with blue color columns are the values that i have for each hour so i tried to put the24h values from each month and i noticed that some hours were missing and if i would add an entire horizontal row then its back to normal as the first example

[Alf]

I'm sorry but the problem was with you data and my macro. You have a blank row at 8901 and another at row 9027. As my macro expect values in the B column these two missing values make the macro "stumble" and fall flat on its feet.

I've edited your original file and have tried to upload it but the system seems to have problems with that.

If you take the file I downloaded and remove row 8901 and row 9026 (both should be empty line) then the macro will work.

Alf

[kanelonis]

Sorry Alf but i dont understand to which file that i send you are you referring to with those rows?
because i could not find any blank rows .Also could you please upload again the marco ?
yes it seems that you can upload up to 1mb only

[Alf]

Still problems uploading with the forum. Here comes the macro:

Please Login or Register  to view this content.

If you check the file you uploaded "TIME VALUE ADJUSTMENT.xlxs" you will find two empty rows if you go to A8937 and A9063. These rows must be delited before you run the macro

Alf

[kanelonis]

does it still take long time as you mentioned above to process the data or its was due to the blank cells?
could you send it to me via email?
chokomike"at"rocketmail.com

[kanelonis]

i run the macro but it disappears the other values .it does add the missing time valluse but i lose the values next to time

[Alf]

but i lose the values next to time

That is the effect of inserting an empty row i.e. if a time value is missing macro inserts a new row with the missing time value in the B column the you get an empty cell in the C column.

At the end where you have 104 rows all having the value of 2400 the macro then inserts a total of 23 rows where there will be no values in the corresponding c column for each 2400 value.

Alf

Ps Also mailed the fixed file (deleted the 2 rows and added macro)

[kanelonis]

Sorry for not replying to the post earlier but i was off with work.

Thank you very much Alf for your non-stop support and help with my problem.
I really can not find words to express my thanks.

Best Regards

[Alf]

Glad to be of help, thanks for feedback and rep!

Alf