copy value

[Guest]

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]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Guest]

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]

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)

 

[Guest]

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]

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.

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]

Change this line:

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

to

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

 

[Guest]

Thanks, Dave. It's almost there. I keep getting Run-time error 13
It stops here:
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.

Change this line:

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

to

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

 

[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

Thanks, Dave. It's almost there. I keep getting Run-time error 13
It stops here:
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.

 

[Guest]

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

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

 

[Dave Peterson]

Whew!

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