Rabu, 29 Februari 2012

Re: [ExcelVBA] How to convert double to integer

Perfect! Thank you so much!

Dawn Bleuel

On Wed, Feb 29, 2012 at 2:01 PM, Paul Schreiner <schreiner_paul@att.net>wrote:

> **
>
>
>
> try using:
> intResult = int(dblTime)
>
> actually, you could use:
>
> dbltime = dbltime - Int(dbltime)
>
> instead of the "if" statement
>
> but this would only work if dbltime is > 0
>
> Paul
> -----------------------------------------
> "Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can." - John Wesley
> -----------------------------------------
>
> ________________________________
> From: Dawn Bleuel <dlbleuel@gmail.com>
> To: ExcelVBA <ExcelVBA@yahoogroups.com>
> Sent: Wed, February 29, 2012 2:17:54 PM
> Subject: [ExcelVBA] How to convert double to integer
>
>
>
> I need some help with something.
>
> In VBA I have a snippet of code...
>
> If dblTime > 1 Then
> intResult = dblTime
> dblTime = dblTime - intResult
> End If
>
> Works great if the value fed into dblTime is 1.454. The intResult = 1 and
> the dblTime becomes .454
>
> However, if the value of dblTime is 1.569 then the intResult becomes 2. I
> need it to stay at 1. I'm sure there is some kind of convert function that
> I'm not thinking of that will just extract the current whole number, but
> I've come up blank.
>
> Ideally, I want to end up with two variables splitting the whole number
> from the decimal.
>
> Thanks.
>
> --
> Dawn Bleuel
> Word MVP
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>

--
Dawn Bleuel
"*Dedicated to Living Healthier"*
Live Total Wellness
www.LiveTotalWellness.com\dbleuel <http://www.livetotalwellness.com/dbleuel>


[Non-text portions of this message have been removed]

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

READ MORE....

RE: [ExcelVBA] Re: LISTBOX

 

Thanks this will help bog time!
-----Original Message-----
Date: Wednesday, February 29, 2012 7:38:14 am
To: ExcelVBA@yahoogroups.com
From: "Ralph" <ralph.gregory@skaino.co.uk>
Subject: [ExcelVBA] Re: LISTBOX

Hi Derek I am trying to follow this and am a bit lost as to where I put the code...I created a new workbook and renamed a sheet Gages then put some data in etc then I created a user form with a listbox on it but now where does your code go and how do I run it....sorry if these are dumb questions but it is still a new world to me....

--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>
> Hi All
>
> Here is my own code to do what Jim wants :- 
>
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> '   Populate a multicolumn Listbox (with >10 columns) from an Excel range
> '   Derek Turner February 2012
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Private Sub UserForm_Initialize()
> Const FIND_I = "MPR0010"
> Const FIND_J = "T1"
> Const COLUMN_I = 9
> Const COLUMN_J = 10
> Const LISTBOXCOLUMNS = 17
> Const TITLEROW = 1
>
> Dim vSourceArray As Variant, vSubsetArray()

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

Re: [ExcelVBA] How to convert double to integer

 

oops... just saw the last line:
"Ideally, I want to end up with two variables splitting the whole number
from the decimal."

in that case, my first answer works.

of course, you don't even need the "if" construct.

intResult = int(dblTime)

will be 0 if the number is less than 1.

then :
dblTime = dblTime - intResult

would simply subtract 0.

 
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: Paul Schreiner <schreiner_paul@att.net>
To: ExcelVBA@yahoogroups.com
Sent: Wed, February 29, 2012 3:01:44 PM
Subject: Re: [ExcelVBA] How to convert double to integer

 

 try using:
intResult = int(dblTime)

actually, you could use:

dbltime = dbltime - Int(dbltime)

instead of the "if" statement

but this would only work if dbltime is > 0

Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: Dawn Bleuel <dlbleuel@gmail.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wed, February 29, 2012 2:17:54 PM
Subject: [ExcelVBA] How to convert double to integer

 
I need some help with something.

In VBA I have a snippet of code...

If dblTime > 1 Then
intResult = dblTime
dblTime = dblTime - intResult
End If

Works great if the value fed into dblTime is 1.454. The intResult = 1 and
the dblTime becomes .454

However, if the value of dblTime is 1.569 then the intResult becomes 2. I
need it to stay at 1. I'm sure there is some kind of convert function that
I'm not thinking of that will just extract the current whole number, but
I've come up blank.

Ideally, I want to end up with two variables splitting the whole number
from the decimal.

Thanks.

--
Dawn Bleuel
Word MVP

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

Re: [ExcelVBA] How to convert double to integer

 


 try using:
intResult = int(dblTime)

actually, you could use:

dbltime = dbltime - Int(dbltime)

instead of the "if" statement

but this would only work if dbltime is > 0

Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: Dawn Bleuel <dlbleuel@gmail.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wed, February 29, 2012 2:17:54 PM
Subject: [ExcelVBA] How to convert double to integer

 
I need some help with something.

In VBA I have a snippet of code...

If dblTime > 1 Then
intResult = dblTime
dblTime = dblTime - intResult
End If

Works great if the value fed into dblTime is 1.454. The intResult = 1 and
the dblTime becomes .454

However, if the value of dblTime is 1.569 then the intResult becomes 2. I
need it to stay at 1. I'm sure there is some kind of convert function that
I'm not thinking of that will just extract the current whole number, but
I've come up blank.

Ideally, I want to end up with two variables splitting the whole number
from the decimal.

Thanks.

--
Dawn Bleuel
Word MVP

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

Re: [ExcelVBA] How to convert double to integer

 

Hello Dawn

    intResult = Int(dblTime)
    dblTime = dblTime - intResult

No need for the If maybe ? 

Derek Turner +++

>________________________________
> From: Dawn Bleuel <dlbleuel@gmail.com>
>To: ExcelVBA <ExcelVBA@yahoogroups.com>
>Sent: Wednesday, 29 February 2012, 19:17
>Subject: [ExcelVBA] How to convert double to integer
>
>

>I need some help with something.
>
>In VBA I have a snippet of code...
>
>If dblTime > 1 Then
>intResult = dblTime
>dblTime = dblTime - intResult
>End If
>
>Works great if the value fed into dblTime is 1.454. The intResult = 1 and
>the dblTime becomes .454
>
>However, if the value of dblTime is 1.569 then the intResult becomes 2. I
>need it to stay at 1. I'm sure there is some kind of convert function that
>I'm not thinking of that will just extract the current whole number, but
>I've come up blank.
>
>Ideally, I want to end up with two variables splitting the whole number
>from the decimal.
>
>Thanks.
>
>--
>Dawn Bleuel
>Word MVP
>
>[Non-text portions of this message have been removed]
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

[ExcelVBA] How to convert double to integer

 

I need some help with something.

In VBA I have a snippet of code...

If dblTime > 1 Then
intResult = dblTime
dblTime = dblTime - intResult
End If

Works great if the value fed into dblTime is 1.454. The intResult = 1 and
the dblTime becomes .454

However, if the value of dblTime is 1.569 then the intResult becomes 2. I
need it to stay at 1. I'm sure there is some kind of convert function that
I'm not thinking of that will just extract the current whole number, but
I've come up blank.

Ideally, I want to end up with two variables splitting the whole number
from the decimal.

Thanks.

--
Dawn Bleuel
Word MVP

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

Re: [ExcelVBA] Re: LISTBOX

 

Hi Ralph

The code at the moment goes into the userform which contains the Listbox1 control and you run it with the F5 key when the cursor is in the code.

As this is work in progress I am not sure how Jim intends to fire it off. In due course it would be fired by a Macro button or event handler somewhere. 

By the way, when I looked at my own post the format was OK, but in the reply to me (and others) indents in the code look like ">         Â". Also these As (hatted A) are scattered throughout  the text. 
Can anyone explain this or even provide a workround ? I am using Yahoo Mail.  

Regards

Derek Turner +++

>________________________________
> From: Ralph <ralph.gregory@skaino.co.uk>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 29 February 2012, 10:59
>Subject: [ExcelVBA] Re: LISTBOX
>
>

>Hi Derek I am trying to follow this and am a bit lost as to where I put the code...I created a new workbook and renamed a sheet Gages then put some data in etc then I created a user form with a listbox on it but now where does your code go and how do I run it....sorry if these are dumb questions but it is still a new world to me....
>
>--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>>
>> Hi All
>>
>> Here is my own code to do what Jim wants :- 
>>
>>
>> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> '   Populate a multicolumn Listbox (with >10 columns) from an Excel range
>> '   Derek Turner February 2012
>> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>> Private Sub UserForm_Initialize()
>> Const FIND_I = "MPR0010"
>> Const FIND_J = "T1"
>> Const COLUMN_I = 9
>> Const COLUMN_J = 10
>> Const LISTBOXCOLUMNS = 17
>> Const TITLEROW = 1
>>
>> Dim vSourceArray As Variant, vSubsetArray() As Variant
>> Dim vSubsetRow(1 To LISTBOXCOLUMNS), clxSubsetRows As New Collection
>> Dim nSourceRow As Long, nSubsetRow As Long, nSourceRowCount As Long, nListBoxColumn As Long
>> '
>>     vSourceArray = Worksheets("Gages").Range("a1").CurrentRegion.Value
>>     nSourceRowCount = UBound(vSourceArray)
>>     
>>     For nSourceRow = 1 To nSourceRowCount
>>         If nSourceRow = 
>> TITLEROW  _
>>          Or (vSourceArray(nSourceRow, COLUMN_I) = FIND_I _
>>          And vSourceArray(nSourceRow, COLUMN_J) = FIND_J) Then
>>             For nListBoxColumn = 1 To LISTBOXCOLUMNS
>>                 vSubsetRow(nListBoxColumn) = vSourceArray(nSourceRow, nListBoxColumn)
>>             Next nListBoxColumn
>>             clxSubsetRows.Add vSubsetRow()
>>         End If
>>     Next nSourceRow
>>     With clxSubsetRows
>>         ReDim vSubsetArray(1 To .Count, 1 To LISTBOXCOLUMNS)
>>         For nSubsetRow = 1 To .Count
>>             For nListBoxColumn = 1 To LISTBOXCOLUMNS
>>                 vSubsetArray(nSubsetRow, nListBoxColumn) = .Item(nSubsetRow)(nListBoxColumn)
>>             Next nListBoxColumn
>>         Next nSubsetRow
>>     End With
>>     With ListBox1
>>         .ColumnCount = LISTBOXCOLUMNS
>>         .ColumnWidths = "100;80;50;50;50;50;50;100;60;50;50;50;50;50;05;05;50"
>>         .List = vSubsetArray
>>     End With
>> End Sub
>> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>
>> To test this you need to  put random stuff  into a range of 17 columns and as many rows as you like (I used 39,000) in a worksheet called "Gages", then populate a few cells in column I with "MPR0010" and  then "T1"  in  a few less cells in the corresponding rows in column J. Of course also you need Listbox1 in the Userform.
>>
>> This code is more complicated than expected because of yet another VBA bug. The Listbox control does not allow you fill more than 10 columns usingthe  .List(.ListCount - 1, nColumn)method that I used in my previous post. It errors. This is undocumented in the Help, so is a bug.  
>>
>>
>> My first approach was to make a subset array from the matching rows but the problem there is that you can only Redim the rightmost dimension in a Redim statement (columns being in the rightmost). In the past I have coped with this with a double transpose method but I did not want to increase the number of code lines. I also tried a method that involved putting a large mainly blank subset array into the Listbox and then in a loop using RemoveItem to delete empty rows but this took too long (10 seconds for 39,000 rows)
>>
>> Therefore the method I used puts individual matching rows into a Collection and then in the last stage unpacks them into a rectangular array which populates the List property of the Listbox. Collections expand and contract dynamically without Re-Dim-ing.
>>
>> The code is written in a self documenting style. If anybody wants an explanation of any point I will be pleased to oblige. By the way I tested this with 480 columns. It works. I don't know how anybody could use a Listbox with 480 columns (let alone 17, Microsoft thinks 10 is too many) but it works with only a minor pause. 
>>
>> Comments invited please. 
>>
>> Regards
>>
>> Derek Turner +++
>>
>>
>>
>>
>>
>>
>> From:JIMNEELY <jimneely@...>
>> >
>> >To: ExcelVBA@yahoogroups.com
>> >Sent: Sunday, 26 February 2012, 4:28
>> >Subject: [ExcelVBA] Re: LISTBOX
>> >
>> >
>> > 
>> >wow I fixed my problem. all i did was to start macro recording and went though the steps i wanted the program to do and it works here is the code.
>> >
>> >Private Sub UserForm_Initialize()
>> >'Author : Jim Neely
>> >'Macro Purpose: To populate a listbox with data from
>> >' a worksheet range
>> >Application.ScreenUpdating = False
>> >Sheets("Gages").Select
>> >Range("A2").Select
>> >Selection.AutoFilter
>> >ActiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=9, Criteria1:="MR0008"
>> >ActiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=10, Criteria1:="T1"
>> >Range("A1:Q905").Select
>> >Selection.Copy
>> >'Workbooks.Open Filename:="C:\Users\Lori\Desktop\awnc\ScratchWB.xlsx"
>> >Sheets("Sheet3").Select
>> >Range("A1").Select
>> >ActiveSheet.Paste
>> >Range("A2").Select
>> >'Windows("Press Area - Master Gage File and Map.xlsm").Activate
>> >Sheets("Gages").Select
>> >Range("A78").Select
>> >Application.CutCopyMode = False
>> >Selection.AutoFilter
>> >Range("A2").Select
>> >'Windows("ScratchWB.xlsx").Activate
>> >Sheets("Sheet3").Select
>> >Dim lbtarget As msforms.ListBox
>> >Dim rngSource As Range
>> >'Set reference to the range of data to be filled
>> >Set rngSource = Worksheets("sheet3").Range("A1:Q500")
>> >'Fill the listbox
>> >Set lbtarget = Me.ListBox3
>> >With lbtarget
>> >'Determine number of columns
>> >.ColumnCount = 17
>> >'Set column widths
>> >.ColumnWidths = "100;80;50;50;50;50;50;100;60;50;50;50;50;50;05;05;50"
>> >'Insert the range of data supplied
>> >.List = rngSource.Cells.Value
>> >End With
>> >'Dim savechanges As Boolean
>> >'savechanges = False
>> >'Application.ActiveWindow.Close (savechanges)
>> >Sheets("Map").Select
>> >Application.ScreenUpdating = True
>> >
>> >End Sub
>> >
>> >--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@> wrote:
>> >>
>> >> Hi Jim
>> >>
>> >> I am not sure about the problem as described in your later post, but this is a more simple way of doing what I think you are trying to achieve in your code below.
>> >>
>> >> Private Sub UserForm_Initialize()
>> >> Dim nRow As Long
>> >> Dim vArray As Variant
>> >> Const LOOKFOR = "PR0010"
>> >> '
>> >>     vArray = Worksheets("Gages").Range("i1:k39000").Value
>> >>     With ListBox1
>> >>
>> >>         .ColumnCount = 3
>> >>         .ColumnWidths = "50"
>> >>         For nRow = 1 To UBound(vArray)
>> >>             If vArray(nRow, 1) = LOOKFOR Then
>> >>                 .AddItem vArray(nRow, 1)
>> >>                 .List(.ListCount - 1, 1) = vArray(nRow, 2)
>> >>                 .List(.ListCount - 1, 2) = vArray(nRow, 3)
>> >>             End If
>> >>         Next nRow
>> >>     End With
>> >> End Sub
>> >>
>> >>
>> >> It assumes you have a Listbox1 in the UserForm and is looking for a match in column i (which I populated with an arbitrary 38,000 random rows to test the speed), and picks up the items in columns j and k for the multi-column listbox. Notice it does not error if you set the ColumnCount to 2, it just ignores the item.
>> >>
>> >> Regards.
>> >>
>> >> Derek +++
>> >>
>> >>  
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> >________________________________
>> >> > From: JIMNEELY <jimneely@>
>> >> >To: ExcelVBA@yahoogroups.com
>> >> >Sent: Wednesday, 22 February 2012, 19:59
>> >> >Subject: [ExcelVBA] Re: LISTBOX
>> >> >
>> >> >
>> >> > 
>> >> >
>> >> >
>> >> >--- In ExcelVBA@yahoogroups.com, "JIMNEELY" <jimneely@> wrote:
>> >> >>
>> >> >> I want a listbox to show items from a spreadsheet. The items have to be certain items so I was thinking a FIND would do the trick. but my main question to the group is this possible.
>> >> >>
>> >> >This might help. I am trying to fill a listbox with 3 columns based on a one find item. so i look at all "PR0008" and store the 3 columns in a array to later display in a listbox. Here is the code so far. It does not store the "DEPT" but instead the count.
>> >> >Private Sub UserForm_Initialize()
>> >> >Dim lb As msforms.ListBox
>> >> >Dim rcArray() As String
>> >> >Dim Dept As Integer
>> >> >With Worksheets("Gages").Range("I2:I25")
>> >> >Set D = .Find("PR0010", LookIn:=xlValues)
>> >> >firstaddress = D.Address
>> >> >Set D = .FindNext(D)
>> >> >While D.Address <> firstaddress
>> >> >Dept = Dept + 1
>> >> >MsgBox Dept
>> >> >MsgBox D
>> >> >ReDim Preserve rcArray(1 To Dept)
>> >> >rcArray(D) = D
>> >> >Set D = .FindNext(D)
>> >> >Wend
>> >> >End With
>> >> >
>> >> >'Place the array in the listbox
>> >> >Set lb = Me.ListBox1
>> >> >With lb
>> >> >.ColumnCount = 1
>> >> >.ColumnWidths = "50"
>> >> >.List = rcArray
>> >> >End With
>> >> >End Sub
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >> [Non-text portions of this message have been removed]
>> >>
>> >
>> >
>> >
>> >
>> >
>>
>> [Non-text portions of this message have been removed]
>>
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

[ExcelVBA] Re: LISTBOX

 

Hi Derek I am trying to follow this and am a bit lost as to where I put the code...I created a new workbook and renamed a sheet Gages then put some data in etc then I created a user form with a listbox on it but now where does your code go and how do I run it....sorry if these are dumb questions but it is still a new world to me....

--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>
> Hi All
>
> Here is my own code to do what Jim wants :- 
>
>
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> '   Populate a multicolumn Listbox (with >10 columns) from an Excel range
> '   Derek Turner February 2012
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Private Sub UserForm_Initialize()
> Const FIND_I = "MPR0010"
> Const FIND_J = "T1"
> Const COLUMN_I = 9
> Const COLUMN_J = 10
> Const LISTBOXCOLUMNS = 17
> Const TITLEROW = 1
>
> Dim vSourceArray As Variant, vSubsetArray() As Variant
> Dim vSubsetRow(1 To LISTBOXCOLUMNS), clxSubsetRows As New Collection
> Dim nSourceRow As Long, nSubsetRow As Long, nSourceRowCount As Long, nListBoxColumn As Long
> '
>     vSourceArray = Worksheets("Gages").Range("a1").CurrentRegion.Value
>     nSourceRowCount = UBound(vSourceArray)
>     
>     For nSourceRow = 1 To nSourceRowCount
>         If nSourceRow = 
> TITLEROW  _
>          Or (vSourceArray(nSourceRow, COLUMN_I) = FIND_I _
>          And vSourceArray(nSourceRow, COLUMN_J) = FIND_J) Then
>             For nListBoxColumn = 1 To LISTBOXCOLUMNS
>                 vSubsetRow(nListBoxColumn) = vSourceArray(nSourceRow, nListBoxColumn)
>             Next nListBoxColumn
>             clxSubsetRows.Add vSubsetRow()
>         End If
>     Next nSourceRow
>     With clxSubsetRows
>         ReDim vSubsetArray(1 To .Count, 1 To LISTBOXCOLUMNS)
>         For nSubsetRow = 1 To .Count
>             For nListBoxColumn = 1 To LISTBOXCOLUMNS
>                 vSubsetArray(nSubsetRow, nListBoxColumn) = .Item(nSubsetRow)(nListBoxColumn)
>             Next nListBoxColumn
>         Next nSubsetRow
>     End With
>     With ListBox1
>         .ColumnCount = LISTBOXCOLUMNS
>         .ColumnWidths = "100;80;50;50;50;50;50;100;60;50;50;50;50;50;05;05;50"
>         .List = vSubsetArray
>     End With
> End Sub
> ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>
> To test this you need to  put random stuff  into a range of 17 columns and as many rows as you like (I used 39,000) in a worksheet called "Gages", then populate a few cells in column I with "MPR0010" and  then "T1"  in  a few less cells in the corresponding rows in column J. Of course also you need Listbox1 in the Userform.
>
> This code is more complicated than expected because of yet another VBA bug. The Listbox control does not allow you fill more than 10 columns usingthe  .List(.ListCount - 1, nColumn)method that I used in my previous post. It errors. This is undocumented in the Help, so is a bug.  
>
>
> My first approach was to make a subset array from the matching rows but the problem there is that you can only Redim the rightmost dimension in a Redim statement (columns being in the rightmost). In the past I have coped with this with a double transpose method but I did not want to increase the number of code lines. I also tried a method that involved putting a large mainly blank subset array into the Listbox and then in a loop using RemoveItem to delete empty rows but this took too long (10 seconds for 39,000 rows)
>
> Therefore the method I used puts individual matching rows into a Collection and then in the last stage unpacks them into a rectangular array which populates the List property of the Listbox. Collections expand and contract dynamically without Re-Dim-ing.
>
> The code is written in a self documenting style. If anybody wants an explanation of any point I will be pleased to oblige. By the way I tested this with 480 columns. It works. I don't know how anybody could use a Listbox with 480 columns (let alone 17, Microsoft thinks 10 is too many) but it works with only a minor pause. 
>
> Comments invited please. 
>
> Regards
>
> Derek Turner +++
>
>
>
>
>
>
> From:JIMNEELY <jimneely@...>
> >
> >To: ExcelVBA@yahoogroups.com
> >Sent: Sunday, 26 February 2012, 4:28
> >Subject: [ExcelVBA] Re: LISTBOX
> >
> >
> > 
> >wow I fixed my problem. all i did was to start macro recording and went though the steps i wanted the program to do and it works here is the code.
> >
> >Private Sub UserForm_Initialize()
> >'Author : Jim Neely
> >'Macro Purpose: To populate a listbox with data from
> >' a worksheet range
> >Application.ScreenUpdating = False
> >Sheets("Gages").Select
> >Range("A2").Select
> >Selection.AutoFilter
> >ActiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=9, Criteria1:="MR0008"
> >ActiveSheet.Range("$A$1:$Q$905").AutoFilter Field:=10, Criteria1:="T1"
> >Range("A1:Q905").Select
> >Selection.Copy
> >'Workbooks.Open Filename:="C:\Users\Lori\Desktop\awnc\ScratchWB.xlsx"
> >Sheets("Sheet3").Select
> >Range("A1").Select
> >ActiveSheet.Paste
> >Range("A2").Select
> >'Windows("Press Area - Master Gage File and Map.xlsm").Activate
> >Sheets("Gages").Select
> >Range("A78").Select
> >Application.CutCopyMode = False
> >Selection.AutoFilter
> >Range("A2").Select
> >'Windows("ScratchWB.xlsx").Activate
> >Sheets("Sheet3").Select
> >Dim lbtarget As msforms.ListBox
> >Dim rngSource As Range
> >'Set reference to the range of data to be filled
> >Set rngSource = Worksheets("sheet3").Range("A1:Q500")
> >'Fill the listbox
> >Set lbtarget = Me.ListBox3
> >With lbtarget
> >'Determine number of columns
> >.ColumnCount = 17
> >'Set column widths
> >.ColumnWidths = "100;80;50;50;50;50;50;100;60;50;50;50;50;50;05;05;50"
> >'Insert the range of data supplied
> >.List = rngSource.Cells.Value
> >End With
> >'Dim savechanges As Boolean
> >'savechanges = False
> >'Application.ActiveWindow.Close (savechanges)
> >Sheets("Map").Select
> >Application.ScreenUpdating = True
> >
> >End Sub
> >
> >--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@> wrote:
> >>
> >> Hi Jim
> >>
> >> I am not sure about the problem as described in your later post, but this is a more simple way of doing what I think you are trying to achieve in your code below.
> >>
> >> Private Sub UserForm_Initialize()
> >> Dim nRow As Long
> >> Dim vArray As Variant
> >> Const LOOKFOR = "PR0010"
> >> '
> >>     vArray = Worksheets("Gages").Range("i1:k39000").Value
> >>     With ListBox1
> >>
> >>         .ColumnCount = 3
> >>         .ColumnWidths = "50"
> >>         For nRow = 1 To UBound(vArray)
> >>             If vArray(nRow, 1) = LOOKFOR Then
> >>                 .AddItem vArray(nRow, 1)
> >>                 .List(.ListCount - 1, 1) = vArray(nRow, 2)
> >>                 .List(.ListCount - 1, 2) = vArray(nRow, 3)
> >>             End If
> >>         Next nRow
> >>     End With
> >> End Sub
> >>
> >>
> >> It assumes you have a Listbox1 in the UserForm and is looking for a match in column i (which I populated with an arbitrary 38,000 random rows to test the speed), and picks up the items in columns j and k for the multi-column listbox. Notice it does not error if you set the ColumnCount to 2, it just ignores the item.
> >>
> >> Regards.
> >>
> >> Derek +++
> >>
> >>  
> >>
> >>
> >>
> >>
> >>
> >>
> >> >________________________________
> >> > From: JIMNEELY <jimneely@>
> >> >To: ExcelVBA@yahoogroups.com
> >> >Sent: Wednesday, 22 February 2012, 19:59
> >> >Subject: [ExcelVBA] Re: LISTBOX
> >> >
> >> >
> >> > 
> >> >
> >> >
> >> >--- In ExcelVBA@yahoogroups.com, "JIMNEELY" <jimneely@> wrote:
> >> >>
> >> >> I want a listbox to show items from a spreadsheet. The items have to be certain items so I was thinking a FIND would do the trick. but my main question to the group is this possible.
> >> >>
> >> >This might help. I am trying to fill a listbox with 3 columns based on a one find item. so i look at all "PR0008" and store the 3 columns in a array to later display in a listbox. Here is the code so far. It does not store the "DEPT" but instead the count.
> >> >Private Sub UserForm_Initialize()
> >> >Dim lb As msforms.ListBox
> >> >Dim rcArray() As String
> >> >Dim Dept As Integer
> >> >With Worksheets("Gages").Range("I2:I25")
> >> >Set D = .Find("PR0010", LookIn:=xlValues)
> >> >firstaddress = D.Address
> >> >Set D = .FindNext(D)
> >> >While D.Address <> firstaddress
> >> >Dept = Dept + 1
> >> >MsgBox Dept
> >> >MsgBox D
> >> >ReDim Preserve rcArray(1 To Dept)
> >> >rcArray(D) = D
> >> >Set D = .FindNext(D)
> >> >Wend
> >> >End With
> >> >
> >> >'Place the array in the listbox
> >> >Set lb = Me.ListBox1
> >> >With lb
> >> >.ColumnCount = 1
> >> >.ColumnWidths = "50"
> >> >.List = rcArray
> >> >End With
> >> >End Sub
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >> [Non-text portions of this message have been removed]
> >>
> >
> >
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>

__._,_.___
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....

[smf_addin] Digest Number 2132

Messages In This Digest (10 Messages)

1a.
10 Year Summary MSN - only a few elements work From: briansaxo@rocketmail.com
1b.
Re: 10 Year Summary MSN - only a few elements work From: Randy Harmelink
2a.
Help needed on Excel 2010 and Add ins From: James Shirley
2b.
Re: Help needed on Excel 2010 and Add ins From: Randy Harmelink
3a.
Retrieved Value Doesn't Match Webpage From: xavierviper
3b.
Re: Retrieved Value Doesn't Match Webpage From: Randy Harmelink
4a.
market open/ close From: jurgen.conrad
4b.
Re: market open/ close From: Randy Harmelink
5a.
Excel Not Responding From: jmkoeck
5b.
Re: Excel Not Responding From: Randy Harmelink

Messages

1a.

10 Year Summary MSN - only a few elements work

Posted by: "briansaxo@rocketmail.com" briansaxo@rocketmail.com   briansaxo@rocketmail.com

Tue Feb 28, 2012 6:31 am (PST)



Hi Randy,
First - thanks for the add-in, it makes life a whole lot easier.
I've been using the 10 year summary on MSN and get very few elements to
return data whether I use the function in my own sheet or the sample
"SMF-Template-MSN-10-Year-Summaries.xls". The elements that return data
are the ones in bold below and it doesn't seem to matter which ticker I
use. Any idea why this is the case?
Cheers
Brian
Price/Earnings = ErrorPrice/Sales = ErrorPrice/Book = ErrorBook
Value/Share% Net Profit Margin = #VALUE% Return on Equity% Return on
AssetsDebt/EquityInterest CoverageCurrent Assets = ErrorCurrent
Liabilities = ErrorLong Term Debt = ErrorShares Outstanding = ErrorSales
= ErrorEBIT = ErrorDepreciation = ErrorTotal Net Income = ErrorEarnings
Per Share = Error% Tax Rate = #VALUE

1b.

Re: 10 Year Summary MSN - only a few elements work

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Tue Feb 28, 2012 7:13 am (PST)



I just download the template from the files area and tried ticker MMM.
Everything is working here. What version of the add-in are you using? You
may need the most current element definitions.

On Tue, Feb 28, 2012 at 6:36 AM, briansaxo@rocketmail.com <
briansaxo@rocketmail.com> wrote:

>
> I've been using the 10 year summary on MSN and get very few elements to
> return data whether I use the function in my own sheet or the sample
> "SMF-Template-MSN-10-Year-Summaries.xls". The elements that return data are
> the ones in bold below and it doesn't seem to matter which ticker I use.
> Any idea why this is the case?
>
> Price/Earnings = Error
> Price/Sales = Error
> Price/Book = Error
> *Book Value/Share*
> % Net Profit Margin = #VALUE
> *% Return on Equity*
> *% Return on Assets*
> *Debt/Equity*
> *Interest Coverage*
> Current Assets = Error
> Current Liabilities = Error
> Long Term Debt = Error
> Shares Outstanding = Error
> Sales = Error
> EBIT = Error
> Depreciation = Error
> Total Net Income = Error
> Earnings Per Share = Error
> % Tax Rate = #VALUE
>
2a.

Help needed on Excel 2010 and Add ins

Posted by: "James Shirley" jimbo91363@yahoo.com   jimbo91363

Tue Feb 28, 2012 11:28 am (PST)



Summary: Problem with getting (1) excel to delete permanently the old "Add in" directory; (2) excel not recognizing the functions once the "Add in" directory is correctly readded. I am having to redue this every time excel starts.

I originally set up the Directory in "SMF Add_ins" then realized it should have been SMF Add_in. Installed as instucted to the Add ins via a blank Excel workbook. Problem: Excel realizes that it the files can not be found because I (deleted, rename, moved). So I have since re-added the SMF Add_in to the Add ins list of which Excel asks me to delete the prior that it cannot find "SMF Add_ins". I do. So, now it finds the functions but they do not calulate. I get the NAME error. I see the functions are in the formulas list. I am using the =smfGetOptionsQuotes() function. In the cell I can remove the "s" off =smfGetOptionsQuotes() to get =smfGetOptionsQuote(), save to cell, recalculates gives VALUE error. I add the "s" back to the function =smfGetOptionsQuotes() it produces a value. This function performs as expected..

I feel like I should be able to delete forever in excel the old Add_in directory. Readd properly. Then when the spreadsheet come up it should calcuate based of the current data being read. Neither is happening. I need some guidance.

2b.

Re: Help needed on Excel 2010 and Add ins

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Tue Feb 28, 2012 11:38 am (PST)



Take a look in the "Links" area of the group -- there is an item on
location errors and the #NAME? problem.

When you move the add-in to a new location, ALL workbooks that used the old
location are going to have to be *manually* fixed. Because of how EXCEL
handles add-ins, your formulas will NOT automatically change to the new
location you have the add-in in. As far as EXCEL is concerned, that is a
different add-in, because it's in a different location.

You can try running the smfFixLinks macro -- I use it to fix location
errors when I have them.

On Tue, Feb 28, 2012 at 9:40 AM, James Shirley <jimbo91363@yahoo.com> wrote:

> Summary: Problem with getting (1) excel to delete permanently the old
> "Add in" directory; (2) excel not recognizing the functions once the "Add
> in" directory is correctly readded. I am having to redue this every time
> excel starts.
>
> I originally set up the Directory in "SMF Add_ins" then realized it should
> have been SMF Add_in. Installed as instucted to the Add ins via a blank
> Excel workbook. Problem: Excel realizes that it the files can not be found
> because I (deleted, rename, moved). So I have since re-added the SMF
> Add_in to the Add ins list of which Excel asks me to delete the prior that
> it cannot find "SMF Add_ins". I do. So, now it finds the functions but
> they do not calulate. I get the NAME error. I see the functions are in the
> formulas list. I am using the =smfGetOptionsQuotes() function. In the
> cell I can remove the "s" off =smfGetOptionsQuotes() to get
> =smfGetOptionsQuote(), save to cell, recalculates gives VALUE error. I add
> the "s" back to the function =smfGetOptionsQuotes() it produces a value.
> This function performs as expected..
>
> I feel like I should be able to delete forever in excel the old Add_in
> directory. Readd properly. Then when the spreadsheet come up it should
> calcuate based of the current data being read. Neither is happening. I
> need some guidance.
>
3a.

Retrieved Value Doesn't Match Webpage

Posted by: "xavierviper" allen.brian@xiostech.com   xavierviper

Tue Feb 28, 2012 2:14 pm (PST)



Example:

=RCHGetYahooQuotes("IBM","r") ---> 15.12

Web page http://finance.yahoo.com/q?s=ibm&ql=1 shows ---> 15.16

And it's not just IBM, as I tried a few other tickers as well.

Thx,
Xavier

3b.

Re: Retrieved Value Doesn't Match Webpage

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Tue Feb 28, 2012 2:20 pm (PST)



I see 15.12 on the CSV file that Yahoo generates, which is where that
function gets the data:

http://download.finance.yahoo.com/d/quotes.csv?s=IBM&f=sr&e=.csv

You can ask Yahoo why the values are different. I suspect it just has to do
with when it's calculated, since the price can change during the day.

The ratio of 15.16 to 15.12 is about the same as the ratio of the closing
price today to the closing price yesterday. So, possibly the CSV file they
create contains the ratio calculated as of the end of the previous day.

On Tue, Feb 28, 2012 at 2:53 PM, xavierviper <allen.brian@xiostech.com>wrote:

> Example:
>
> =RCHGetYahooQuotes("IBM","r") ---> 15.12
>
> Web page http://finance.yahoo.com/q?s=ibm&ql=1 shows ---> 15.16
>
> And it's not just IBM, as I tried a few other tickers as well.
>
4a.

market open/ close

Posted by: "jurgen.conrad" jurgen.conrad@gmail.com   jurgen.conrad

Tue Feb 28, 2012 2:40 pm (PST)



hi all - is the market open/ close information obtainable???

i.e. it would be great to know if the AU market (or other markets) is currently open or closed.

i can do it by other means, however it would be more ease (i believe).

thanks in advance for your help & time - regards...

...jurgen

4b.

Re: market open/ close

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Tue Feb 28, 2012 3:04 pm (PST)



You can try:

=smfGetTagContent("http://au.finance.yahoo.com/q?s=AAC.AX
","span",-1,"yfs_market_time")

I'm not sure what it will look like after the market is closed, but here's
what I got just prior to the market open, and after the market opened:

Wed 29 Feb 2012 09:50 - Australia Markets open in 10 mins.
Wed 29 Feb 2012 10:00 - Australia Markets close in 6 hrs.

If they follow the same structure as the U.S. web pages, it should say
"Australia Markets closed" when the market closes for the day.

On Tue, Feb 28, 2012 at 3:38 PM, jurgen.conrad <jurgen.conrad@gmail.com>wrote:

> hi all - is the market open/ close information obtainable???
>
> i.e. it would be great to know if the AU market (or other markets) is
> currently open or closed.
>
> i can do it by other means, however it would be more ease (i believe).
>
> thanks in advance for your help & time - regards...
>
5a.

Excel Not Responding

Posted by: "jmkoeck" jmkoeck@hotmail.com   jmkoeck

Tue Feb 28, 2012 3:20 pm (PST)



Has anyone encountered this issue? I've been using the add-in for months now and all of a sudden a workbook with the add-in enabled will not respond when I attempt to open it. I have not downloaded any new programs or have written any new macros.

Any thoughts?

Thanks.

5b.

Re: Excel Not Responding

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Tue Feb 28, 2012 3:24 pm (PST)



I've had that a few times in the past. In those cases, it was because one
of the websites I was getting data from was down. That means that each
formula that gets data from a new web page on that website has to "time
out" before it can go on to the next formula...

On Tue, Feb 28, 2012 at 4:13 PM, jmkoeck <jmkoeck@hotmail.com> wrote:

> Has anyone encountered this issue? I've been using the add-in for months
> now and all of a sudden a workbook with the add-in enabled will not respond
> when I attempt to open it. I have not downloaded any new programs or have
> written any new macros.
>
> Any thoughts?
>
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

Need traffic?

Drive customers

With search ads

on Yahoo!

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
READ MORE....