About Us

williamr

Thank you very very much!

"Mat P:son" wrote:

"williamr" wrote:

Hi again.

Hi ya,

I copied Nigel's code and it does what I need

Okay, that's good then -- I thought you might need a slightly more general
solution than four For-loops, but if that does the trick then that's fine,
obviously ...

but I would like the
output of the "Shelf" (L3) to be only 1 character but I don't know how to
change it.

Well, the easiest wasy of doing it is to change:

& fnstr(L3) & "-" _

...into

& L3 & "-" _

...or nicer (but it'll give you the same result, really):

& CStr(L3) & "-" _

How many years does it take to understand this stuff?????

Hehehe, not long at all actually, but don't tell anyone because then they'll
lower my salary :o)

On a serious note: there are plenty of good tutorials out there, and many
snippets of VBA code that you can take a look at, and, of course, you've got
the whole VBA Help at your fingertips (when you need some detailed technical
information). Start with a Google search for "Excel VBA tutorial" and go
through a couple of the web pages you find -- I'm sure things will be much,
much clearer afterwards.

Thank You Again

No problem, you're welcome. And good luck with your VBA career :o)
/MP

"Mat P:son" wrote:

"williamr" wrote:

I don't really understand either solution

That's all right, we can obviously discuss these things...

but looking at both I have a
question. Where would the code I wrote go?

Nowhere really -- both Nigel's solution and mine should be fairly
self-contained. That is, just put any one of them into the code module of any
sheet and run. You should see column A getting filled, from row 3 downwards.

The values we output into cells are just example values though, and that's
why I put the "TODO:" tags into the code -- we now have to figure out the
correct values for your rows, units etc.

Here is the code, please don't
laugh, I'm just learning!!!

No one will be laughing, I'm sure -- we've all been beginners, and we all
continue to learn new stuff.

However, I believe the VBA code below is a tad difficult to follow, so
instead I'd like to ask you how the numbering of rows, units, and those
things are supposed to work. Are the following statements correct:

- Row should go from 1 to 5
- Unit should go from 1 to 11 for Row 1, and then from 1 to 22 for the others
- Shelf should always go from 1 to 5
- Position should always go from 1 to 5

(However, that doesn't get me nowhere near 4,750 rows though... Hmmm,
5*5*5*22 yields 2,750 -- was that what you meant?)

I suppose there might be a bug or two that makes my deduction less than
accurate, so I want to hear your version of the story as well :o) And you're
talking about 4,750 different combinations, but since I only get 2,000-odd
rows when I run your program I suppose the output is also cropped.

I'm keen to get some more input -- send us a few lines, and I'm sure we'll
be able to work something out... :o)

Cheers,
/MP

Sub bill()
'Dim varibles
Dim xx As Integer
Dim Row As Integer
Dim Unit As Integer
Dim Shelf As Integer
Dim Position As Integer

'Set varibles starting number
Row = 1
Unit = 1
Shelf = 1
Position = 1
Unit = 1
Position = 0
xx = 0

Do While xx <= 2000
xx = xx + 1
Position = Position + 1

If Position = 6 Then
Position = 1
Shelf = Shelf + 1
End If

If Shelf = 6 And Position = 1 Then
Unit = Unit + 1
Shelf = 1
Position = 1
End If

If Row = 1 And Unit = 12 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 2 And Unit = 23 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 3 And Unit = 23 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 4 And Unit = 23 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 5 And Unit = 23 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 6 And Unit = 12 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 7 And Unit = 11 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 8 And Unit = 11 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 9 And Unit = 21 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

If Row = 10 And Unit = 21 And Position = 1 Then
Row = Row + 1
Unit = 1
Shelf = 1
Position = 1
End If

Debug.Print " Row is " & Row & _
" Unit is " & Unit & _
" Shelf is " & Shelf & _
" Position is " & Position

Loop
End Sub

"Nigel" wrote:

Here is a general purpose code to fill the sheet, you set the run time
parameters for each value range; target sheet name and the row and column on
that sheet you wish to populate.

Sub FillSheet()
Dim sRow As Long, sUnit As Long, sShelf As Long, sPosition As Long
Dim eRow As Long, eUnit As Long, eShelf As Long, ePosition As Long
Dim shRow As Long, shCol As Integer, xR As Long
Dim L1 As Long, L2 As Long, L3 As Long, L4 As Long
Dim shName As String

'---------------------------------------------
' Run Time PARAMETERS
'---------------------------------------------
' set start and end values for each level
sRow = 1: eRow = 5
sUnit = 1: eUnit = 5
sShelf = 1: eShelf = 5
sPosition = 1: ePosition = 5

' specify datahseet to act upon
shName = "Sheet1"

' set first row and column for datasheet
shRow = 3: shCol = 1

'---------------------------------------------
' MAIN CODE
'---------------------------------------------
' check combinations do not exceed range
If (eRow - sRow) * (eUnit - sUnit) * _
(eShelf - sShelf) * (ePosition - sPosition) _
Rows.Count Then
MsgBox "Range combinations exceed spreadsheet limit"
Exit Sub
End If

Application.ScreenUpdating = False
With Sheets(shName)
xR = shRow
For L1 = sRow To eRow
For L2 = sUnit To eUnit
For L3 = sShelf To eShelf
For L4 = sPosition To ePosition
.Cells(xR, shCol) = _
fnstr(L1) & "-" _
& fnstr(L2) & "-" _
& fnstr(L3) & "-" _
& fnstr(L4)
xR = xR + 1
Next
Next
Next
Next
End With
Application.ScreenUpdating = True
MsgBox CStr(xR - shRow) & " combinations created"
End Sub

Function fnstr(xV As Long) As String
' function to take value and convert to leading zero string
If xV 9 Then
fnstr = CStr(xV)
Else
fnstr = "0" & CStr(xV)
End If
End Function

--
Cheers
Nigel

"williamr" wrote in message
...
I have a small "Do While" VBA program that creates some numbers (4750).
The
problem is that I don't know how to populate the cells in the worksheet
using
the VBA code. My results look like the following:

Row 1 Unit 2 Shelf 4 Position 5

There are 4750 different combinations. I want to concatenate the number
so
they look like 01-02-04-05 in Col A starting in row 3 until the Macro is
done, can someone help?

Thanks in Advance.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Populating Cells	rciolkosz	Excel Discussion (Misc queries)	3	August 27th 08 06:54 PM
Randomly populating empty cells with other text cells	Throme88	Excel Discussion (Misc queries)	3	July 1st 08 02:58 PM
Auto-populating cells depending on the value of other cells!	RemySS	Excel Programming	7	October 10th 05 04:29 PM
Populating several cells	A	Excel Worksheet Functions	1	September 15th 05 05:58 PM
Populating Cells in a row.	Jako[_50_]	Excel Programming	6	August 2nd 04 12:24 AM

Menu

About Us