Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Populate word in cell based on two columns are matched

0

Hi 

I  try  to  populate  two  words (MATCHED, NOT  MATCHED )  for  cells in  column  G based on  adjacent  cells  for  column B,H , if   the  items  in  adjacent cells  for  column B,H   are  the  same  thing , then  should  populate  word MATCHED   for  adjacent  cell  in  column G   and  if  not  , then  should  populate  word NOT  MATCHED   for  adjacent  cell  in  column G  

this  is  what  I  have  , but  Gives  error  applicaton defined  error  in this  line 

 ElseIf Target.Value <> Target.Offset(, -6) Then Target.Offset(, -1) = "NOT MATCHED"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
       If Not Intersect(Target, Range("h7:h500")) Is Nothing Then
             If Target.Value = Target.Offset(, -6) Then Target.Offset(, -1) = "MATCHED"
             ElseIf Target.Value <> Target.Offset(, -6) Then Target.Offset(, -1) = "NOT MATCHED"
            End If
Application.EnableEvents = True
End Sub
excel
MS Office Version: 2010
posted by: speed (rep: 40) on Fri Dec 23, 2022 at 2:06 pm
Answer
Discuss

Answers

0
Selected Answer

Speed

I think you get that error if the line:

If Target.Value = Target.Offset(, -6) Then Target.Offset(, -1) = "MATCHED"

is True since Target is changed since there was another change but before the code completes.

You need to disable events first as below- where I've tweaked the codea little too:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("h7:h500")) Is Nothing Then
          If Target.Value = Target.Offset(, -6) Then
            Target.Offset(, -1) = "MATCHED"
            Else
            Target.Offset(, -1) = "NOT MATCHED"
          End If
    End If
Application.EnableEvents = True
End Sub
 

Hope this works for you.

posted by: John_Ru (rep: 6152) on Sat Dec 24, 2022 at 2:11 am
Discuss

Discussion

Hi John,
that'  works  greatly !
thank  you  so  much
speed (rep: 40) Dec 24, '22 at 4:36 am
Thanks for selecting my Answer,  Speed.

I did think you might might make the first If line starting:

If Target.Cells.Count = 1 And Not Intersect(Target, Range("h7:h500")) Is Nothing Then

and remove the previous line with the IsEmpty test but I guess that's needed if your user picks an empty row (i.e. with nothing in column D).
John_Ru (rep: 6152) Dec 24, '22 at 5:12 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login