• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy/Paste loop with offset

Gregg Wolin

Member
I need a macro that will copy and offset data from a source table of cash flow streams where each column in the source table represents a different type of transaction.

A second source table establishes the timing and velocity (number of transactions that will be executed between various increments of months).

I would like the macro to randomly choose one of the columns from the transaction table and paste the data to a new matrix offsetting each additional project based on the timing table.

The attached file visually illustrates what I'd like to achieve.

Thanks in advance!
 

Attachments

  • Portfolio Offset.xlsx
    23.8 KB · Views: 7
Gregg

Try the following code

Code:
Sub Transfer()

Range("R9:AF68").ClearContents

Dim srcRng As Range, dstRng As Range
Dim i As Integer, val As Integer, offsetrow As Integer

offsetrow = -1
Set dstRng = Range("Q9")
   
i = 1
   
Do

  val = Application.WorksheetFunction.VLookup(i, Range("M9:O11"), 3)
  offsetrow = offsetrow + 1
   
  For j = 0 To val - 1

  cf_Rnd = Int(5 * Rnd() + 1)
  Range("D12:D47").Offset(0, cf_Rnd).Copy

  Set dstRng = dstRng.Offset(offsetrow, 1)
  dstRng.Resize(36, 1).PasteSpecial xlPasteValues

  offsetrow = 0
  Next j
  i = i + 1
   
Loop While dstRng.Column < 32

End Sub
 
Gregg

Try the following code

Code:
Sub Transfer()

Range("R9:AF68").ClearContents

Dim srcRng As Range, dstRng As Range
Dim i As Integer, val As Integer, offsetrow As Integer

offsetrow = -1
Set dstRng = Range("Q9")
  
i = 1
  
Do

  val = Application.WorksheetFunction.VLookup(i, Range("M9:O11"), 3)
  offsetrow = offsetrow + 1
  
  For j = 0 To val - 1

  cf_Rnd = Int(5 * Rnd() + 1)
  Range("D12:D47").Offset(0, cf_Rnd).Copy

  Set dstRng = dstRng.Offset(offsetrow, 1)
  dstRng.Resize(36, 1).PasteSpecial xlPasteValues

  offsetrow = 0
  Next j
  i = i + 1
  
Loop While dstRng.Column < 32

End Sub
Follow-up... how to i code the next to last line (Loop While) if rather than entering a number (i.e. 32), I want to refer to a named range called "i_Month.LastDeal".
 
32 is the column number of the last column - 1
So use something like

Range().column + Range().columns -1
 
32 is the column number of the last column - 1
So use something like

Range().column + Range().columns -1
Wasn't working (object defined error). Then I tried:
Code:
Loop While dstRng.Column < Range("i_LastOriginationMonth").Value + 4
which returns 32 in the Immediate window. Didn't work.

Then I tried:
Code:
Worksheets("Inputs").Range("j31").value + 4
and it worked.

Then I tried:
Code:
Worksheets("Inputs").[i_LastOriginationMonth].Value + 4
and that also worked.

Since i used named ranges quite often, should i make a habit of using the bracket reference?
 
Last edited:
Back
Top