Excel97/VB

I'd like to know how i can use a formula on a variable selection of cells with VB.
The problem is that i don't know how to create a code which reads the active selection i make from my Excel sheet, and then use a formula on this selection. When i want to change te numberformat of the selection (Selection.NumberFormat=....) it works fine on any selection of cells i make, but the SelectionFormula statement only works on the first cell of my slection (only one active cell in a selection). I just don't know enough of VB to solve this simple problem. Can you?
LVL 1
mosiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

staffiidbaCommented:
Increase your points. This questions aint that difficult but would require some work.
0
mosiAuthor Commented:
Adjusted points to 100
0
nfernandCommented:
Hi guys...

This will let you work with every cell in the active range selected:

------------------------------
Dim X as Range

for each X in Selection
   .... Example
   debug.print X.value
   .... Your Code here
   Next

------------------------------
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tureCommented:
mosi,

I don't really understand what you are having problems with. This code will enter a formula in every cell in the selection.

Sub HelpMosi()
  Selection.Formula = "=A1+B1"
End Sub

Note that this procedure enters formulas using relative references. The FIRST cell of the selection will have the formula '=A1+B1', just as written in the code. The cell below the first cell will have the formula '=A2+B2' and so on.

Ture Magnusson
Karlstad, Sweden
0
mosiAuthor Commented:
I know, that is'nt the problem. Your code would replace existing formulas in the selected cells.
The idea is that i want to manipulate data in a selection with respect to formulas which are in the selected cells.
I have an Excel sheet with financial data and calculations in it. Now i want to use a VB macro that lets me select any combination of cells and then changes the currency to another while leaving all calculations in tact.
0
mosiAuthor Commented:
To nfernand

Thanks, here's the VB code i've used with your comment in it. It works fine!. Can you tell me if this VB code is correct, and more important can it be used in complex (linked) sheets without destroying formulas?.


Private Sub CommandButton1_Click()
Dim X As Range
For Each X In Selection
X = (X / 2.20371)
Next
Selection.NumberFormat = "_(€* #,##0.00_);_(€* (#,##0.00);_(€* ""-""??_);_(@_)"
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
End With
End Sub
0
mosiAuthor Commented:
To nfernand

Thanks, here's the VB code i've used with your comment in it. It works fine!. Can you tell me if this VB code is correct, and more important can it be used in complex (linked) sheets without destroying formulas?.


Private Sub CommandButton1_Click()
Dim X As Range
For Each X In Selection
X = (X / 2.20371)
Next
Selection.NumberFormat = "_(€* #,##0.00_);_(€* (#,##0.00);_(€* ""-""??_);_(@_)"
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .IndentLevel = 0
    .ShrinkToFit = False
    .MergeCells = False
End With
End Sub
0
nfernandCommented:
Hi Mosi...

1) The format you used in "NumberFormat" looks a little bit strange for me (I suppose the "boxes" are problems with my Internet Browser. Did you wrote boxes on it?)

2) The line: X = (X / 2.20371) will destroy any formula in the active selection. It is something like "Paste as Values". If you need to preserve your original formulas use:
x.formula = x.formula & " / 2.20371"

Hope this help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mosiAuthor Commented:
To nfernand:

Thanks a lot!, i will accept your comments as an answer. I did'nt write boxes in the code by the way, so i suppose your browser plays tricks on you. The numberformat line is a standard currency format with a 'Euro' sign in it. The VB program will be used to convert all Excel sheets with 'Dutch guilders' to 'Euro'.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.