Replace Cell values (vba)

[thorne_]

Hi all

I have a sheet which downloads data onto it. This data is split into logical columns eg 'Price'

However this column will not always be the same and some rows dont contain a value for price.

Can anyone help me to create a procedure that identifies all values for price and replaces them with a generic value eg 100 goes to ?100

thanks in advance

[lucas]

Well, you could use a custom format such as:

[$€-42D]#,##0

But you don't tell us how we might identify the price columns......Do they say price in the header?

[thorne_]

Sorry - thats where the problem is, because this data is downloaded it is on the sheet in a fairly haphazard manner. eg cell b12 may have a value of 'price' and then b13:b17 may have values that i wish to change.

Then further down in the sheet c52 may have value of 'price' and c52:c87 may need to be changed( please note that the number of rows being changes is variable and my examples are not necessarily accurate)

The main point is that the code must first identify any row with 'price' in it and look in the immediate rows below it until it hits a blank value. it will then start looking for 'price' again

sorry if im confusing you!

[thorne_]

If Worksheet = vbNullString Then Worksheet = ActiveSheet.Name

Dim cellNum As Integer, endRow As Integer

With Worksheets(Worksheet)
On Error Resume Next
endRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then xlLastRow = 0
End With

Dim bookCol As Range, Cell As Object

'loop through each row
For Row = 1 To endRow

Set bookCol = Range(Row)
Set bookCol = Range(bookCol, Cells(Rows.Count, bookCol.Column).End(xlUp))
cellNum = 1
For Each Cell In bookCol.Cells
If Cell.Value Like "Price" Then 'move down the rows until u hit a blank row

Cell.Value = "YAY"
'TODO: check all rows below to see if they contain a value

End If
cellNum = cellNum + 1
Next
Next Row
Thats what i have at the moment but it doesn't seem to be working correctly. Im also not sure of how to do the inner loop once 'price has been found

[lucas]

so the word "price" is physically located at various places in the sheet and you want to find that word and format the cells beneath it until you come to a blank cell?

[thorne_]

If Worksheet = vbNullString Then Worksheet = ActiveSheet.Name

Dim endRow As Integer

With Worksheets(Worksheet)
On Error Resume Next
endRow = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByRows, xlPrevious).Row
If Err <> 0 Then xlLastRow = 0
End With

Set UsedRng = Worksheets(Worksheet).UsedRange
LastCol = UsedRng(UsedRng.Cells.Count).Column

'loop through each row
For Row = 1 To endRow
For cellNum = 1 To LastCol
If Worksheets(Worksheet).Cells(Row, cellNum).Value = "Price" Then 'move down the rows until u hit a blank row
For i = Row + 1 To endRow
If Worksheets(Worksheet).Cells(i, cellNum).Value = "" Then
Exit For
End If
Worksheets(Worksheet).Cells(i, cellNum).Value = "NEW VALUE"

Next i
Row = i
Exit For
End If
Next cellNum
Next Row

Right so for anybody else - this seems to work, no doubt there are errors but ill find them when necessary

[thorne_]

so the word "price" is physically located at various places in the sheet and you want to find that word and format the cells beneath it until you come to a blank cell?

yup exactly :)