Excel - Copy rows "n" times with calculation
Solved/Closed
Tracy
-
May 13, 2010 at 04:42 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 14, 2010 at 04:02 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 14, 2010 at 04:02 AM
Related:
- Excel - Copy rows "n" times with calculation
- Calculation is incomplete. recalculate before saving - Guide
- Excel marksheet - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Vat calculation formula - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 14, 2010 at 04:02 AM
May 14, 2010 at 04:02 AM
Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant
Dim vNumber As Variant
Dim vDivFactor As Variant
Sheets("Sheet1").UsedRange.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
lRow = 1
Do While (Cells(lRow, "A") <> "")
RepeatFactor = Cells(lRow, "C")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
Range(Cells(lRow, "A"), Cells(lRow, "C")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "C")).Select
Selection.Insert Shift:=xlDown
vNumber = Cells(lRow, "B")
vDivFactor = Cells(lRow, "C")
If vNumber = "" Then vNumber = 0
If ((vDivFactor <> "") And (vDivFactor <> 0) And (IsNumeric(vDivFactor)) And (IsNumeric(vNumber))) Then
Range(Cells(lRow, "B"), Cells(lRow + RepeatFactor - 2, "B")).Value = Round(vNumber / vDivFactor, 2)
Cells(lRow + RepeatFactor - 1, "B").Value = vNumber - (Round(vNumber / vDivFactor, 2) * (vDivFactor - 1))
Range(Cells(lRow, "C"), Cells(lRow + RepeatFactor - 1, "C")).Clear
End If
lRow = lRow + RepeatFactor - 1
End If
lRow = lRow + 1
Loop
End Sub
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 13, 2010 at 08:11 AM
May 13, 2010 at 08:11 AM
Try this
Sub CopyData()
Dim lRow As Long
Dim RepeatFactor As Variant
Dim vNumber As Variant
Dim vDivFactor As Variant
Sheets("Sheet1").UsedRange.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste
lRow = 1
Do While (Cells(lRow, "A") <> "")
RepeatFactor = Cells(lRow, "C")
If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then
Range(Cells(lRow, "A"), Cells(lRow, "C")).Copy
Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor, "C")).Select
Selection.Insert Shift:=xlDown
vNumber = Cells(lRow, "B")
vDivFactor = Cells(lRow, "C")
If vNumber = "" Then vNumber = 0
If ((vDivFactor <> "") And (vDivFactor <> 0) And (IsNumeric(vDivFactor)) And (IsNumeric(vNumber))) Then
Range(Cells(lRow, "B"), Cells(lRow + RepeatFactor - 1, "B")).Value = Round(vNumber / vDivFactor, 2)
Cells(lRow + RepeatFactor, "B").Value = vNumber - WorksheetFunction.Sum(Range(Cells(lRow, "B"), Cells(lRow + RepeatFactor - 1, "B")))
Range(Cells(lRow, "C"), Cells(lRow + RepeatFactor, "C")).Clear
End If
lRow = lRow + RepeatFactor - 1
End If
lRow = lRow + 1
Loop
End Sub
mama_tracy
Posts
3
Registration date
Friday May 14, 2010
Status
Member
Last seen
May 18, 2010
May 14, 2010 at 02:50 AM
May 14, 2010 at 02:50 AM
Thanks for your reply. I want to add some details for my request. If in sheet 1 col A is "ABC", col B value is 5 and col C value is 3 (col C mean the row number will be copy).
Then Sheet 2 result will be:
ABC 1.66
ABC 1.66
ABC 1.68
However, your macro result is:
ABC 1.66
ABC 1.66
ABC 1.66
ABC 0.02
In any case, many thanks for your help!!!
Then Sheet 2 result will be:
ABC 1.66
ABC 1.66
ABC 1.68
However, your macro result is:
ABC 1.66
ABC 1.66
ABC 1.66
ABC 0.02
In any case, many thanks for your help!!!
