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

Open in new window

ASKER CERTIFIED SOLUTION
Panagiotis Toumpaniaris
System Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
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