Copy/Paste loop with offset

[Gregg Wolin]

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!

 

[Hui]

Gregg

Try the following 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 Wolin]

FRIGGIN' AMAZING - Thank you so much!!

 

[Gregg Wolin]

Gregg

Try the following 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".

 

[Hui]

32 is the column number of the last column - 1
So use something like

Range().column + Range().columns -1

 

[Gregg Wolin]

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:

Loop While dstRng.Column < Range("i_LastOriginationMonth").Value + 4

which returns 32 in the Immediate window. Didn't work.

Then I tried:

Worksheets("Inputs").Range("j31").value + 4

and it worked.

Then I tried:

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?