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)
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: