Excel VBA Compare equal values that don't match !!

  Heefie 18:36 27 Dec 04
Locked

Me again, with some more crap code that almost does as I want !!! I have 2 variables defined as Variants but when I compare them, one is in quotes and one is not, so they never match !!!

This is not ALL of the code, just the relevant bits ... I think !!!

Code is as follows :

Dim MyCell As String
Dim MyRow As Integer
Dim MyDataRec As String
Dim MyTempInteger As Integer
Dim OldBalance As Variant
Dim NewBalance As Variant
Dim AddAmount As Variant
Dim CalcBalance As Variant
Dim ErrorCount As Integer
Dim TurnFee As Variant

TurnFee = 2.25

If Left$(MyDataRec, 11) = "AccountBal=" Then
MyCell = "F" & MyRow
Range(MyCell).Activate
AddAmount = ActiveCell.Value
If AddAmount = "" Then
AddAmount = 0
End If
MyCell = "E" & MyRow
Range(MyCell).Activate
OldBalance = ActiveCell.Value
MyTempInteger = Len(MyDataRec) - 11
NewBalance = Right$(MyDataRec, MyTempInteger)
CalcBalance = OldBalance + AddAmount – TurnFee
‘ At this point CalcBalance =19.78 & NewBalance = “19.78” so they don’t match !
If CalcBalance = NewBalance Then
ActiveCell = NewBalance
MyCell = "E" & MyRow
Range(MyCell).Activate
ActiveCell = ""
Else
ErrorCount = ErrorCount + 1
‘ Can I also point out that ErrorCount is still zero … what have I done wrong ?
MsgBox prompt:="Problem with Cash Balance - " & OldBalance & "/" & NewBalance, Title:="Program Error " & ErrorCount & " ...", Buttons:=vbCritical
End If
End If

I'll take a guess that the Command "NewBalance = Right$(MyDataRec, MyTempInteger)" is causing the Variable definition to be changed ... any ideas why & what I have to do to rectify this ?

Any ideas on the ErrorCount = ErrorCount + 1 not actually doing anything will be gratefully received too !!!

Thanks in advance for any and all help !!!

  Heefie 18:38 27 Dec 04

I spent ages structuring this and then cut & pasted it ... what do I have to do to keep the formatting ?!?!?

  VoG II 18:39 27 Dec 04

Double space it. Add a blank line after each line of code, please.

  VoG II 18:40 27 Dec 04

Use

=Val( Right$(MyDataRec, MyTempInteger) )

  Heefie 19:18 27 Dec 04

Dim MyCell As String

Dim MyRow As Integer

Dim MyDataRec As String

Dim MyTempInteger As Integer

Dim OldBalance As Variant

Dim NewBalance As Variant

Dim AddAmount As Variant

Dim CalcBalance As Variant

Dim ErrorCount As Integer

Dim TurnFee As Variant

TurnFee = 2.25

If Left$(MyDataRec, 11) = "AccountBal=" Then

MyCell = "F" & MyRow

Range(MyCell).Activate

AddAmount = ActiveCell.Value

If AddAmount = "" Then

AddAmount = 0

End If

MyCell = "E" & MyRow

Range(MyCell).Activate

OldBalance = ActiveCell.Value

MyTempInteger = Len(MyDataRec) - 11

NewBalance = Right$(MyDataRec, MyTempInteger)

CalcBalance = OldBalance + AddAmount – TurnFee

‘ At this point CalcBalance =19.78 & NewBalance = “19.78” so they don’t match !

If CalcBalance = NewBalance Then

ActiveCell = NewBalance

MyCell = "E" & MyRow

Range(MyCell).Activate

ActiveCell = ""

Else

ErrorCount = ErrorCount + 1

‘ Can I also point out that ErrorCount is still zero … what have I done wrong ?

MsgBox prompt:="Problem with Cash Balance - " & OldBalance & "/" & NewBalance,
Title:="Program Error " & ErrorCount & " ...", Buttons:=vbCritical

End If

End If

  VoG II 19:24 27 Dec 04

NewBalance = Val( Right$(MyDataRec, MyTempInteger))

should sort it by "forcing" NewBalance to be numeric.

  Heefie 19:26 27 Dec 04

... I must owe you about a dozen beers by now !!!

Works perfectly ... any idea why the "ErrorCount = ErrorCount + 1" isn't working ?!?!?

  VoG II 19:33 27 Dec 04

Sorry, I can't immediately see why ErrorCount was not updating.

  VoG II 22:39 27 Dec 04

Some of the above coding is rather good. I'm going to make a few comments and I hope that you do not take them the wrong way.

Using .Activate is excellent; much better than .Select. But you do not need to Activate a cell to add a value to it.


So for example

MyCell = "E" & MyRow

Range(MyCell).Activate

OldBalance = ActiveCell.Value

-----------

OldBalance = Range ("E" & MyRow).Value

would be much faster and save a load of typing.

  Heefie 18:07 30 Dec 04

Awww, I feel kind of embarrassed now ... thanks VoG <blush> !!!

I see your point about the shorter, more concise, code though, I'll incorporate it tomorrow ... the trouble is, I've spent nearly 30 years coding in COBOL & it's very difficult to get out of the IF-THEN-ELSE style of coding !!!

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…