troubleshooting Question

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

Avatar of ABINAYA MOHAN
ABINAYA MOHAN asked on
VBA
2 Comments1 Solution111 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros