Macro question

Posted by Herman on July 31, 2001 3:40 AM

I like to know how i can do the following

i have a workbook with 3 sheets
each sheet has in different cells,cells with a currency in it.

i want that my macro looks in all 3 sheets
and look for cells with currency and then multiply the cells found with 1.2

because some sheets have only 3 rows data i also want
that before the macro starts, the macro looks for the last cell and only loops for searching for currency cells in for example the range a1:c7 (where c7 is my last cell)so my macro finishes faster

Posted by Ivan F Moala on July 31, 2001 7:41 PM

Try something like this;
Assums Values are constants and NOT derived
from formulas.

Sub changeVal()
Dim Sh As Worksheet
Dim rg As Range
Dim cell As Range
Dim K As Double

For Each Sh In ThisWorkbook.Sheets
Set rg = Sh.[a1].SpecialCells(xlCellTypeConstants, 1)
For Each cell In rg
If cell.NumberFormat Like "*$*" Then
cell = cell * 1.2
K = K + 1
End If
Next cell
Set rg = Nothing
Next Sh
MsgBox K & " values converted"
End Sub

Ivan

Posted by Herman on August 01, 2001 10:03 PM

Thanx for your effort i have used some of it and got it oke for the moment.