INSERT ROW WITH SPECIFIS TEXT IN A COLUMN

[xenios]

Hi All!

How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F.

If I record the macro it gives me the following result.

Sub INSERT()
'
' INSERT Macro
'

'
    Rows("2:2").Select
    Selection.INSERT Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "JUICE"
    Range("A2:G2").Select
    Range("G2").Activate
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlLTR
        .MergeCells = False
    End With
    Selection.Copy
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 9
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 13
    ActiveWindow.ScrollRow = 14
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 21
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 23
    ActiveWindow.ScrollRow = 24
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 26
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 29
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 32
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 35
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 37
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 41
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 44
    ActiveWindow.ScrollRow = 45
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 49
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 52
    ActiveWindow.ScrollRow = 53
    ActiveWindow.ScrollRow = 54
    ActiveWindow.ScrollRow = 55
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 62
    ActiveWindow.ScrollRow = 66
    ActiveWindow.ScrollRow = 68
    ActiveWindow.ScrollRow = 69
    ActiveWindow.ScrollRow = 70
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 72
    Rows("82:82").Select
    Selection.INSERT Shift:=xlDown
    Range("A82").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "MELON"
    Range("A82:G82").Select
    Range("G82").Activate
    Selection.Copy
    ActiveWindow.ScrollRow = 73
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 79
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 82
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 85
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 88
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 90
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 93
    ActiveWindow.ScrollRow = 94
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 96
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 99
    ActiveWindow.ScrollRow = 101
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 103
    ActiveWindow.ScrollRow = 104
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 106
    ActiveWindow.ScrollRow = 107
    ActiveWindow.ScrollRow = 108
    ActiveWindow.ScrollRow = 109
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 112
    ActiveWindow.ScrollRow = 114
    ActiveWindow.ScrollRow = 117
    ActiveWindow.ScrollRow = 118
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 120
    ActiveWindow.ScrollRow = 121
    ActiveWindow.ScrollRow = 122
    ActiveWindow.ScrollRow = 123
    ActiveWindow.ScrollRow = 124
    Rows("132:132").Select
    Selection.INSERT Shift:=xlDown
    Range("A132").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "APPLE"
    Range("A132:G132").Select
    Range("G132").Activate
    Selection.Copy
    ActiveWindow.ScrollRow = 125
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 129
    ActiveWindow.ScrollRow = 132
    ActiveWindow.ScrollRow = 140
    ActiveWindow.ScrollRow = 143
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 148
    ActiveWindow.ScrollRow = 149
    ActiveWindow.ScrollRow = 150
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 152
    ActiveWindow.ScrollRow = 153
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 162
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 164
    ActiveWindow.ScrollRow = 165
    ActiveWindow.ScrollRow = 166
    ActiveWindow.ScrollRow = 167
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 170
    ActiveWindow.ScrollRow = 171
    Rows("183:183").Select
    Selection.INSERT Shift:=xlDown
    Range("A183").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "LIME"
    Range("B184").Select
End Sub

 

[My Aswer Is This]

Try using this Vba script:

Sub Insert_Row()
'Modified  10/22/2020  8:25:02 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
End Sub

 

[xenios]

Try using this Vba script:

Sub Insert_Row()
'Modified  10/22/2020  8:25:02 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox SearchString & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
End Sub

Thank you, I just need the wow to have same background color as row 1, and the text from column F to be written on the new row in CAPS with Center across selection.

 

[My Aswer Is This]

Try this:
Not sure what you mean by center across selection.
What selection?

Sub Insert_Row()
'Modified  10/22/2020  9:06:47 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim color As Long
color = Cells(1, "F").Interior.ColorIndex
MsgBox color
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox UCase(SearchString) & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
Rows(ans).Interior.ColorIndex = color
Cells(ans, "F").Value = UCase(st)
End Sub

 

[My Aswer Is This]

Assuming you want the cell where we enter the text centered
Try this:

Sub Insert_Row()
'Modified  10/22/2020  10:21:15 AM  EDT
Dim st As String
st = InputBox("Enter Search String")
Dim ans As Long
Dim color As Long
color = Cells(1, "F").Interior.ColorIndex
MsgBox color
Dim SearchString As String
Dim SearchRange As Range
SearchString = st
Dim lastrow As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
Set SearchRange = Range("F2:F" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox UCase(SearchString) & "  Not Found": Exit Sub
ans = SearchRange.Row
Rows(ans).Insert
Rows(ans).Interior.ColorIndex = color
Cells(ans, "F").Value = UCase(st)
Cells(ans, "F").HorizontalAlignment = xlCenterAcrossSelection

End Sub

 

[xenios]

across A to G as on the picture, and the text start from A tried to change it but didn't manage

For the moment it asks me to enter the word, can it somehow see it?

It also makes the grey color of the whole row till the end, can be until G?

 

[My Aswer Is This]

How do you expect it to see the word ? Where is the word?
Your original post said: with specific text in column F.

I do not know what specific Text.
What specific Text

 

[xenios]

yes, the word/text is always in column F,
Let say it goes

Melon
Melon
Melon
Orange
Orange

So the row on top of of the melon it to show Melon
and before Orange to show Orange
if it's possible of course

 

[My Aswer Is This]

So this should happen no matter what the word. So if you have:
Alpha
Bravo
Charlie
Delta
Alpha
Dog
Cat

What would you want your end result to look like?

 

[xenios]

Please find the pic attached. In my case the same word can't be located in different place, it is sorted by names.