Horizontally Merged cells give error while using Vlookup Function in EXCEL VBA

Hello All,

I am using the below Vlookup code with a For loop so that the vlookup runs until it reaches last row in my excel sheet.But it gives error when the for loop reaches a merged cells(Horizontally merged cell). Can someone help me in skipping the merged cells ?

Dim LastRowG As Double, dealer As Variant, DealerNumber As Variant, f As Double
                
        With Balance.Sheets("Líquidas")
               
           LastRowG = .Cells(.Rows.Count, "A").End(xlUp).Row
           
        End With

For f = 7 To LastRowG
           
        dealer = Balance.Sheets("Líquidas").Range("C" & f).Value
        
 'Checking when the column which is being compared is not empty
        If Not IsEmpty(dealer) Then 
'Checking when the column which is beincompared with is not empty
           If Not (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(dealer, RSC.Sheets("OSB").Range("A2:G1000"), 2, False))) Then
             DealerNumber = Application.WorksheetFunction.VLookup(dealer, RSC.Sheets("OSB").Range("A2:G1000"), 2, False)
           Else
             DealerNumber = 0
           End If
        Else
          DealerNumber = 0
        End If
        
        Balance.Sheets("Líquidas").Range("AH" & f).Value = DealerNumber
            
Next f

Open in new window

ABINAYA MOHANAsked:
Who is Participating?
 
Panagiotis ToumpaniarisConnect With a Mentor System EngineerCommented:
you should insert a check like :

if Activecell.Mergecells Then Next f

Open in new window


or something along this line.
Before assigning values to dealer.
0
 
ABINAYA MOHANAuthor Commented:
Thank you! Added the below check

If Garantias.Sheets("Garantia Líquidas").Range("C" & f).MergeCells = False Then
        
        dealer = Garantias.Sheets("Garantia Líquidas").Range("C" & f).Value

Open in new window

It Works!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.