ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Error with calling Format sub (https://www.excelbanter.com/excel-programming/448930-error-calling-format-sub.html)

Howard
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

Howard
Error with calling Format sub
 
On Wednesday, June 26, 2013 5:05:47 PM UTC-7, Howard wrote:
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

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

Claus Busch
Error with calling Format sub
 
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.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Howard
Error with calling Format sub
 
On Wednesday, June 26, 2013 11:21:51 PM UTC-7, Claus Busch wrote:
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.



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

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

Claus Busch
Error with calling Format 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.

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

Howard
Error with calling Format sub
 
On Thursday, June 27, 2013 12:33:31 AM UTC-7, Claus Busch wrote:
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.



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

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/eldkj3rjds...rop%20Box.xlsm

Howard

Claus Busch
Error with calling Format sub
 
Hi Howard,

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

https://www.dropbox.com/s/eldkj3rjds...rop%20Box.xlsm

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 he
https://skydrive.live.com/#cid=9378A...121822A3%21326
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Howard
Error with calling Format sub
 
On Thursday, June 27, 2013 1:37:48 AM UTC-7, Claus Busch wrote:
Hi Howard,



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



https://www.dropbox.com/s/eldkj3rjds...rop%20Box.xlsm



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 he

https://skydrive.live.com/#cid=9378A...121822A3%21326

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


Well, that certainly cleaned things up nicely.

I'm mighty grateful.

Thanks.

Regards,
Howard


All times are GMT +1. The time now is 03:13 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com