Assistance with checking cell length for a range of cells with VBA

Likes Likes:  0
Results 1 to 10 of 10

Thread: Assistance with checking cell length for a range of cells with VBA

  1. #1
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Assistance with checking cell length for a range of cells with VBA

     
    Hello, I'm trying to write some code to loop through a range of cells and check each cell's length and then perform a function based on that. Here is what I have so far:

    Code:
    Sub CopyTranspose()
    
    textlen As Integer, rng As Range, cell As Range
    
    textlen = 36
    
    
    
    With ThisWorkbook.Worksheets("PIF>BATCH")
    
    Set rng = Range("D3", "H59")
    
    
    For Each cell In rng
    
    
    If Len(cell.Value) >= textlen Then
        Range("cell").HorizontalAlignment = xlFill
    Else
        Range("cell").HorizontalAlignment = xlCenter
    End If
    Next cell
    End With
    
    End Sub
    I am getting a Runtime 1004 error "method 'range' of object '_Global' failed".

    I am trying to muddle my way through things but I'm just not doing well it seems today.

    I want to loop through each cell in the range of B3:H59 and check each cell length, if it is greater than 36 then change the Horizontal Alignment to be "Fill" if it is 36 or shorter set it to "Center".

    Thanks,
    Phil

  2. #2
    Board Regular iggydarsa's Avatar
    Join Date
    Jun 2005
    Location
    One of the Blue States
    Posts
    1,637
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    Change Set rng = Range("D3", "H59")
    to Set rng = Range("D3:H59")

  3. #3
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    I had that also, and I get the same error.

  4. #4
    Board Regular iggydarsa's Avatar
    Join Date
    Jun 2005
    Location
    One of the Blue States
    Posts
    1,637
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    Code:
    Sub CopyTranspose()
        Dim textlen As Integer, rng As Range, cell As Range
        textlen = 36
        With ThisWorkbook.Worksheets("PIF>BATCH")
            Set rng = .Range("D3:H59")
            For Each cell In rng
                If Len(cell.Value) >= textlen Then
                    cell.HorizontalAlignment = xlFill
                Else
                    cell.HorizontalAlignment = xlCenter
                End If
            Next cell
        End With
    End Sub
    Last edited by iggydarsa; Dec 7th, 2017 at 05:34 PM. Reason: clean up code

  5. #5
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    Thanks that works.

    How can I update this to only check cell's that aren't blank and for a larger range, so it runs more efficiently? Say the range was B7:BF6000

    And is it possible to change textlen to change based upon the columnwidth of the cell the loop is in rather than being static?
    Last edited by philwojo; Dec 7th, 2017 at 05:58 PM.

  6. #6
    Board Regular iggydarsa's Avatar
    Join Date
    Jun 2005
    Location
    One of the Blue States
    Posts
    1,637
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    Code:
    Sub CopyTranspose()
        Dim textlen As Integer, rng As Range, cell As Range
        textlen = 36
        With ThisWorkbook.Worksheets("PIF>BATCH")
            Set rng = .Range("B7:BF6000")
            For Each cell In rng
                If Len(cell.Value) >= textlen Then
                    cell.HorizontalAlignment = xlFill
                ElseIf Len(cell.Value) > 0 Then
                    cell.HorizontalAlignment = xlCenter
                End If
            Next cell
        End With
    End Sub

  7. #7
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    Thanks, I knew how to update the range, that I have no problem with.

    I appreciate the extra ElseIf part, but it still seems to go through each of those cells so it kind of bogs down the workbook for a short bit.

    This works, and it is better than what I had, totally, and I thank you for that, but is there a way to make it run quicker still, if not that is fine, just asking.

  8. #8
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    So I'm trying this and it isn't working, the part in red and underlined in the code.

    Code:
    With ThisWorkbook.Worksheets("PIF>BATCH")
    
        Set rng = .Range("B7:B6000")
            
        For Each cell In rng
    
    
    cnum = Range("rng").Columnwidth
    textlen = cnum
            
        If Len(cell.Value) >= textlen Then
            cell.HorizontalAlignment = xlFill
        ElseIf Len(cell.Value) > 0 Then
            cell.HorizontalAlignment = xlCenter
        End If
        Next cell
    End With
    How can I set texlen to be the Width of the column it is checking at the time from the range?

  9. #9
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

    Anyone have any feedback for me on this one?

    I changed it to be
    Code:
    set cnum = Range("rng").ColumnWidth
    But that made no difference.

  10. #10
    Board Regular
    Join Date
    May 2013
    Location
    Downers Grove, IL
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Assistance with checking cell length for a range of cells with VBA

      
    Ok, I got it working, here is what I ended up with:

    Code:
    With ThisWorkbook.Worksheets("PIF>Batch")
    
        Set rng = .Range("B7:BF6000")
            
        For Each cell In rng
    
    
    textlen = cell.ColumnWidth
            
        If Len(cell.Value) >= textlen Then
            cell.HorizontalAlignment = xlFill
        ElseIf Len(cell.Value) > 0 Then
            cell.HorizontalAlignment = xlCenter
        End If
        Next cell
    End With
    It probably can be faster still but this is at least doing what I need it to do.

    Thanks again to everyone on the boards for helping along the way.

    Phil

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
  •  

 

 
DMCA.com