CODEWITHSUNDEEP
excelvbaloopsformula
RTrain3k
RTrain3k

Reputation: 867

Loop Over Rows and Columns

For the life of me, I can't figure out how to loop a formula over columns and down rows. I put some code together that I thought would be on the right track: 

Sub LoopAcrossColsRows()
    Dim C As Integer
    Dim R As Integer
    Dim LastCol As Integer
    Dim LastRow As Long
    Dim lr As Long

    Worksheets("Sheet1").Activate
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, 20).End(xlUp).Row
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For C = 2 To LastCol
        For R = lr + 3 To LastRow
            .Cells(R, C).FormulaR1C1 = “=SUMPRODUCT(--(R6C:R7C>=RC1), --(R6C:R7C<=(RC1+30))*R4C)"
        Next R
    Next C
End Sub

The formula is causing an expected list separator or ) error. The formula works when I insert it on my worksheet, so I do not know what could be going wrong. Thanks for all help in advance.

As an aside, is there anyway to create an input box that would prompt for a new sheet name, which would be reflected as the new sheet name in the Worksheets("Sheet1").Activate line, so that I wouldn't have to keep changing the sheet name in the vba code?

Thanks again,

Upvotes: 1

Views: 929

Answers (4)

Maus
Maus

Reputation: 158

EDITED: Ok, so took a detailed look at your code. There are a couple of changes to make here.

  1. The loop for "R" doesn't get triggered because lr+3 will always > LastRow
  2. C and R in "Cells(R,C)" could be problematic --> varR and varC.
  3. .Cells --> Activesheet.Cells
  4. You define your last row variable twice "LastRow" & "lr"? I'm not sure why. (also see #6 below.
  5. RC reference in .FormulaR1C1 to be bound by []
  6. The End(xl[Up, ToLeft]) method could miss the full size of the data set if it is not contiguous. "a1" can be replaced with different row/column ranges to constrain the variable. (Sorry for not knowing the source of this code, but have been using this for a while)

I'm not sure if the code will run seamless, or will loop exactly the range you need. But give it a go and see if it works. Hopefully the principle will guide you to a solution:

Sub LoopAcrossColsRows()
Dim varC As Integer
Dim varR As Integer
Dim LastCol As Integer
Dim LastRow As Long
Dim lr As Long

Worksheets("Sheet1").Activate
LastRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
LastCol = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column

For varC = 2 To LastCol
    For varR = (LastRow + 3) To LastRow Step -1
        Worksheets("Sheet1").Cells(varR, varC).FormulaR1C1 = "=SUMPRODUCT(--(R6C:R7C1>=RC1),--(R6C:R7C1<=(RC1+30))*R4C)"
    Next varR
Next varC

End Sub

Upvotes: 0

Harunojikan
Harunojikan

Reputation: 577

I believe you need the code to be 

Activesheet.Cells(R,C)...

If you don't want to use name to call worksheets, then use index

worksheet(1).cells(x,y)...

however this is only useful if you never change the order of worksheet. Because worksheet(1) always goes to the first sheet of the workbook.

also you can set a variable as worksheet and use that

Dim ws1 As Worksheet
'or set it = Worksheet(1)
Set ws1 = Worksheets("sheet"1)
ws1.cells(1,1) = "test"

For the name problem you can try this

Dim yourName as String
yourName = Application.InputBox("Enter name of worksheet")

worksheet(1).Name = yourName

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

  • You need to qualify the Cells property with an object
  • Your error message from the VB interface is due to the fact that your first quote mark is a left double quote and not a regular quote mark.

Try

Cells(R, C).FormulaR1C1 = "=SUMPRODUCT(--(R6C:R7C>=RC1), --(R6C:R7C<=(RC1+30))*R4C)"

And better than looping would be a routine that assigns the formula to a multicell range object.

Not Tested:

range(cells(lr+3,2),cells(lastrow,lastcol)).FormulaR1C1 = "=SUMPRODUCT(--(R6C:R7C>=RC1), --(R6C:R7C<=(RC1+30))*R4C)"

Upvotes: 2

Zev Spitz
Zev Spitz

Reputation: 15327

The line 

.Cells(R, C)...

should probably be qualified:

ActiveSheet.Cells(R,C)...

Better yet, you should retain a reference to the appropriate worksheet in a variable, instead of depending on it being the active sheet for the length of the For loop:

Dim wks As Worksheet
Set wks=Worksheets("Sheet1")
...
    For R = lr + 3 To LastRow
        wks.Cells(R, C).FormulaR1C1 = “=SUMPRODUCT(--(R6C:R7C>=RC1), --(R6C:R7C<=(RC1+30))*R4C)"
    Next R
...

Your aside is an entirely separate question and doesn't belong here, but consider using the Excel Application.Inputbox function, or the VBA Inputbox function.

Upvotes: 0

Related Questions