View Full Version : Weird Type Mismatch Problem (Cell Formula vs. Cell Value)
SwissBoy2098
06-08-2010, 12:24 AM
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
06-08-2010, 01:05 AM
I cannot replicate the problem here.
What Excel/OS version are you using? Can you post a workbook?
SwissBoy2098
06-08-2010, 01:15 AM
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
06-08-2010, 01:20 AM
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
06-08-2010, 01:32 AM
Here you go!
Thanks!
Bob Phillips
06-08-2010, 01:36 AM
Your real workbook is using the .Formula property, not .Value. Change it and it works fine.
Bob Phillips
06-08-2010, 01:42 AM
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
06-08-2010, 01:49 AM
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
06-08-2010, 01:50 AM
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
06-08-2010, 02:02 AM
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
06-08-2010, 02:11 AM
You're the man! Thanks dude
And good luck for the World Cup, we're playing each other :)
Bob Phillips
06-08-2010, 02:14 AM
Not much difference in the rankings either, Swiitzerland 24, Chile 18. I am predicting 2-0 to Chile :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.