VBA: Trying to pass Cells to Function Range parameters but is passing values instead

ez08mba

Board Regular
Joined
Nov 1, 2011
Messages
225
Office Version
  1. 2016
Platform
  1. Windows
This is a small part of a bigger project which iterates through spreadsheets using Cells and Row and Column values. It is easier to manage than working with Ranges and String values (e.g. "A1"). In the example below, I'm trying to pass the Cells as ranges, and then the method will evaluate the ranges for content and formatting. This is a test which will be the template to create multiple functions which evaluate the ranges for different types of content and formatting.

Problem:
I cannot get my Cells references to pass as a Range but instead just passes the values to a function. As a result, the Range/Cells methods fail because they are actual values and not ranges/cells. This seems simple, I'm not sure what I'm doing wrong.

Trying to do:
I"m trying simply pass the Range to the function then use Range/Cells methods to evaluate the content and formatting of each range against another range for validation.

What have I tried:
I've tried each of these and it just passes the value of the ranges. It doesn't pass the range as a whole so-to-speak.

Set rngA = wsTemplate.Range(Cells(r, 1).Address)
Set rngB = wsContract.Range(Cells(r, 1).Address)

Set rngA = wsTemplate.Cells(r, 1)
Set rngB = wsContract.Cells(r, 1)

Code:
Option Explicit
Public wsTemplate As Worksheet
Public wsContract As Worksheet


Sub Main()
    Set wsTemplate = ThisWorkbook.Worksheets("Template")
    Set wsContract = ThisWorkbook.Worksheets("Contract")
    
    Dim rw As Integer
    Dim co As Integer
    Dim rngA As Range
    Dim rngB As Range
    
    
    For r = 1 To 15
        Set rngA = wsTemplate.Range(Cells(r, 1).Address)
        Set rngB = wsContract.Range(Cells(r, 1).Address)
    
        If CellEmpty(rngA, rngB) = False Then
            MsgBox rngA.Address & " is not fully cleared of content and formatting"
        End If
    
    Next
End Sub


Function CellEmpty(rngT As Range, rngC As Range)
    ' Check for value, border, formula or formatting
    If IsEmpty(rngT) <> IsEmpty(rngC) Or rngT.Borders.Count <> rngC.Borders.Count Or rngT.hasformua <> rngC.HasFormula Or _
        rngT.MergeCells <> rngC.MergeCells Then
        CellEmpty = False
    Else
        CellEmpty = True
    End If
    
End Function
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
The only problem with your code is a typo in the Function, have a look at the two HasFormula parts.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,974
Messages
6,175,739
Members
452,667
Latest member
vanessavalentino83

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top