View Full Version : Replace Cell values (vba)
thorne_
01-07-2009, 07:48 AM
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
01-07-2009, 08:15 AM
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_
01-07-2009, 08:23 AM
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_
01-07-2009, 08:39 AM
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
01-07-2009, 08:40 AM
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_
01-07-2009, 09:00 AM
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_
01-07-2009, 09:01 AM
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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.