You're welcome

Cheers

--
macropod
[MVP - Microsoft Word]


"mateo" wrote in message ...
| Wow, it works great. Thank you very much, Macropod! I hope someone else gets
| to discover this info someday.
|
| Cheers!
| Mateo
|
|
|
| "macropod" wrote:
|
| To process the whole worksheet, you could use something like:
|
| Sub RoundDownSheet()
| Dim OldVal As Object
| Dim NewVal As String
| Dim ConvRate
| ConvRate = InputBox("What is the conversion rate?", "Currency Conversion", 1)
| On Error GoTo Abort
| For Each OldVal In ActiveSheet.UsedRange
| If Application.WorksheetFunction.IsNumber(OldVal.Valu e) Then
| NewVal = OldVal.Formula
| If Left(NewVal, 1) = "=" Then
| NewVal = Right(NewVal, Len(NewVal) - 1)
| End If
| NewVal = "=ROUND(" & NewVal & "*" & ConvRate & ",-1)-1"
| OldVal.Formula = NewVal
| End If
| Next OldVal
| Abort:
| End Sub
|
| This version includes a prompt for the currency conversion rate. If you don't want to apply the same conversion rate throughout
the
| worksheet, you'll need to change 'ActiveSheet.UsedRange' to 'Selection' and select the range the macro is to apply to. The new
| formula retains both the original value and the conversion rate.
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
|
| "mateo" wrote in message ...
| | No, did not modify. Tried again multiple times, the original still works fine
| | though, and thinking about how I will be referencing each table individually
| | when going back and forth from the destination webpage, I realize that
| | rounding each table individually using your original formula is not a problem
| | at all. Thanks Macropod.
| |
| | If you care to write one that can start from the very beginning in one step
| | allowing for a bunch of text thrown into the tables, that would be great.
| | Converting currencies and then rounding to the nearest nine.
| |
| | an example: I need to change $4699CAD into $3099Euro using the conversion
| | rate of 0.66. A macro that could easily adapt to different currencies by
| | throwing in the percentile would be great.
| |
| | Cheers
| | M
| |
| |
| |
| | "macropod" wrote:
| |
| | Hi mateo,
| |
| | It doesn't behave that way for me. On my system, it processes all cells containing only numeric values, or formulae that
return
| | numeric values, and skips any other cells (eg empty cells and cells with text). Merged cells have no effect - the code
processes
| | them just the same as it does with un-merged cells.
| |
| | Did you modify the code in any way?
| |
| | Cheers
| |
| | --
| | macropod
| | [MVP - Microsoft Word]
| |
| |
| | "mateo" wrote in message ...
| | | Thanks for that Macropod, I should have mention the text titles. For some
| | | reason when running your new macro after selecting everything it either says
| | | "this macro requires merge cells to be of the same size." Or when just
| | | selecting numbers, it make each row contain the same number as the first cell
| | | in each row.
| | |
| | |
| |
|
|
|