Weird Type Mismatch Problem (Cell Formula vs. Cell Value)

[SwissBoy2098]

I have problem with Type Mismatch error. I know what the error is generally about and I'm pretty sure I know the cause of the error, I just don't know how to fix it.

Here an abreviated version that should contain all the relevant lines:


Dim Energy As Double
Energy = Energy + Worksheets("Portfolio Companies").Cells(4, 7).Value


Cells(4,7) has the following code:

=IF(F4="","",IF($A4="USD",F4/1.39, IF($A4="EUR",F4,IF($A4="AUD",F4/1.45,IF($A4="GBP",F4/0.85,IF($A4="CAD",F4/1.29,""))))))

This gives the following value: 151,079

I tried just typing in 151,079 and then the code worked...so what do I have to do to fix this? Your help is greatly appreciated!

P.S. Instead of ...Cells(4,7).Value I also tried Cells(4,7).Text but that didn't help either

THANKS!

[Bob Phillips]

I cannot replicate the problem here.

What Excel/OS version are you using? Can you post a workbook?

[SwissBoy2098]

Unfortunately, I can't post a workbook since it is full of very sensitive information. :-/

Here's the entire code though, maybe this helps?

Sub AVG()

Dim Energy As Double
Dim Water As Double
Dim Materials As Double

Dim i As Integer

Dim AverageE As Integer
Dim AverageM As Integer
Dim AverageW As Integer

i = 4

Do Until Worksheets("Portfolio Companies").Cells(i, 2).Value = ""

If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Energy") Then
Energy = Energy + Worksheets("Portfolio Companies").Cells(i, 7).Value
If (Worksheets("Portfolio Companies").Cells(i, 7).Text <> "") Then
AverageE = AverageE + 1
End If
End If
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Materials") Then
Materials = Materials + Worksheets("Portfolio Companies").Cells(i, 7).Formula
If (Worksheets("Portfolio Companies").Cells(i, 7).Text <> "") Then
AverageM = AverageM + 1
End If
End If
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Water") Then
Water = Water + Worksheets("Portfolio Companies").Cells(i, 7).Formula
If (Worksheets("Portfolio Companies").Cells(i, 7).Text <> "") Then
AverageW = AverageW + 1
End If
End If
i = i + 1
Loop
Worksheets("VBA").Cells(3, 7).Value = Energy / (AverageE)
Worksheets("VBA").Cells(4, 7).Value = Materials / (AverageM)
Worksheets("VBA").Cells(5, 7).Value = Water / (AverageW)
End Sub


I'm using Windows 7 / Excel 2007.

Again, the formula in cell G4 (which is where the loop starts is:

=IF(F4="","",IF($A4="USD",F4/1.39, IF($A4="EUR",F4,IF($A4="AUD",F4/1.45,IF($A4="GBP",F4/0.85,IF($A4="CAD",F4/1.29,""))))))

If I change "Cells(i,7)" to "Cells(i,6) it works. Cell F4 is just a number (210,000). Both columns F and G are formatted as numbers.

Thanks for trying to help me out here, this is kind of urgent and important!

[Bob Phillips]

Nope, that code works just as well.

Can't you obfuscate all the data and still demonstrate the problem, or, create a new workbook that demonstrates the problem.

[SwissBoy2098]

Here you go!

Thanks!

[Bob Phillips]

Your real workbook is using the .Formula property, not .Value. Change it and it works fine.

[Bob Phillips]

BTW, if you put the currencies and rates in a table, you can reduce your conversion formula to

=IF(F4="","",F4/VLOOKUP(A4,Rates!$A$1:$B$6,2,FALSE))

which as well as being simpler, means it is easier to change the rates.

[SwissBoy2098]

Unfortunately not. Sorry, I uploaded a bad version earlier.

The reason why it worked there (after changing back to .Value) was because there were no gaps. Somehow the gaps, are the problem (it's a database but I don't have all the necessary data yet that's why the currency column is sometimes empty)

Check out the new version. Any idea what I can do?

Thanks!!!

[SwissBoy2098]

BTW, if you put the currencies and rates in a table, you can reduce your conversion formula to

=IF(F4="","",F4/VLOOKUP(A4,Rates!$A$1:$B$6,2,FALSE))

which as well as being simnpler, means it is simpler to change the rates.

Thanks for the tip, I was almost positive that there has to be a less barbaric way haha

[Bob Phillips]

Here's a simple solution



Sub AVG()

Dim Energy As Double
Dim Water As Double
Dim Materials As Double

Dim i As Integer

Dim AverageE As Integer
Dim AverageM As Integer
Dim AverageW As Integer

Energy = 0
i = 4

Average = 0

Do Until Worksheets("Portfolio Companies").Cells(i, 2).Value = ""

If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Energy") Then
Energy = Energy + Val(Worksheets("Portfolio Companies").Cells(i, 7).Value)
If (Worksheets("Portfolio Companies").Cells(i, 7).Value <> "") Then
AverageE = AverageE + 1
End If
End If

If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Materials") Then
Materials = Materials + Val(Worksheets("Portfolio Companies").Cells(i, 7).Value)
If (Worksheets("Portfolio Companies").Cells(i, 7).Value <> "") Then
AverageM = AverageM + 1
End If
End If

If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Water") Then
Water = Water + Val(Worksheets("Portfolio Companies").Cells(i, 7).Value)
If (Worksheets("Portfolio Companies").Cells(i, 7).Value <> "") Then
AverageW = AverageW + 1
End If
End If

i = i + 1

Loop

Worksheets("VBA").Cells(3, 7).Value = Energy / (AverageE)
Worksheets("VBA").Cells(4, 7).Value = Materials / (AverageM)
Worksheets("VBA").Cells(5, 7).Value = Water / (AverageW)

End Sub

[SwissBoy2098]

You're the man! Thanks dude

And good luck for the World Cup, we're playing each other :)

[Bob Phillips]

Not much difference in the rankings either, Swiitzerland 24, Chile 18. I am predicting 2-0 to Chile :)