Populating Cells using VBA
 

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.

Populating Cells using VBA
 

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.

Populating Cells using VBA
 

What about this, as a first stab... I don't know how you define your Row,
Unit, Shelve and Position numbers, so I simply assumed it's all right to hard
code them into the VBA macro.

Also, the macro assumes (as is indicated in your example) that none of the
numbers will be greater than 99.

===========================

Private Sub FillRows()
Dim iaRows() As Variant
Dim iaUnits() As Variant
Dim iaShelves() As Variant
Dim iaPositions() As Variant

' TODO: Fill out the proper values here
iaRows = Array(1, 2, 3, 4)
iaUnits = Array(99, 88, 77, 66, 55)
iaShelves = Array(12, 34, 56, 78, 90)
iaPositions = Array(9, 8, 7)

' TODO: You have to decide in which order
' the loops should iterate. Rearrange...
Dim x As Long
x = 3
For Each r In iaRows
For Each u In iaUnits
For Each s In iaShelves
For Each p In iaPositions
Cells(x, 1).Value = BuildValue(r, u, s, p)
x = x + 1
Next p
Next s
Next u
Next r
End Sub


Private Function BuildValue( _
ByVal r As Integer, _
ByVal u As Integer, _
ByVal s As Integer, _
ByVal p As Integer) As String

BuildValue = _
Format(r, "00") & "-" & _
Format(u, "00") & "-" & _
Format(s, "00") & "-" & _
Format(p, "00")
End Function

===========================


"williamr" wrote:

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.

Populating Cells using VBA
 

I don't really understand either solution but looking at both I have a
question. Where would the code I wrote go? Here is the code, please don't
laugh, I'm just learning!!!

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.

Populating Cells using VBA
 

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

Populating Cells using VBA
 

Hi again. I copied Nigel's code and it does what I need but I would like the
output of the "Shelf" (L3) to be only 1 character but I don't know how to
change it. How many years does it take to understand this stuff?????

Thank You Again

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

Populating Cells using VBA
 

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

Populating Cells using VBA
 

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.