Reimertoad
05-21-2017, 08:15 PM
I'm attempting to create a macro that will search a range of values, and insert a row directly underneath any of the values found - via an input box.
In column F, I have a list of employees' 'Start Times'. I want to create an input box that prompts me to enter a specific start time (there are 37), and once it finds an exact match of that particular value it will A) copy the entire row in which it was found, B) insert the entire row directly underneath (including the formulas), and C) shade the new row in yellow.
I already have the below code which will find every example of the start time '1200-2000' ONLY and then insert a blank underneath where found;
Sub BlankLine()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
LSearchValue = InputBox("Please enter a Start Time.", "Enter value")
Col = "F"
StartRow = 1
BlankRows = 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "1200-2000" Then
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
As I have so many start times (all with a '****-****' format like '1200-2000' used in the above), can the above be modified to accommodate, or does it need to be done as a range? Also, how/where/what do I enter the code for copying the data from the whole row (once it's successfully found a searched value), and inserting that copied data in a new row directly underneath?
The working file is attached with a 'before' and 'after' on how I'd ideally like it to appear.
Many thanks in advance!!
In column F, I have a list of employees' 'Start Times'. I want to create an input box that prompts me to enter a specific start time (there are 37), and once it finds an exact match of that particular value it will A) copy the entire row in which it was found, B) insert the entire row directly underneath (including the formulas), and C) shade the new row in yellow.
I already have the below code which will find every example of the start time '1200-2000' ONLY and then insert a blank underneath where found;
Sub BlankLine()
Dim Col As Variant
Dim BlankRows As Long
Dim LastRow As Long
Dim R As Long
Dim StartRow As Long
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
LSearchValue = InputBox("Please enter a Start Time.", "Enter value")
Col = "F"
StartRow = 1
BlankRows = 1
LastRow = Cells(Rows.Count, Col).End(xlUp).Row
Application.ScreenUpdating = False
With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "1200-2000" Then
.Cells(R + 1, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
As I have so many start times (all with a '****-****' format like '1200-2000' used in the above), can the above be modified to accommodate, or does it need to be done as a range? Also, how/where/what do I enter the code for copying the data from the whole row (once it's successfully found a searched value), and inserting that copied data in a new row directly underneath?
The working file is attached with a 'before' and 'after' on how I'd ideally like it to appear.
Many thanks in advance!!