By using VBA how do i compare values
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)
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.
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