Appel = 10 in Excel spread sheet?

[Mr.G]

Hi all
I want to create a macro that will change text in a cel to a number only when calculating.

CellA1 = Appel
CellA2 = Pear
CellA3 = No

Appel , Pear and No must still display. When you calculate sum(appel, pear,No) you must get a number.eg.=50

I have a whole workbook that needs to be calculated in this manner constantly.
I do not know if one can tell Excel that
If cell.Text = "appel" then
cell.numbervalue=10

thank you

[Charlize]

Option Explicit
Option Compare Text
Sub text_to_number()
Dim vFruit As Long, vSum As Long
Dim cell As Variant
For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
Select Case cell
Case "Appel"
vFruit = 10
Case "Pear"
vFruit = 20
Case Else
vFruit = cell.Value
End Select
vSum = vSum + vFruit
Next cell
MsgBox "Total : " & vSum
End Sub

[Mr.G]

Thank you. Charlize

That could work except that I need it to remember that a appel anywhere in the workbook will = 10.

So when I change the value in Vb it will adjust all the calculated values in whole book.

[Aussiebear]

I believe it should do so.

[Bob Phillips]

A formula way

=SUMPRODUCT((A1:A10={"Apple","Pear"})*({10,20}))

[Mr.G]

and then confusion set in.......

So how does it work in Vb.?
This is what I got......doesn't work.
If Range.Value = "Appel" Then
Item.Value = 10
If Range.Value = "Yes" Then
Item.Value = 50

regards

[Bob Phillips]

Look at Charlize's post, and don't use Range as a variable name, nor Item.

[Mr.G]

Doesn't that only refer to a range and not the whole work book?

it gives me a error"type mismatch"

If I change the range or clear the range in Excel cells it gives me an aswer of 0.

Maybe I should be more clear, the calculations won't be at the same place in every sheet and every sheet might have around 100 calculations on it.

simple calc like Z5=sum(Z10:Z20)

Now we might have 5 appels and 1 pear in one and the next might have 2 bannas and 4 pears.

[Aussiebear]

Then declare a value for each type of fruit

[Mr.G]

That is still only valid for the Range(A1:A).
I need it to be valid only for that Specific fruit in the whole book in every formula. I will add the values for all the other fruits after.
Should this be changed from....
.......For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).......
to....
For Each cell In Workbook

[Bob Phillips]

No, range is a worksheet property, not a workbook



For Each sh In Activeworkbook.Worksheets
For Each cell In sh
Select Case cell
'etc

[Mr.G]

Thank you

It will work.

Enjoy.

[Charlize]

Let me know when you've got a solution. A little tip : use the usedrange property, otherwise you'll have to wait a long time to process all the cells of all the sheets in a workbook.