Find and Replace Values in Range
 

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!

Find and Replace Values in Range
 

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
--
Don Guillett
SalesAid Software

"ThatGirlinMS" wrote in message
oups.com...
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!

Find and Replace Values in Range
 

Don Guillett wrote:
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
--
Don Guillett
SalesAid Software

"ThatGirlinMS" wrote in message
oups.com...
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!


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?

Find and Replace Values in Range
 

Don Guillett wrote:
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
--
Don Guillett
SalesAid Software

"ThatGirlinMS" wrote in message
oups.com...
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!


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?

Find and Replace Values in Range
 

ThatGirlinMS wrote:
Don Guillett wrote:
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
--
Don Guillett
SalesAid Software

"ThatGirlinMS" wrote in message
oups.com...
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!


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

Find and Replace Values in Range
 

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.

--
Don Guillett
SalesAid Software

"ThatGirlinMS" wrote in message
ups.com...

ThatGirlinMS wrote:
Don Guillett wrote:
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
--
Don Guillett
SalesAid Software

"ThatGirlinMS" wrote in message
oups.com...
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!


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