View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Howard Howard is offline
external usenet poster
 
Posts: 536
Default Error with calling Format sub

I can run Sub CryptoLine_Fix() by itself and it works just fine.

I can run Sub Align_Auto_Font by itself and it works just fine.

If I call Sub Align_Auto_Font from Sub CryptoLine_Fix() I get error 400 and no cell for

Option Explicit

Sub CryptoLine_Fix()

Dim CrypyoLine As Range
Dim c As Range
Const maxLen As Integer = 26
Dim Str1 As String
Dim i As Integer

For Each c In Range("CrypyoLine")

c.Copy Range("A1")

For i = 1 To 1
Str1 = ""
Str1 = IIf(Len(Cells(i, 1)) maxLen, Left(Cells(i, 1), _
InStrRev(Cells(i, 1), " ", maxLen)), Cells(i, 1))
Cells(i, 1).Offset(3, 0) = Replace(Cells(i, 1), Str1, "")
Cells(i, 1) = Str1
Next

Columns("A").TextToColumns Destination:=Range("A1"), _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _
Array(25, 1), Array(26, 1)), TrailingMinusNumbers:=True

Range("A1:Z4").Copy Range("AC40").End(xlUp).Offset(3, 0)
Range("A1:Z4").ClearContents
Next

Align_Auto_Font
End Sub

Sub Align_Auto_Font()
Range("AC1:BB40").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End Sub