Subject: Type 13 Error when defining a Columns variable

[MikeZz]

Hi,
I have a routine that pastes an array to a worksheet.
I want to have it call another routine that autosizes all the columns at
once, then if any column widths excede my max, it sets width to max.

Everthing comes to an "Error 13" Wrong Type crash at this line:
Set cols = pasteRange .Columns

I'm not sure if I'm defining the variable wrong or what.

Any help would be great,
Thanks,
MikeZz

Private Sub z20_Set_This_Range_from_Array(rng1Cell As Range, arr() As
Variant, Optional autoFitCols as Boolean)

Dim r, c
Dim cols As Columns
Dim pasteRange As Range
r = UBound(arr, 1) - LBound(arr, 1)
c = UBound(arr, 2) - LBound(arr, 2)

Set pasteRange = Range(rng1Cell, rng1Cell.Offset(r, c))
pasteRange = arr

If IsMissing(autoFitCols) Then
Else
Set cols = pasteRange .Columns
If autoFitCols = True Then _
Call z30_Set_Auto_Column_Size_With_Max(cols, 25)
Set cols = Nothing
End If

End Sub

Sub z30_Set_Auto_Column_Size_With_Max(cols As Columns, cMax)
Dim col As Column

cols.AutoFit

For Each col In cols
If col.Width > cMax Then col.Width = cMax
Next col

End Sub

 

[MikeZz]

Hi Jessen,
Looks a lot cleaner than my method....Thanks!

The only additional fix I had to make was that Err 424 until I changed
"Col.Width" to "Col.ColumnWidth".

I didn't know that there were two widths in the col range property.
Any idea?

Thanks again for the quick help!
MikeZz

 

[Per Jessen]

Hi Mike

Thanks for your reply. I'm glad you made it work though I used the wrong
property. I knew it looked wrong, but didn't test it :-(

The Width property sets an objects (Application, Range, Window) width in
points, but isn't related to the column width.

Regards,
Per