Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Two values returned as unequal yet code proceeds as if equal

  Asked By: Vernon    Date: Jan 09    Category: MS Office    Views: 799
  

This one has me stumped. From the bottom of a list up I evaluate the
cell in column A to the cell above it. If they match, I proceed to
evaluate the 2nd cell above, whether it matches the first. Everything
works fine if two cells don't match. But when a matching pair is found
(verified by a msgbox) it always says there is a third match...EVEN
THOUGH the two cells being compared are reported in the msgbox as
being unequal. I can't figure out any more direct to validate what VBA
is calling a match! Any ideas?

Do Until ActiveCell.Address = Range("A6130").Address
ThisRange = ActiveCell.Value
NextRange = ActiveCell.Offset(-1, 0).Value
'reset var
dupecount = 0
If ThisRange = NextRange Then
dupecount = 2
MsgBox "2 match found" & vbCrLf _
& NextRange & " " & ThisRange & vbCrLf _
& dupecount
ThisRw = ActiveCell.Row
ThisDate = ActiveCell.Offset(0, 2).Value
NextDate = ActiveCell.Offset(-1, 2).Value
NextMatch = True
GoToRow = ThisRw - 2
'see if third cell matches the first
ThirdRange = ActiveCell.Offset(-2, 0).Value
ThirdDate = ActiveCell.Offset(-2, 2).Value
If ThirdRange = ThisRange Then ThirdMatch = True
dupecount = 3
MsgBox "3 match found" & vbCrLf _
& ThirdRange & " " & ThisRange & vbCrLf _
& dupecount
GoToRow = GoToRow - 1
End If


Share: 

 

5 Answers Found

 
Answer #1    Answered By: Amir Hashmi     Answered On: Jan 09

The main problem seems to be that you have 2 "If" statements and
no "End If" statements. "If" and "End If" must be in pairs. I can't
tell exactly what you want to do, but you may need "ElseIf"
or "Else".

Highlight your "If" statement and press F1 to see the help on this.

Also, you need to close your "Do" with "Loop".

Other Tips: If you haven't already, try using F8 to step through
your code. It is also very helpful to select "View - Locals Window"
to see all your variables while stepping code. Hovering the cursor
over variables also lets you see their value. These are alternatives
to the message box method.

 
Answer #2    Answered By: Jarryd Williams     Answered On: Jan 09

Not all VBA IF statements need an EndIf. Mark's second example is one of
those.

IF condition THEN statement

The statement following the IF is run no matter the condition was.

 
Answer #3    Answered By: Kate Johnston     Answered On: Jan 09

I am used to programming Paradox and am just learning VBA. I am
accustomed to being able to include many instructions between if and
endif. VBA has prompted me to provide endifs so I assumed that applied
to all circumstances. How do I tell when to use endifs and when does
VBA only process only one expression?

BTW- The Do does have a loop and the first if does have an endif. I
just didn't copy those lines because they were further down the code.

 
Answer #4    Answered By: Gavril Bonkob     Answered On: Jan 09

If a line finishes with 'Then', it needs an 'End If' later on.

If a line has something after the 'Then', it executes that code  if the
condition is True, the subsequent line is a normal line not reliant on
the If statement above  it.

The code as it stands won't work very well since the active cell  never
changes, but I suppose you didn't copy those lines either - because
they were further down the code.

 
Answer #5    Answered By: Lula Woods     Answered On: Jan 09

If a line finishes with 'Then', it needs an 'End If' later on.

If a line has something after the 'Then', it executes that code  if the
condition is True, the subsequent line is a normal line not reliant on
the If statement above  it.

The code as it stands won't work very well since the active cell  never
changes, but I suppose you didn't copy those lines either - because
they were further down the code.

 




Tagged:            

 
 
 
 

Related Post