Insert new data line when criteria metSolved/Closed

Question

Good day,

I need to insert a new line with data when the criteria met based on the table below.

Time                     Operation
------                     ------------
9:30                     Machining
9:50                     Machining
10:10                   Machining
10:30                   Assembly
10:50                   Assembly
11:10                   Assembly

In this case, when the time is 10:10, it should insert a new line with time 10:10 with operation BREAK TIME. Then, change the time of the original 10:10 to 10:20 and the rest of the time should changed accordingly as well to the multiple of 20 minutes. The end result should look as follow.

Time                      Operation
------                      -------------
9:30                      Machining
9:50                      Machining
10:10                    BREAK TIME
10:20                    Machining
10:40                    Assembly
11:00                    Assembly
11:20                    Assembly

How to go about this in macro? Appreciate the help from everyone. 

Thank you in advance for the help.

Best regards,
wliang

venkat1926 · Answer

the data is from A1 with column headings


timing	operation
9:30	Machining
9:50	Machining
10:10	BREAK TIME
10:20	Machining
10:40	Assembly
11:00	Assembly
11:20	Assembly
try this macro


Sub test()
    Dim dtime1 As Date, dtime2 As Date, cfind As Range, diff As Date
    Dim c As Range
    Worksheets("sheet1").Cells.Clear
    Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("a1")
    Worksheets("sheet1").Activate
    dtime1 = InputBox("type the time which you want to find e..g 10:10")
    dtime2 = InputBox("type the time you want to r eplace e.g. 10:20")
    diff = dtime2 - dtime1
    Set cfind = Columns("A:A").Cells.Find(what:=dtime1, lookat:=xlWhole)
    If Not cfind Is Nothing Then
        cfind.EntireRow.Insert
        MsgBox cfind.Address
        cfind.Offset(-1, 0) = dtime1
    End If
    cfind.Offset(-1, 1) = "BREAK TIME"
    For Each c In Range(cfind, cfind.End(xlDown))
        c = c + diff
    Next c

End Sub

wliang · Answer

Hi venkat,

Thanks for your reply. I copied the codes you provided and try it out but it hit with problem on this line:

cfind.Offset(-1, 1) = "BREAK TIME"

The error is "Object variable or With block variable not set".

Also, can the macro be executed without the input as I would like to have the macro check the data and execute accordingly?

Thanking you in advance for the help.

Best regards,
wliang

wliang · Answer

Hi venkat,

I believe I found what contributed to the error. I put the data on the wrong sheet to work. The error resolved now.

However, I still would like to know if the macro can be executed without the input.

Sorry for the confusion and thanks for the advice.

Best regards,
wliang

venkat1926 · Answer

I put the input box because you can use any inputs.
but if that 10:10 and 10:20 are fixed then in the macro you replace by


detime1=10:10
dtime 2=10:20

wliang · Answer

Hi venkat,

Thanks for your prompt reply. I have substituted the input box with the fixed time and it worked fine so far.

However, as I have blank row in between the rows with data, the macro stopped after reading the next row with data. I inserted a line to get the total of rows in the sheet and tried to use it to make the macro loop until the end of the sheet. But it was not successful.

Here is my file for your reference and advice.
http://speedy.sh/YxxGG/Book2.xls

Thanks in advance for your advice.

Best regards,
wliang

venkat1926 · Answer

try to do this and see

change

 For Each c In Range(cfind, cfind.End(xlDown))

to 

for each c in range(cfind,cells(rows.count,cfind.column).end(xlup))

wliang · Answer

Hi venkat,
Thanks for your immediate reply. With your advice, I am almost there with the result.

However, there is still a small problem. The result looks like in the following and I would like to delete the working data which is the "0:10" and let it blank.

10:10 | BREAK TIME
<BLANK ROW>
10:20 | XXXX (the info)
0:10
10:40 | XXXX (the info)
0:10
11:00 | XXXX (the info)

I added in the code as below.

For Each c In Range(cfind, Cells(Rows.Count, cfind.Column).End(xlUp))
        c = c + diff
        Cells(Rows.Count, cfind.Column).Clear
Next c


Sheet1 in the attached is the result of the macro.
http://speedy.sh/AYYBW/Book2.xls

Sorry for so many questions. Please advise.

Thanks in advance for your advice.

Best regards,
wliang

Insert new data line when criteria met

7 responses

Similar discussions

Subscribe To Our Newsletter!