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