Results 1 to 4 of 4

Thread: VBA: Trying to pass Cells to Function Range parameters but is passing values instead
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    181
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by ez08mba; Sep 20th, 2019 at 10:37 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    The only problem with your code is a typo in the Function, have a look at the two HasFormula parts.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Posts
    181
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Fluff View Post
    The only problem with your code is a typo in the Function, have a look at the two HasFormula parts.
    80/20 rule, LOL! Thanks!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,177
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

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

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •