copy value
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
|