VBA Macro if critieria is met place text in blank row of column A

[slohman]

I am trying to find a macro that if the word "Type" is found in column A the word Essentials is found in Column H but could be found on any row I need it to find the row above which is empty and put the words "Play > Essentials"
also the next thing to find is the again "Type" in Column A and the word Swings in Column H and find the empty row above and put the words "Play > Swings" see attached.

I'm copying the rows from another worksheet and need these as headers. But I need it to stop at only once I dont want it to loop.

Play > Essentials
Type	SKU	Name	ALP Price						CategoriesAA2
simple	SS10-1111	SS10-3000	999​						Essentials
simple	SS10-2222	SS10-3001	1000​						Essentials
Play > Swings
Type	SKU	Name	ALP Price						CategoriesAA2
simple	SW50	3.5m Swing Double Polished	4300​						Swings
simple	SW98	3m Swing Double Polished	4100​						Swings

 

[DanteAmor]

Hi @slohman.

Thanks for posting on the board.​

If you have the word "Type" more than 2 times in the sheet, the following macro will perform the search and put the data from column "H" in the above row.

Try this:

Sub place_text()
  Dim f As Range, cell As String
  
  Set f = Range("A:A").Find("Type", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      f.Offset(-1).Value = "Play > " & Range("H" & f.Row + 1)
      Set f = Range("A:A").FindNext(f)
    Loop While f.Address <> cell
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[slohman]

Hi @slohman.

Thanks for posting on the board.​

If you have the word "Type" more than 2 times in the sheet, the following macro will perform the search and put the data from column "H" in the above row.

Try this:

Sub place_text()
  Dim f As Range, cell As String
 
  Set f = Range("A:A").Find("Type", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      f.Offset(-1).Value = "Play > " & Range("H" & f.Row + 1)
      Set f = Range("A:A").FindNext(f)
    Loop While f.Address <> cell
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

I dont think I made it clear enough, I have rows from sheet1 a2 to used range it then copies these rows to a2 sheet 2 it will have 2 blank rows at the top and then rows of text, then it will have 1 blank row at the bottom then more text and so on down the length of the sheet 1 which has been copied to sheet 2.

What I would like it to do after or before being copied to Sheet 2 if Column A has the word Type and Column J has the word Essentials I would like to put a heading Play > Essentials in the above blank row + 1 then if Type is found in Column A further down then check that and also check if Column J has the word Swings and put a heading Play > Swings and so on.

I want this to only happen ONCE each time in the sheet to create headings for each of my sheet 2.

Sheet 1 - Example
Type	SKU	Name	ALP Price						CategoriesAA2
simple	SS10-1111	SS10-3000	999​						Essentials
simple	SS10-2222	SS10-3001	1000​						Essentials
Type	SKU	Name	ALP Price						CategoriesAA2
simple	SW50	3.5m Swing Double Polished	4300​						Swings
simple	SW98	3m Swing Double Polished	4100​						Swings

Sheet 2 - Example
Play > Essentials
Type	SKU	Name	ALP Price						CategoriesAA2
simple	SS10-1111	SS10-3000	999​						Essentials
simple	SS10-2222	SS10-3001	1000​						Essentials
Play > Swings
Type	SKU	Name	ALP Price						CategoriesAA2
simple	SW50	3.5m Swing Double Polished	4300​						Swings
simple	SW98	3m Swing Double Polished	4100​						Swings