Oh ok Paul, i did it that way, because i recorded it first, then defined the
Range("A" & i), but it´s good now, millions thanks, the activecell i did
remember to leave just the range, still taking baby steps in this :)
I forgot to mention, after this i have to switch the cells, the Last A goes
to the first B, like inverting the order, is this possible?thanks
2011/2/12 Dave Bonallack <davebonall...@hotmail.com>
> Hi Paul,
> I'm really interested in the part of the macro that I have highlighted
> below (in case the highlighting doesn't travel well, I've marked each line
> with a *)
> I've not seen the 'Split' function before. I looked it up in the Help, but
> still couldn't make sense of it. If you have time, could you please explain
> that part?
> Regards - Dave.
>
> ------------------------------
> Date: Fri, 11 Feb 2011 06:15:45 -0800
> From: schreiner_p...@att.net
> Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to
> columns
>
> To: excel-macros@googlegroups.com
>
> 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
>
> --
>
> ----------------------------------------------------------------------------------
> 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
