Curious...
Actually, the Activecell object is not necessary.
I changed it to:
If Range("A"&i) = "" then exit sub else
and it worked just fine.
BTW:
I changed the loop to:
Application.ScreenUpdating = False
For R = 1 To 1000
If (Range("A" & R).Value = "") Then Exit Sub
StrArray = Split(Range("A" & R).Value, ";")
For C = 0 To UBound(StrArray)
Cells(R, C + 1).Value = StrArray(C)
Next C
Next R
Application.ScreenUpdating = True
and it worked almost instantaneously instead of all the
screen "flashing" that goes on with TextToColumns.
Paul
________________________________
From: Jorge Marques <leote.w...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Thu, February 10, 2011 6:46:37 AM
Subject: $$Excel-Macros$$ Macro issue passing values from Cell to columns
Hi, i have a macro i´ve done, but it it has a problem, when it passes the data
from cells A to various columns it stops no cell 20 in the sheet2 e the another
stops at another cell number, i have the file attached here with the code
below,
can´t seem to discover the error, taking baby steps at this!i think the issue
is
on the "if" statement, but if i take it out, it does the macro well but
indicates error in the end!
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+p
'
Dim i As Double
i = 1
While ActiveSheet.Select
If ActiveCell.Range("A" & i) = "" Then Exit Sub Else
Range("A" & i).Select
Selection.TextToColumns Destination:=Range("A" & i), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=";", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
i = i + 1
Wend
End Sub
Thank you very much for your help--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
