VBA Stop For/Next Statement at Certain Criteria

Question

Edit: to help clarify, I'd like to be able to populate B2:B6 through VBA so I can copy paste section A2:B6 down. My problem is that next month I will lose the August section and only have Sep to Dec, and so on as the year goes on.

This is my first time actually asking a question here so sorry in advance if I do something incorrectly. I'm very new to vba and need help getting this code to adjust itself and know when to stop.

My old code is this:

    ActiveCell.FormulaR1C1 = "=RC[1]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C[2]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-2]C[3]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C[4]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=R[-4]C[5]"
    ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-5]C[6]"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-6]C[7]"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-7]C[8]"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-8]C[9]"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-9]C[10]"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-10]C[11]"
    'ActiveCell.Offset(1, 0).Range("A1").Select
    'ActiveCell.FormulaR1C1 = "=R[-11]C[12]"
    'ActiveCell.Offset(1, 0).Range("A1").Select

Where all it does is transpose a year's worth of data into a singular column. I'm trying to end with something like:

   If ActiveCell.Offset(0, 1).Value <> "Dec" Then  
   c As Long
   For c = 1 To 12
   ActiveCell.FormulaR1C1 = "=RC[&c&]"
   ActiveCell.Offset(1, 0).Range("a1").Select
   Next c

Where it will adjust the C# and stop after it reaches a certain value in the next column. Currently I just add or remove a ' in front of each pair of the old code to get it to stop where i need it to but i'd like it to be able to do it by itself.

Thanks!

You really want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Aug 23 '19 at 18:33
Would help a lot if you could share a screenshot of what you want to do — Tim Williams, Aug 23 '19 at 18:38
Like @Tim Williams is saying, please share a screenshot of what you are trying to do. As a note, try and explain what the problem you are trying to solve is instead of your solution is. — yossup, Aug 23 '19 at 19:13
@TimWilliams Edited to include image and hopefully help clarify — doofee, Aug 23 '19 at 19:30

score 1 · Accepted Answer · answered Aug 23 '19 at 18:55

Try this:

    Dim rng As Range
    Dim last_col As Integer

    last_col = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column

    If last_col > ActiveCell.Column Then
        Set rng = Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row, last_col))

        rng.Copy
        ActiveCell.Offset(1, 0).PasteSpecial xlPasteAll, Transpose:=True

        Set rng = Nothing
    End If

VBA Stop For/Next Statement at Certain Criteria

1 Answers1