By using VBA how do i compare values

Question

VBA code

   Sub loopchange()
 'select the blank column
   Dim level As String

   Range("AF2").Select
   Do
   'move to A2 cell'
   ActiveCell.Offset(0, -31).Select

   'find out which is level2
  If (ActiveCell.Value = "..2") Then

'move to column AD
ActiveCell.Offset(0, 28).Select

'Store the first 4 digit for level 2
level = Left(ActiveCell.Value, 4)

'Move to the column A3
ActiveCell.Offset(1, -28).Select 

   End If 

   MsgBox Left(ActiveCell.Value, 4)
'compare whether it's level 3(VBA don't detect this)
If (ActiveCell.Value = "...3") Then
    'move to the column AD
    ActiveCell.Offset(0, 28).Select
    'compare the stored first 4 digit in level 2 known as level to first
    '4 digit of current cell
    If (level = Left(ActiveCell.Value, 4)) Then
        'move to column AF
        ActiveCell.Offset(0, 2).Select
        'input the word NO CTH
        ActiveCell.Value = "No CTH"
    End If

   End If
Loop Until IsEmpty(ActiveCell.Offset(0, -31))
End Sub

My rationale is to compare level 2(..2) first four digit to level 3(...3) first four digit and input the world "No CTH" into column AF3 using a Do loop.

(PS my code stuck at " If (ActiveCell.Value = "...3") Then" the vba code doesn't run this and went straight to the End if statement)

Please post your code as text, not an image, so people can copy it instead of typing everything again. Also, indent your code properly, so it becomes easier to read and more obvious to see nested statements. — teylyn, Jun 3, 2016 at 0:54
Step through the code and determine what the ActiveCell.Value is in the yellow line. Is it what you expect? — teylyn, Jun 3, 2016 at 0:56
Thanks for your prompt reply @teylyn as I'm quite new to excel vba when stepping through the code do you mean pressing f8 at the line? if it is than I have done it the code went straight to the second "End if" than the third "End if" which its not suppose to — Ky TySon, Jun 3, 2016 at 1:10
I've removed all the spaces and indented your code. That's a lot more readable. — teylyn, Jun 3, 2016 at 2:46

teylyn · Accepted Answer · 2016-06-03 02:45:22Z

1

Step through the code with F8. When you get to the critical line of code, hover over the "ActiveCell.Value" and see the pop up. Is the value of the active cell what you expect?

Your code seems to be running fine, but the data may be wrong.

answered Jun 3, 2016 at 2:45

teylyn

22.8k2 gold badges41 silver badges54 bronze badges

@telylyn I tried hover my mouse over it show "...3" apparently the value matches but when I press f8 again it still proceed toward the End if Statement... instead of going to "ActiveCell.Offset(0,29).Select"..
– Ky TySon
Jun 6, 2016 at 9:31
Check again. Special (invisible) characters in the cell, leading or trailing blanks? If the value matches, the IF statement will go to the next command. If it goes to the EndIF then the value does not match.
– teylyn
Jun 6, 2016 at 20:46

Add a comment |

Ky TySon · Accepted Answer · 2016-06-09 01:37:10Z

Sub loopchange()

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim level As Integer
Range("AF3").Select
Do

ActiveCell.Offset(0, -31).Select
If ((Right(ActiveCell.Value, 1)) = 2) Then
    ActiveCell.Offset(0, 28).Select
    level = Left(ActiveCell.Value, 4)
    ActiveCell.Offset(1, -28).Select
End If
If ((Right(ActiveCell.Value, 1)) = 3) Then
    ActiveCell.Offset(0, 28).Select
       If (level = Left(ActiveCell.Value, 4)) Then
            ActiveCell.Offset(0, 3).Select
            ActiveCell.Value = "No CTH"
            ActiveCell.Offset(0, -3).Select
       ElseIf (Levels <> Left(ActiveCell.Value, 4)) Then
            ActiveCell.Offset(0, 3).Select
            ActiveCell.Value = ""
            ActiveCell.Offset(0, -3).Select
        End If
ElseIf ((Right(ActiveCell.Value, 1)) <> 2 Or (Right(ActiveCell.Value, 1)) <> 3) Then
ActiveCell.Offset(0, 28).Select
End If

ActiveCell.Offset(1, 3).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -31))

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

@ejbytes I solved it already.Thanks a lot for your guidances for this few days :D Have a nice day

Ky TySon · Accepted Answer · 2016-06-08 06:58:21Z

0

  
 Sub loopchange()
   'select the blank column
   Dim level As String

   Range("AF2").Select
   Do
     'move to A2 cell'
     ActiveCell.Offset(0, -31).Select

     'find out which is level2

     MsgBox ("cell = ..2?" & (ActiveCell.Value = "..2"))
     If (ActiveCell.Value = "..2") Then

       'move to column AD
       ActiveCell.Offset(0, 28).Select

       'Store the first 4 digit for level 2
       level = Left(ActiveCell.Value, 4)
       MsgBox ("Levels is set to: " & level)

       'Move to the column A3

       '**MOVE DOWN?**
       ActiveCell.Offset(1, -28).Select 

     End If 

     MsgBox ("ColA = " & (Left(ActiveCell.Value, 4)))

     'compare whether it's level 3(VBA don't detect this)


     MsgBox ("cell = ...3?" & (ActiveCell.Value = "...3"))
     If (ActiveCell.Value = "...3") Then
       'move to the column AD
       ActiveCell.Offset(0, 28).Select
       'compare the stored first 4 digit in level 2 known as level to first
       '4 digit of current cell

       MsgBox (level & "=" & Left(ActiveCell.Value, 4) & "? " & (level = Left(ActiveCell.Value, 4)))  
       If (level = Left(ActiveCell.Value, 4)) Then
         'move to column AF
         ActiveCell.Offset(0, 3).Select

         ActiveCell.Value = "No CTH"
       End If
    End If

   'Move down one row.
   ActiveCell.Offset(1, 0).Select

  Loop Until IsEmpty(ActiveCell.Value)
End Sub

edited Jun 8, 2016 at 6:58

Ky TySon

1791 silver badge13 bronze badges

answered Jun 8, 2016 at 4:36

ejbytes

2,0303 gold badges15 silver badges25 bronze badges

I added msgbox's for debugging purposes and lined up the code a little.
– ejbytes
Jun 8, 2016 at 4:39
Did you try it out with the message boxes I added?
– ejbytes
Jun 8, 2016 at 4:52
I tried the method you show me. when I step thru the code and when it reaches this line "MsgBox ("cell = ...3?" & (ActiveCell.Value = "...3"))" the msg box prompt me msg "mycell...3?false" what can I do to correct this
– Ky TySon
Jun 8, 2016 at 4:54
Make sure you are at the correct OFFSET. 28 or 29? MsgBox the cell value to make sure you are even looking at the right cell. That's what I mean by "logic" and "unreachable code". You are pointing to the wrong cell and comparing something contrary to your expectations.
– ejbytes
Jun 8, 2016 at 4:59
1

MsgBox ("cell = ...3?" & (Left(ActiveCell.Value, 4))) If ((Left(ActiveCell.Value, 4)) = "...3") Then ActiveCell.Offset(0, 28).Select I have check the value for "(Left(ActiveCell.Value, 4))" apparently it's "...3" however when I use " If ((Left(ActiveCell.Value, 4)) = "...3") , it will went to the end if statement @ejbytes
– Ky TySon
Jun 8, 2016 at 5:35

| Show 11 more comments

Stack Exchange Network

By using VBA how do i compare values

3 Answers 3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
microsoft-excel-2010
vba
.

Linked

Hot Network Questions

By using VBA how do i compare values

3 Answers 3

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged microsoft-excel-2010vba.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
microsoft-excel-2010
vba
.