Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Associating one array with another

Well that was easy, I guess that's why I don't get paid to do this for a
living!

Thanks, Tom

Mike

--
Michael J. Malinsky


"Tom Ogilvy" wrote in message
...
If I understand what you want you would make two changes

Private Sub Main()

x = x - 1

Dim y, z, RandomNumber()
Dim i As Long, j As Long
Dim k As Long, m As Long
Dim num As Long

'Dim arr() As Long
Dim arr() as String '<===

Dim varr
Dim SampleSize

SampleSize = TextBox3.Value

'Calculate total population
Population = 0
For y = 0 To x
Population = Population + TopRange(y) - BottomRange(y) + 1
Next y
ReDim RandomNumber(Population)
MsgBox "Population - " & Population


ReDim arr(1 To Population)

'Thanks to Tom Ogilvy
j = 0
For i = LBound(BottomRange) To UBound(BottomRange)
For j = BottomRange(i) To TopRange(i)
k = k + 1

'arr(k) = j
arr(k) = rangename(i) ' <=======

Next
Next
'End thanks to Tom Ogilvy

RandomNumber(0) = -1
For y = 1 To SampleSize
RandomNumber(y) = Int(Population * Rnd + 1)
Cells(y, 1).Value = RandomNumber(y)
Cells(y, 2).Value = arr(RandomNumber(y)) 'I want to replace this
' line with something that will give me RangeName
Next y

Unload UserForm1

End Sub



--
Regards,
Tom Ogilvy


Michael J. Malinsky wrote in message
...
I have a macro in which I can have several ranges of numbers input by a

user
via a userform. The user inputs the RangeName, BottomRange and TopRange
(all arrays). Since the BottomRange and TopRange for each GroupName can
overlap, Tom Ogilvy was nice enough to supply me with some code that

will
associate a sequential range of numbers with the ranges input. For

example:

RangeName(0) = "Group1"
BottomRange(0) = 1
TopRange(0) = 10
RangeName(1) = "Group2"
BottomRange(1) = 2
TopRange(1) = 8

Tom's code creates another array called arr which associates, in this
example, the numbers 1 through 17 to each of the numbers input by the

user,
like this:

1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 2
12 3
13 4
14 5
15 6
16 7
17 8


Instead of having the numbers 1 through 17 being listed down column A, I
would rather have the associated RangeName. I've toyed with a

For...Next
loop with poor results. Any ideas? The full code is below for your
reference.

TIA
-----------------------------------------------------
Private Sub Main()

x = x - 1

Dim y, z, RandomNumber()
Dim i As Long, j As Long
Dim k As Long, m As Long
Dim num As Long
Dim arr() As Long
Dim varr
Dim SampleSize

SampleSize = TextBox3.Value

'Calculate total population
Population = 0
For y = 0 To x
Population = Population + TopRange(y) - BottomRange(y) + 1
Next y
ReDim RandomNumber(Population)
MsgBox "Population - " & Population


ReDim arr(1 To Population)

'Thanks to Tom Ogilvy
j = 0
For i = LBound(BottomRange) To UBound(BottomRange)
For j = BottomRange(i) To TopRange(i)
k = k + 1
arr(k) = j
Next
Next
'End thanks to Tom Ogilvy

RandomNumber(0) = -1
For y = 1 To SampleSize
RandomNumber(y) = Int(Population * Rnd + 1)
Cells(y, 1).Value = RandomNumber(y)
Cells(y, 2).Value = arr(RandomNumber(y)) 'I want to replace this
line with something that will give me RangeName
Next y

Unload UserForm1

End Sub


--
Michael J. Malinsky







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Associating a Name with a Number Vbgooroo Excel Discussion (Misc queries) 5 September 29th 07 03:33 AM
Associating Tab Names to Cells Slave2Six Excel Discussion (Misc queries) 2 August 7th 06 09:31 PM
Associating highest value with name mailrail Excel Discussion (Misc queries) 5 July 30th 06 06:57 PM
Associating a Radio Button to a Value instructorjml Excel Discussion (Misc queries) 0 April 5th 06 02:49 AM
a little help with finding and associating cells rcarrollct Excel Discussion (Misc queries) 2 July 5th 05 10:36 PM


All times are GMT +1. The time now is 01:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"