Error with calling Format sub

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
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
Click to expand...

Inadvertently posted before I was done with explanation.

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

I need the formatting and alignment after Sub CryptoLine_Fix() runs because this sub is working with imported text and in blue font color and not aligned as I want.

Thanks,
Howard
 
Hi Howard,

Am Wed, 26 Jun 2013 17:12:10 -0700 (PDT) schrieb Howard:
If I call Sub Align_Auto_Font from Sub CryptoLine_Fix() I get error 400 and no cell formatting.

I need the formatting and alignment after Sub CryptoLine_Fix() runs because this sub is working with imported text and in blue font color and not aligned as I want.
Click to expand...

for me it works fine. Are both macros in a standard module?

There is no need to select in Align_Auto_Font if the reference is
correct:
Sub Align_Auto_Font()
With Range("AC1:BB40")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Range("AC1:BB40").Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End Sub


Regards
Claus Busch
 
Hi Howard,



Am Wed, 26 Jun 2013 17:12:10 -0700 (PDT) schrieb Howard:






for me it works fine. Are both macros in a standard module?



There is no need to select in Align_Auto_Font if the reference is

correct:

Sub Align_Auto_Font()

With Range("AC1:BB40")

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Range("AC1:BB40").Font

.ColorIndex = xlAutomatic

.TintAndShade = 0

End With

End Sub





Regards

Claus Busch
Click to expand...

Here is what I have in Module 1
The sheet name is "Crypto Boogie"
I get a post run error 1004 on the text to column lines and the called font/formatting code works ok except on column AC.

There are some commented out notes in each sub.

Howard

Option Explicit

Sub CryptoLine_Fix()
Dim nRow As Range
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

' run time error 1004 after code runs on 'text to columns lines'
' after working correctly
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()

' Works fine if run from vb editor.
' If called from code above, then in column AC only,
' there are some blue fonts and all are not xlCenter or xlBottom
' All other cells are fine

With Range("AC1:BB40")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

With Range("AC1:BB40").Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End Sub
 
Hi Howard,

Am Thu, 27 Jun 2013 00:03:20 -0700 (PDT) schrieb Howard:
Here is what I have in Module 1
The sheet name is "Crypto Boogie"
I get a post run error 1004 on the text to column lines and the called font/formatting code works ok except on column AC.

There are some commented out notes in each sub.
Click to expand...

I guess there is an error with your handling of Range("CrypyoLine").
When you run it step by step in VBA-Editor you have at least no entry in
column A.
Try:
i = 1
With Sheets("Crypto Boogie")
For Each c In Range("CrypyoLine")
Str1 = ""
Str1 = IIf(Len(c) > maxLen, Left(c, _
InStrRev(c, " ", maxLen)), c)
.Cells(i, 1).Offset(3, 0) = Replace(c, Str1, "")
.Cells(i, 1) = Str1
i = i + 1
Next

If that is not the solution, please send me your workbook.


Regards
Claus Busch
 
Hi Howard,



Am Thu, 27 Jun 2013 00:03:20 -0700 (PDT) schrieb Howard:








I guess there is an error with your handling of Range("CrypyoLine").

When you run it step by step in VBA-Editor you have at least no entry in

column A.

Try:

i = 1

With Sheets("Crypto Boogie")

For Each c In Range("CrypyoLine")

Str1 = ""

Str1 = IIf(Len(c) > maxLen, Left(c, _

InStrRev(c, " ", maxLen)), c)

.Cells(i, 1).Offset(3, 0) = Replace(c, Str1, "")

.Cells(i, 1) = Str1

i = i + 1

Next



If that is not the solution, please send me your workbook.





Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Hi Claus

Here is a link the workbook. Sorta works except for the warts I have pointe out

Thanks for taking a look.

https://www.dropbox.com/s/eldkj3rjds7tc2e/Crypto Boogie Drop Box.xlsm

Howard
 
Hi Howard,

Am Thu, 27 Jun 2013 01:10:54 -0700 (PDT) schrieb Howard:
https://www.dropbox.com/s/eldkj3rjds7tc2e/Crypto Boogie Drop Box.xlsm
Click to expand...

your range "CrypyoLine" is not filled completely. So if you run through
each c of this range you have empty cells at least.
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Crypto Boogie"
Now there is a IF-Statement
If c <>""
You could also make the range "CrypyoLine" dynamic and work without that
IF-Statement.


Regards
Claus Busch
 
Hi Howard,



Am Thu, 27 Jun 2013 01:10:54 -0700 (PDT) schrieb Howard:






your range "CrypyoLine" is not filled completely. So if you run through

each c of this range you have empty cells at least.

Please look here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for the workbook "Crypto Boogie"

Now there is a IF-Statement

If c <>""

You could also make the range "CrypyoLine" dynamic and work without that

IF-Statement.





Regards

Claus Busch
Click to expand...

Well, that certainly cleaned things up nicely.

I'm mighty grateful.

Thanks.

Regards,
Howard
 
Back
Top