How to code this in my worksheet

smlim7

New Member
Joined
Apr 21, 2009
Messages
4
I need the following expected output :-

Column A1 : 6
Column B1 : 3
Column C1 : 5

Answer A10 : 6
A11 : 5
A12 : 4
A13 : 3
A14 : 2
A15 : 1
A16 : 0

where it reach 0, the next column B17 : 3 will start as ( it take from the upper column B1 )

B17 : 3
B18 : 2
B19 : 1
B20 : 0

then the next column if still not null, then it will take value from C1 to start again. If C1 : 5, then it will be

C21 : 5
C22 : 4
C23 : 3
. .... till it goes to reach 0. It will start to fill as long as the upper column 1 filled with value.

How to code this is macro vb for easier data fill ?

Please help me. Thanks.

Regards,
Lim
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
Give this macro a try...

Code:
Sub StaggerFillColumnsDown()
  Dim X As Long, LastColumn As Long, StartCell As Range
  LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  Set StartCell = Range("A10")
  For X = 1 To LastColumn
    StartCell.Value = Cells(1, X).Value
    With StartCell.Offset(1).Resize(StartCell.Value)
      .FormulaR1C1 = "=R[-1]C-1"
      .Value = .Value
    End With
    Set StartCell = Cells(Cells(Rows.Count, X).End(xlUp).Row + 1, X + 1)
  Next
End Sub
 

smlim7

New Member
Joined
Apr 21, 2009
Messages
4
Hi Rick,

Thank you very much. It is really what I am looking for.

However, I got another small request hope you can help me out too.

In fact there is a sequential order of the happened and so long the number never go to null then the portion that I mentioned should be repeated for every column till it reach the last number. Hence, it will be like :=

Column B1 : 6
Column C1 : 3
Column D1 : 5

Answer
A10 : 1, B10 : 6
A11 : 2. B11 : 5
A12 : 3, B12 : 4
A13 : 4, B13 : 3
A14 : 5, B14 : 2
A15 : 6, B15 : 1
A16 : 7, B16 : 0
A17 : 8,
A18 : 9, B18 : 6
A19 : 10. B19 : 5
A20 : 3, B20 : 4
A21 : 4, B21 : 3
A22 : 5, B22 : 2
A23 : 6, B23 : 1
A24 : 7, B24 : 0
A25 : 8,

The column A will be filled by the user, I just want to formula will fill with the script that you wrote until it reach the last roll whereby it does not contain any value in column A. The same pattern will be for the column C & D that I mentioned on my first mail. It will loop till the end of the column A.

Once again really appreciate on your valuable script. I have learned a lot from your script.

Looking forward on your reply. Thanks.

Lim
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
Does this macro do what you want?

Code:
Sub StaggerFillColumnsDown()
  Dim X As Long, LastRow As Long, LastColumn As Long, StartCell As Range, NextStartCell As Range
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  Set StartCell = Range("B10")
  For X = 2 To LastColumn
    StartCell.Value = Cells(1, X).Value
    StartCell.Offset(1).Resize(StartCell.Value).FormulaR1C1 = "=R[-1]C-1"
    Set NextStartCell = Cells(Cells(Rows.Count, X).End(xlUp).Row + 1, X + 1)
    StartCell.Offset(1 + Cells(1, X).Value).Resize(LastRow - NextStartCell.Row + 1).FormulaR1C1 = "=if(R[" & (-2 - Cells(1, X).Value) & "]C="""","""",R[" & (-2 - Cells(1, X).Value) & "]C)"
    With Cells(10, X).Resize(LastRow - 9)
      .Value = .Value
    End With
    Set StartCell = NextStartCell
  Next
End Sub
 

smlim7

New Member
Joined
Apr 21, 2009
Messages
4
Hi Rick,

You're brilliant! Your script really helps me out.

Very much thank you and appreciate on your helps.

Regards,
Lim
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top