Find and Replace Values in Range

[ThatGirlinMS]

I have a workbook with serveral sheets that has numbers formatted as
'General' throughout in varying places. The worksheets are of no
specific width or length.

I need to loop through all sheets looking for cells containing data
within a range (ex. 167 - 450) and change to numbers (ex subtract one
from the value)


any ideas? I thought there has to be a better way than looping through
EVERY cell in EVERY sheet.

(>= 167 and <= 450) = (value - 1)

Thanks!

 

[Don Guillett]

how about something like this

for each ws in worksheets
for each c in range("a167:a450")
if c>=167 and c<=450 then c.value=c-1
next c
next ws

 

[ThatGirlinMS]

how about something like this

for each ws in worksheets
for each c in range("a167:a450")
if c>=167 and c<=450 then c.value=c-1
next c
next ws

Nope, that's not the solution to my question. I have to look at EVERY
cell in EVERY sheet. Then I have to see if the value in that cell is
between a couple of values (ex. 167 - 450) and perform an action
accordingly (ex subtract one from the value). I have a
Selection.SpecialCells(xlCellTypeConstants).Select which selects all
cells with a constant value within. Is there a way to loop through
every cell in the selection?

 

[ThatGirlinMS]

how about something like this

for each ws in worksheets
for each c in range("a167:a450")
if c>=167 and c<=450 then c.value=c-1
next c
next ws

Nope, that's not the solution to my question. I have to look at EVERY
cell in EVERY sheet. Then I have to see if the value in that cell is
between a couple of values (ex. 167 - 450) and perform an action
accordingly (ex subtract one from the value). I have a
Selection.SpecialCells(xlCellTypeConstants).Select which selects all
cells with a constant value within. Is there a way to loop through
every cell in the selection?

 

[ThatGirlinMS]

Nope, that's not the solution to my question. I have to look at EVERY
cell in EVERY sheet. Then I have to see if the value in that cell is
between a couple of values (ex. 167 - 450) and perform an action
accordingly (ex subtract one from the value). I have a
Selection.SpecialCells(xlCellTypeConstants).Select which selects all
cells with a constant value within. Is there a way to loop through
every cell in the selection?

I solved it myself. Thank's for viewing!

Sub callme()
Application.ScreenUpdating = False
Dim sheetcnt As Integer
sheetcnt = ActiveWorkbook.Sheets.Count
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
MsgBox "Working on sheet " & ActiveSheet.Name

Selection.SpecialCells(xlCellTypeConstants).Select

Dim oCell As Range
For Each oCell In Selection
If IsNumeric(oCell.Value) Then
If oCell.Value >= 167 And oCell.Value <= 450 Then
oCell.Value = (oCell.Value - 1)
oCell.Font.Bold = True
End If
If oCell.Value >= 475 And oCell.Value <= 595 Then
oCell.Value = (oCell.Value + 2)
oCell.Font.Bold = True
End If
If oCell.Value >= 596 And oCell.Value <= 805 Then
oCell.Value = (oCell.Value + 5)
oCell.Font.Bold = True
End If
If oCell.Value >= 812 And oCell.Value <= 814 Then
oCell.Value = (oCell.Value - 1)
oCell.Font.Bold = True
End If
If oCell.Value >= 815 And oCell.Value <= 819 Then
oCell.Value = (oCell.Value + 2)
oCell.Font.Bold = True
End If
End If
Next
Next
MsgBox "Done"
End Sub

 

[Don Guillett]

I'm glad you solved it, all by yourself....
A simple mod to my "something like" would have done it, as well.
BTW You do NOT need to activate the worksheet or select the special cells.