Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Error with calling Format sub
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 536
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,872
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 536
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,872
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 536
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,872
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 536
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
format calling cell RedLars Excel Programming 3 March 24th 08 02:30 PM
calling calendar, get Automation Error Arnold Klapheck Excel Programming 1 November 22nd 06 02:47 AM
calling a subroutine to format date(s) jenelle Excel Programming 7 February 6th 05 09:35 PM
Error 49: Bad .dll Calling Convention Jarrett Sadowsky Excel Programming 1 January 20th 04 07:52 PM
Bad DLL Calling Convention (Error 49) Shaikh Excel Programming 3 November 18th 03 10:18 PM


All times are GMT +1. The time now is 04:06 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement