edit multiple values

[Eddie]

Hi, I have a price list sheet with item descriptions and pricing. The
information is all over, not in straight colums or rows.
Is there a way to have excel search out all the cells with $ values and
reduce or increase by a specific %. example : Icrease or derease ever cell
with a $ value by 20%

 

[Pat Garard]

Hi Eddie,

It can be done with a VBA macro, Post Back if you want more.
--
Hope this helps!

Pat Garard
Australia.
apgarardATbigpondDOTnetDOTau

 

[Dave Peterson]

Are those dollar signs placed there by the numberformat?

If yes, here's one version of a macro that increases each numeric constant
that's formatted with $ signs by 20%.

(It doesn't touch any formulas at all.)

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Const pctIncrease As Double = 0.2 '20%

Set wks = ActiveSheet

With wks
Set myRng = Selection
'set myrng = .UsedRange

On Error Resume Next
Set myRng = Intersect(myRng, _
myRng.Cells.SpecialCells(xlCellTypeConstants, xlNumbers))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Sorry--no numeric constants found"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
If InStr(1, .NumberFormat, "$", vbTextCompare) > 0 Then
.Value = .Value * (1 + pctIncrease)
End If
End With
Next myCell
End With

End Sub

I left two lines in there:
Set myRng = Selection
'set myrng = .UsedRange

The top one says you want to run it against a selection of cells (you select
first). The bottom (.usedrange) version looks at every cell on that's used on
that sheet.

Just change the .2 to -.2 to get a 20% decrease.

Since this changes your data, you may want to really check the output before you
save over the older version.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm