Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
I'm still not sure what you're trying to do. Your original data looked something like: A;11;21;31;1 B;12;22;32;2 C;13;23;33;3 D;14;24;34;4 E;15;25;35;5 F;16;26;36;6 G;17;27;37;7 H;18;28;38;8 I;19;29;39;9 You split it into separate columns, using ; as a delimeter.. Now you're saying that you want to move the 19 from the last line in column B to column C of the first line? Then the 18 from Column B in the second to last line, to the second line of column C? I'm sure I can do that with a loop... but is that what you mean? What do you want to the do with the value already in column C? Paul From: Jorge Marques leote.w...@gmail.com To: excel-macros@googlegroups.com Sent: Sun, February 13, 2011 10:54:40 AM Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns I have names from cell B1 to cell B1000, but sometimes i need to pass the B1000 to C1 B999 to C2 and so on, i don´t know if this is a stupid question lol, because i can do it by putting a filter in it in A cell but i was wondering if there´s a tool ou way to do this! 2011/2/12 Paul Schreiner schreiner_p...@att.net I'm not sure what you mean by the last A goes to the first B. Can you give an example? Paul From: Jorge Marques leote.w...@gmail.com To: excel-macros@googlegroups.com Sent: Sat, February 12, 2011 9:47:21 AM Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
OT Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
This is Off Topic (I put OT in the title) Technically, to keep from hijacking a thread if you wish to continue this discussion, we ought to start a separate topic Or take it off-line (email between the two of us) However, since others might be interested, I'll mention it here... You CAN load a VBA array from a two dimensional range. For example, Data like: A B C 101258 PIPE BUSHNG 16 219255 DRILL CHUCK 14 221280 ABRAS CLOTH 11 233553 SPOT DRILL 12 241280 HAND FILE 15 328982 GRINDING WHEEL 15 Can be loaded into a VBA array using: Dim StrArray StrArray = Range(A1:C6).Value the result will be: StrArray(1,1) = 101258 StrArray(1,2) = PIPE BUSHING StrArray(1,3) = 16 and so on. Curiously, you CANNOT declare the array initially as: Dim StrArray(10,20) and then exptect load it with: Strarray = Range(A1:C6).value This technique is essentially ReDimensioning the variable. as in: You CAN: Dim TempVar ReDim TempVar(20) But you CANNOT: Dim TempVar(10) ReDim TempVar(20) Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Sat, February 12, 2011 7:02:36 PM Subject: RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns Hi Paul, Very helpful and clearly explained, thank-you. Can we use this, or something similar, to load a range (say A1:A20) into an array without using a loop? Sorry, but my knowledge of arrays in VBA is very limited. Regards - Dave. Date: Sat, 12 Feb 2011 09:20:50 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns To: excel-macros@googlegroups.com It's like the text-to-columns in Excel 2007. If you have a string: The Quick Brown Fox jumped over the lazy dog and you wanted to store the words into an array, If you're using Option Explicit (recommended) you have to declare the variable: Dim StrArray (but don't define an array SIZE) Next, split the string using a space ( ) as delimeter: StrArray = Split(The Quick Brown Fox jumped over the lazy dog, ) or: Str = (The Quick Brown Fox jumped over the lazy dog StrArray = Split(Str, ) The array is now: StrArray(0) = The Strarray(1) = Quick Strarray(2) = Brown Strarray(3) = Fox Strarray(4) = jumped Strarray(5) = over Strarray(6) = the Strarray(7) = lazy Strarray(8) = dog and of course ubound(StrArray) gives the upper bound of the array (8). I wrote a function LONG ago using this to accomplish what is now done with txt-to-columns in Excel2007! hope this helps, Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Sat, February 12, 2011 3:17:14 AM Subject: RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
Yes exactly like you demonstrated :), sorry for my poor explanation, i can do it if i separate first to columns, but can it be done all at the same time? 2011/2/14 Paul Schreiner schreiner_p...@att.net I'm still not sure what you're trying to do. Your original data looked something like: A;11;21;31;1 B;12;22;32;2 C;13;23;33;3 D;14;24;34;4 E;15;25;35;5 F;16;26;36;6 G;17;27;37;7 H;18;28;38;8 I;19;29;39;9 You split it into separate columns, using ; as a delimeter.. Now you're saying that you want to move the 19 from the last line in column B to column C of the first line? Then the 18 from Column B in the second to last line, to the second line of column C? I'm sure I can do that with a loop... but is that what you mean? What do you want to the do with the value already in column C? Paul -- *From:* Jorge Marques leote.w...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Sun, February 13, 2011 10:54:40 AM *Subject:* Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns I have names from cell B1 to cell B1000, but sometimes i need to pass the B1000 to C1 B999 to C2 and so on, i don´t know if this is a stupid question lol, because i can do it by putting a filter in it in A cell but i was wondering if there´s a tool ou way to do this! 2011/2/12 Paul Schreiner schreiner_p...@att.net I'm not sure what you mean by the last A goes to the first B. Can you give an example? Paul -- *From:* Jorge Marques leote.w...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Sat, February 12, 2011 9:47:21 AM *Subject:* Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
I have names from cell B1 to cell B1000, but sometimes i need to pass the B1000 to C1 B999 to C2 and so on, i don´t know if this is a stupid question lol, because i can do it by putting a filter in it in A cell but i was wondering if there´s a tool ou way to do this! 2011/2/12 Paul Schreiner schreiner_p...@att.net I'm not sure what you mean by the last A goes to the first B. Can you give an example? Paul -- *From:* Jorge Marques leote.w...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Sat, February 12, 2011 9:47:21 AM *Subject:* Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns
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(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
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(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
It's like the text-to-columns in Excel 2007. If you have a string: The Quick Brown Fox jumped over the lazy dog and you wanted to store the words into an array, If you're using Option Explicit (recommended) you have to declare the variable: Dim StrArray (but don't define an array SIZE) Next, split the string using a space ( ) as delimeter: StrArray = Split(The Quick Brown Fox jumped over the lazy dog, ) or: Str = (The Quick Brown Fox jumped over the lazy dog StrArray = Split(Str, ) The array is now: StrArray(0) = The Strarray(1) = Quick Strarray(2) = Brown Strarray(3) = Fox Strarray(4) = jumped Strarray(5) = over Strarray(6) = the Strarray(7) = lazy Strarray(8) = dog and of course ubound(StrArray) gives the upper bound of the array (8). I wrote a function LONG ago using this to accomplish what is now done with txt-to-columns in Excel2007! hope this helps, Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Sat, February 12, 2011 3:17:14 AM Subject: RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
I'm not sure what you mean by the last A goes to the first B. Can you give an example? Paul From: Jorge Marques leote.w...@gmail.com To: excel-macros@googlegroups.com Sent: Sat, February 12, 2011 9:47:21 AM Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns
Hi Paul, Very helpful and clearly explained, thank-you. Can we use this, or something similar, to load a range (say A1:A20) into an array without using a loop? Sorry, but my knowledge of arrays in VBA is very limited. Regards - Dave. Date: Sat, 12 Feb 2011 09:20:50 -0800 From: schreiner_p...@att.net Subject: Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns To: excel-macros@googlegroups.com It's like the text-to-columns in Excel 2007. If you have a string: The Quick Brown Fox jumped over the lazy dog and you wanted to store the words into an array, If you're using Option Explicit (recommended) you have to declare the variable: Dim StrArray (but don't define an array SIZE) Next, split the string using a space ( ) as delimeter: StrArray = Split(The Quick Brown Fox jumped over the lazy dog, ) or: Str = (The Quick Brown Fox jumped over the lazy dog StrArray = Split(Str, ) The array is now: StrArray(0) = The Strarray(1) = Quick Strarray(2) = Brown Strarray(3) = Fox Strarray(4) = jumped Strarray(5) = over Strarray(6) = the Strarray(7) = lazy Strarray(8) = dog and of course ubound(StrArray) gives the upper bound of the array (8). I wrote a function LONG ago using this to accomplish what is now done with txt-to-columns in Excel2007! hope this helps, Paul From: Dave Bonallack davebonall...@hotmail.com To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Sat, February 12, 2011 3:17:14 AM Subject: RE: $$Excel-Macros$$ Macro issue passing values from Cell to columns 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(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
Curious... Actually, the Activecell object is not necessary. I changed it to: If Range(Ai) = 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
Re: $$Excel-Macros$$ Macro issue passing values from Cell to columns
While condition '-- your macro Wend condition is an expression that evaluate to TRUE or FALSE Ex: yr data are in cell B4-down. i = 1 With Activesheet.Range(B4) While Len(.cell(i,1) 0 .cell(i,1).texttocolumns destination:=.cell(i,2), '-- etc -- i = i + 1 Wend End with On 2/10/11, Jorge Marques leote.w...@gmail.com wrote: 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
