I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson

Sub DupData()
Dim rng as Range, cell as Range
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
for each cell in rng
cell.offset(0,2).Resize(1,cell.offset(0,1).Value). Value = cell.Value
Next
End Sub

--
Regards,
Tom Ogilvy

"Myriam" wrote in message
...
I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish
this?
Thanks for your help.
Regards,

Thanks, Dave and Tom. You're both awsome! It's exactly what I needed.
Regards,

"Tom Ogilvy" wrote:

Sub DupData()
Dim rng as Range, cell as Range
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
for each cell in rng
cell.offset(0,2).Resize(1,cell.offset(0,1).Value). Value = cell.Value
Next
End Sub

--
Regards,
Tom Ogilvy

"Myriam" wrote in message
...
I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish
this?
Thanks for your help.
Regards,

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Change this line:

..Range("C1", .Cells(1, .Columns.Count)).Clear

to

..Range("C1", .Cells(1, .Columns.Count)).Entirecolumn.Clear



Dave Peterson wrote:

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Thanks, Dave. It's almost there. I keep getting Run-time error 13
It stops he
HowMany = .Cells(iRow, "B").value

but it does place the values where they belong. I formatted all cells as
"number" to prevent any formatting issue w/cells.



"Dave Peterson" wrote:

Change this line:

..Range("C1", .Cells(1, .Columns.Count)).Clear

to

..Range("C1", .Cells(1, .Columns.Count)).Entirecolumn.Clear



Dave Peterson wrote:

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

I declared HowMany as a long. It expects to find a number in that cell. Maybe
it should check a little more to protect itself from bad data:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Variant

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).EntireColumn.Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If IsNumeric(HowMany) Then
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
End If
Next iRow
End With
End Sub





Myriam wrote:

Thanks, Dave. It's almost there. I keep getting Run-time error 13
It stops he
HowMany = .Cells(iRow, "B").value

but it does place the values where they belong. I formatted all cells as
"number" to prevent any formatting issue w/cells.

"Dave Peterson" wrote:

Change this line:

..Range("C1", .Cells(1, .Columns.Count)).Clear

to

..Range("C1", .Cells(1, .Columns.Count)).Entirecolumn.Clear



Dave Peterson wrote:

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Fabulous! It works like a breeze! You're brilliant!

"Dave Peterson" wrote:

I declared HowMany as a long. It expects to find a number in that cell. Maybe
it should check a little more to protect itself from bad data:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Variant

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).EntireColumn.Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If IsNumeric(HowMany) Then
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
End If
Next iRow
End With
End Sub





Myriam wrote:

Thanks, Dave. It's almost there. I keep getting Run-time error 13
It stops he
HowMany = .Cells(iRow, "B").value

but it does place the values where they belong. I formatted all cells as
"number" to prevent any formatting issue w/cells.

"Dave Peterson" wrote:

Change this line:

..Range("C1", .Cells(1, .Columns.Count)).Clear

to

..Range("C1", .Cells(1, .Columns.Count)).Entirecolumn.Clear



Dave Peterson wrote:

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson

Whew!

Myriam wrote:

Fabulous! It works like a breeze! You're brilliant!

"Dave Peterson" wrote:

I declared HowMany as a long. It expects to find a number in that cell. Maybe
it should check a little more to protect itself from bad data:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Variant

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).EntireColumn.Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If IsNumeric(HowMany) Then
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
End If
Next iRow
End With
End Sub





Myriam wrote:

Thanks, Dave. It's almost there. I keep getting Run-time error 13
It stops he
HowMany = .Cells(iRow, "B").value

but it does place the values where they belong. I formatted all cells as
"number" to prevent any formatting issue w/cells.

"Dave Peterson" wrote:

Change this line:

..Range("C1", .Cells(1, .Columns.Count)).Clear

to

..Range("C1", .Cells(1, .Columns.Count)).Entirecolumn.Clear



Dave Peterson wrote:

You could use a worksheet event. And since the values are updated via formulas,
you can use the _calculate event.

Rightclick on the worksheet tab that should have this behavior and select view
code. Then paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Me
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("C1", .Cells(1, .Columns.Count)).Clear

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
If HowMany 1 _
And HowMany < (.Columns.Count - 2) Then
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
End If
Next iRow
End With
End Sub

The code will have to go behind each worksheet that needs it.

Myriam wrote:

Thanks for the reply.
No, the Sheet will not be active, it is a background calculations sheet.
Column A and B will be linked to cells in other worksheets.
When cells in A and B change, I need to populate the necessary columns
utilizing code The results are used for additional calculation within that
Sheet.
Can this be done?
Regards,

"Dave Peterson" wrote:

If you want to run the code against the sheet that's active, you could change
this line:

With Worksheets("sheet1")
to
With Activesheet

(in either suggested code)

Myriam wrote:

Dave / Tom,

I tried both codes on an empty book and they work excellent. But when I
place it on my workbook it gives me error 'subscript out of range' . I will
place the code on Sheet14. Why does it give me that error? And, I also
need to change it automatically when the figures change.
I tried the Worksheet_Change ... but I can't make it work.

Thanks again for your help.

"Dave Peterson" wrote:

How about something like:

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowMany As Long

With Worksheets("sheet1")
FirstRow = 1 'no header rows
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
HowMany = .Cells(iRow, "B").Value
.Cells(iRow, "C").Resize(1, HowMany).Value _
= .Cells(iRow, "A").Value
Next iRow
End With
End Sub




Myriam wrote:

I have a sheet of values in column A. In column B I have a number that
indicates how many times the value of A should be copied to the adjacent
cells.
A B C D E F G
120 1 120
240 2 240 240
350 5 350 350 350 350 350
460 3 460 460 460

Could you please point me in the right direction on how to accomplish this?
Thanks for your help.
Regards,

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson