macro to insert a cell and shift down depending on the value in another cell containing a string

CarDyer

New Member
Joined
Feb 4, 2017
Messages
8
Hi
Hope somebody can help. I've been trawling forums looking for a solution without success.
I am new to VBA, so would be grateful if a full macro could be provided that I can lift and paste.

I am trying to achieve the following

I have a column A with values. Some of the cells contain the string 'timeslice' and other data.
When there is an occurrence of this, I want to insert a blank cell in columns B and C on the same row.
Example
from this:-
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]timeslice 01/01/98 aaa[/TD]
[TD]apple[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]orange[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]apple[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]timeslice 12/03/89 bbb[/TD]
[TD]grape[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]banana[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]grape[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]timeslice 19/09/01 jjj[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 265"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]to this:-[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]timeslice 01/01/98 aaa[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]apple[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]orange[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]timeslice 12/03/89 bbb[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]apple[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]grape[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]banana[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]timeslice 19/09/01 jjj[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]grape[/TD]
[TD]black[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 265"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Mnay thanks in anticipation.:)
 
This assumes your data begin in cell A1.
Code:
Sub InsertCellsBC()
'assumes data start in A1
Dim V As Variant, i As Long
V = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
Application.ScreenUpdating = False
For i = LBound(V, 1) To UBound(V, 1)
    If V(i, 1) Like "timeslice*" Then
        Cells(i, "B").Resize(, 2).Insert shift:=xlDown
    End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:

Code:
Sub Time_Slice()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If InStr(Cells(i, 1).Value, "timeslice") Then
        Cells(i, 1).Resize(, 2).Offset(0, 1).Insert xlShiftDown
        End If
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi JoeMo
Very grateful for the quick response. It works perfectly. I can now use it in work tomorrow.
My Aswer is this sent me similar and it too worked. Glad I took the time to post the question.
 
Upvote 0
Hi My Aswer is this
Very grateful for the quick response. It works perfectly. I can now use it in work tomorrow.
JoeMo sent me similar and it too worked. Glad I took the time to post the question.
 
Upvote 0
Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.
Hi My Aswer is this
Very grateful for the quick response. It works perfectly. I can now use it in work tomorrow.
JoeMo sent me similar and it too worked. Glad I took the time to post the question.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top