Find Text and Insert Row Above

[ryguy7272]

I lost my train of thought and now I’m stuck. I’m trying to figure out a way
to insert rows dynamically. The logic is to look for this text: ‘Enter
non-listed privately held securities or groups of assets by asset class.’

Then I need to go up to find the first cell ABOVE that contains this text
'CUSIP', then offset (1,0) . . . ActiveCell.EntireRow.Insert AND copy the
format from the row above. I have CUSIP in ColA with DataValidation on this
cell. I want to copy that format down 1 row and I have a few other cells to
the right, with other formatting, all on the same row; I want to copy this
formatting down when the row is inserted.

Here’s the non-working code that I have now:
Private Sub CommandButton3_Click()

CovRow = 0

For x = 20 To 200
If InStr(Cells(x, 1), "Enter non-listed" & "*") <> 0 Then
CovRow = x
End If
Next x

If CovRow > 1 Then
CovRow.Select

Selection.Resize(1, 25).Select
ActiveCell.EntireRow.Insert
End If
End Sub

I'm very open to ideas as to how to do this!

Thanks everyone!!

 

[JLGWhiz]

If CovRow > 1 Then
CovRow.Select
Selection.Resize(1, 25).Select
ActiveCell.EntireRow.Insert
End If

Maybe this:

If CovRow > 1 Then
Range("A" & CovRow).Resize(1, 25).Copy
Range("A" & CovRow + 1).PasteSpecial Paste:=xlFormats

 

[ryguy7272]

Thanks JLG! I made a couple modifications and came up with this:
If CovRow > 1 Then
Range("A" & CovRow).Resize(2, 26).Select
Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Selection.End(xlUp).Select
Selection.Resize(1, 25).Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End If

Now, that works great, but I have two ActiveX control buttons on my sheet,
and when I run the code, the buttons don't get pushed down, although
everything else is fine. I can record a macro, and see that the piece of
code below, indeed, does push the buttons down:
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

How do I incorporate that into my code above? It seems like the code is
exactly the same, but it works VERY different. Why?

Thanks!!

 

[JLGWhiz]

Sorry, you wanted the inserted row above:

If CovRow > 1 Then
Range("A" & CovRow).Resize(1, 25).Copy
Range("A" & CovRow).EntireRow.Insert
Range("A" & CovRow).PasteSpecial Paste:=xlFormats

If CovRow > 1 Then
CovRow.Select
Selection.Resize(1, 25).Select
ActiveCell.EntireRow.Insert
End If

Maybe this:

If CovRow > 1 Then
Range("A" & CovRow).Resize(1, 25).Copy
Range("A" & CovRow + 1).PasteSpecial Paste:=xlFormats

 

[ryguy7272]

Thanks again, but that's still not it. This is the setup:

This text is in currently in row 21:
‘Enter non-listed privately held securities or groups of assets by asset
class.’

Basically, I’m going to let users insert rows by clicking a button. I first
want to find the text above (on ColA), then go UP to the first non blank cell
in ColA (currently A15), then offset (1,0) and add a row there plus fill down
the formatting from row 15. Although it’s not elegant, the code below works
except for the part of pushing those ActiveX buttons down when the rows are
inserted. The Code:
Private Sub CommandButton3_Click()

CovRow = 0

For x = 20 To 100
If InStr(Cells(x, 1), "Enter non-listed privately held securities or
groups of assets by asset class.") <> 0 Then
CovRow = x
End If
Next x

If CovRow > 1 Then

Range("A" & CovRow).Resize(2, 26).Select
Selection.Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
Selection.End(xlUp).Select
Selection.Resize(1, 25).Copy
Selection.Offset(1, 0).Select
ActiveSheet.Paste

End If

End Sub

The buttons are between rows 17 and 21. I’d like to use the AxtiveX buttons
for this project because they are harder to accidentally move (Form Controls
move too easy if they are accidentally right-clicked). What do you think?

Thanks again!!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Sorry, you wanted the inserted row above:

If CovRow > 1 Then
Range("A" & CovRow).Resize(1, 25).Copy
Range("A" & CovRow).EntireRow.Insert
Range("A" & CovRow).PasteSpecial Paste:=xlFormats

 

[GS]

ryguy7272 explained :

I lost my train of thought and now I’m stuck. I’m trying to figure out a way
to insert rows dynamically. The logic is to look for this text: ‘Enter
non-listed privately held securities or groups of assets by asset class.’

Then I need to go up to find the first cell ABOVE that contains this text
'CUSIP', then offset (1,0) . . . ActiveCell.EntireRow.Insert AND copy the
format from the row above. I have CUSIP in ColA with DataValidation on this
cell. I want to copy that format down 1 row and I have a few other cells to
the right, with other formatting, all on the same row; I want to copy this
formatting down when the row is inserted.

Here’s the non-working code that I have now:
Private Sub CommandButton3_Click()

CovRow = 0

For x = 20 To 200
If InStr(Cells(x, 1), "Enter non-listed" & "*") <> 0 Then
CovRow = x
End If
Next x

If CovRow > 1 Then
CovRow.Select

Selection.Resize(1, 25).Select
ActiveCell.EntireRow.Insert
End If
End Sub

I'm very open to ideas as to how to do this!

Thanks everyone!!

$0.02:
If you start your loop at 20 then any row it finds with your value will
be '> 1'. I would use Find() because it's a lot faster than a VBA For
Each loop, and will return a range object that you can test to see if
it found (or not) the value you passed:

Sub InsertBlankRow()
Dim rng As Range
Dim lStartrow As Long, lEndRow As Long
Dim lFoundRow As Long, lInsertRow As Long
lStartrow = 20: lLastRow = 100
Const sCovRow As String = "Enter non-listed privately held " & _
"securities or groups of assets by asset class."
With Range("A" & lStartrow & ":A" & lEndRow)
Set rng = .Find(sCovRow, LookIn:=xlValues)
End With
If Not rng Is Nothing Then
lFoundRow = rng.Row
If Cells(lFoundRow, 1).Offset(-1) = "" Then
lInsertRow = lFoundRow
Else
lInsertRow = Cells(lFoundRow, 1).End(xlUp).Row
End If

Cells(lInsertRow).EntireRow.Insert
End If
End Sub

Here's what this does:
We set a ref to our rng variable using the Find() method of the Range
object. Next we test to see if the value was found by seeing if the ref
was set or not. If set, we get its row into our lFoundRow variable and
check that the cell immediately above is not empty. If it is empty then
this is where we insert the new row (or not if that's your preference).
In the Else clause we navigate up the column to the first cell below
the first empty cell above the found row using the End() function and
specifying the direction. This function finds the last cell that
contains values above the start location. In this case, it's the row
where you want to insert the new row, and so we insert an entire row.
(Since you state you want to find the first empty row above the found
text and then move down one row, the End() function puts you there by
default. Also, Excel copies the formatting from the row above by
default and so there's no need to repeat that in code.

HTH
Garry

 

[ryguy7272]

Thanks GS. I pasted that into my Sheet and got this error:
Run-time error ‘1004’:
Application-defined or object-defined error

I stepped through the code and it seems to error on this line:
With Range("A" & lStartrow & ":A" & lEndRow)

Variables lStartrow and lLastRow are set correctly. After that, it blows up.

As an aside, when I click the button, I get a slightly different error:
Run-time error ‘1004’:
Method ‘Range’ of object ‘_Worksheet’ failed

Slightly different error; same line:
With Range("A" & lStartrow & ":A" & lEndRow)

That code looks pretty cool!! Your description sound pretty darn accurate!!
Am I doing something wrong?

 

[GS]

ryguy7272 presented the following explanation :

Thanks GS. I pasted that into my Sheet and got this error:
Run-time error ‘1004’:
Application-defined or object-defined error

I stepped through the code and it seems to error on this line:
With Range("A" & lStartrow & ":A" & lEndRow)

Variables lStartrow and lLastRow are set correctly. After that, it blows up.

As an aside, when I click the button, I get a slightly different error:
Run-time error ‘1004’:
Method ‘Range’ of object ‘_Worksheet’ failed

Slightly different error; same line:
With Range("A" & lStartrow & ":A" & lEndRow)

That code looks pretty cool!! Your description sound pretty darn accurate!!
Am I doing something wrong?

Nope! It's my bad! The line should be:
With Range("A" & CStr(lStartrow) & ":A" & CStr(lEndRow))

Sorry about that!
Garry
--

 

[ryguy7272]

Thanks for the help everyone! I ended up using this:

Private Sub CommandButton3_Click()
Dim rng As Range
Dim lStartrow As Long, lLastRow As Long
Dim lFoundRow As Long
lStartrow = 20: lLastRow = 30
Const sCovRow As String = "Enter non-listed privately held securities or
groups of assets by asset class."

With Range("A" & CStr(lStartrow) & ":A" & CStr(lLastRow))
Set rng = .Find(sCovRow, LookIn:=xlValues)
End With
If Not rng Is Nothing Then
lFoundRow = rng.Row
If Cells(lFoundRow, 1).Offset(-1, 0) = "" Then
Cells(lFoundRow, 1).Select
Selection.Offset(-7, 0).Select
ActiveCell.EntireRow.Insert
End If
End If
End Sub

Works great!!

It doesn't look like lInsertRow is used.

 

[GS]

ryguy7272 formulated on Saturday :

Thanks for the help everyone! I ended up using this:

Private Sub CommandButton3_Click()
Dim rng As Range
Dim lStartrow As Long, lLastRow As Long
Dim lFoundRow As Long
lStartrow = 20: lLastRow = 30
Const sCovRow As String = "Enter non-listed privately held securities or
groups of assets by asset class."

With Range("A" & CStr(lStartrow) & ":A" & CStr(lLastRow))
Set rng = .Find(sCovRow, LookIn:=xlValues)
End With
If Not rng Is Nothing Then
lFoundRow = rng.Row
If Cells(lFoundRow, 1).Offset(-1, 0) = "" Then
Cells(lFoundRow, 1).Select
Selection.Offset(-7, 0).Select
ActiveCell.EntireRow.Insert
End If
End If
End Sub

Works great!!

It doesn't look like lInsertRow is used.

I don't understand why you'd hard code the offset. Using the End()
function as I illustrated is maintenance free if the number of rows
ever changes and your solution is not.

Garry
--