Macro analyses Cell.Serches for given word,the puts text in cell a

[andrei]

I have a column with a lot of cells . Something like this :

Product
Price
Some empty cells
Product
Price
Some empty cells
Price
Some empty cells
Product
Price

So , sometimes i have the price , but no product name for the cell above .
What i need is a macro which serches for keyword "price" . After that goes to
cell above . In cell above is empty puts in it " unknown product" . If cell
above has text in it ( even a single character or number ) , it leaves the
cell as it is

 

[Patrick Molloy]

thet the constant to the correct column letter.

Option Explicit
Sub checkPrice()
Const col As String = "B"
For rw = 2 To Cells(1, col).End(xlDown).Row
If Cells(rw, col) = "price" Then
If Cells(rw - 1, col) = "" Then
Cells(rw - 1, col) = "unknown product"
End If
End If
Next
End Sub

 

[Patrick Molloy]

earlier posting used xldown to get last row, given that we know there are
blanks, a more suitable approach would be to go to the bottom of the column
as use xlUp to get the last row --- ie the FOR loop has changed ...see below:

Option Explicit
Sub checkPrice()
Const col As String = "B"
Dim rw As Long
For rw = 2 To Cells(Rows.Count, col).End(xlUp).Row
If Cells(rw, col) = "price" Then
If Cells(rw - 1, col) = "" Then
Cells(rw - 1, col) = "unknown product"
End If
End If
Next
End Sub