CODEWITHSUNDEEP
excelvba
doofee
doofee

Reputation: 3

VBA Stop For/Next Statement at Certain Criteria

Image

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!

Upvotes: 0

Views: 67

Answers (1)

mattJ
mattJ

Reputation: 51

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

Upvotes: 1

Related Questions