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("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
--
----------------------------------------------------------------------------------
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
