Increment a Formula

user_18970544 0
Viewed 80

Okay so basically this is probably really simple but I can't find anything online anywhere that matches what I need.

(Sheet1) https://i.stack.imgur.com/TeVyJ.png (Sheet2) https://i.stack.imgur.com/AUM9z.png

I have a table with data and each row is a record essentially (Sheet1). I have a separate sheet where I want to be able to cycle through each record individually (Sheet2).

So right now I have each cell on Sheet2 equal to the top record on Sheet1 and it looks like this for formulas, =Sheet1!F5, =Sheet1!G5, =Sheet1!H5, etc.

So I'm taking a single row and just formatting it differently to be printed individually if that makes sense. Problem is that I don't know the easiest way to do this, so what I have in mind is to increment the formula by one when I press a button.

Example: =Sheet1!F5 --> =Sheet1!F6

Problem is I don't know how to increment a formula using VB.

vba excel
2 Answers
Sub Rectangle1_Click()
   Dim a As Integer
   a = 10
   
   For i = 0 To a Step 1
      MsgBox "Sheet1!F" & i
   Next
End Sub

This code will loop and show alerts from Sheet1!F1 to Sheet1!F10

user_189131990

If you want to store formulas like =Sheet1!F5 in B1:B3, then we can use this lines to make it work:

Private Sub CommandButton1_Click()
Dim Cell As Range
Dim FirstRow&, LastRow&, RowsCount&, Shift&
Const Source = "Table1"
Const Holder = "B1:B3"
  With Application.Range(Source)
    FirstRow = .Row
    RowsCount = .Rows.Count
    LastRow = FirstRow + RowsCount - 1
  End With
  For Each Cell In Range(Holder)
    Address = Cell.Formula
    Address = Right(Address, Len(Address) - 1)   ' drop "=" at the beginning
    Shift = 1
    If Application.Range(Address).Row = LastRow Then
      Shift = Shift - RowsCount
    End If
    Cell.Formula = "=" & Application.Range(Address).Offset(Shift).Address(External:=True)
  Next Cell
End Sub

We can make it shorter. Let's copy data from the table without any formula.

Sub CommandButton1_Click()
Static CurrentRow as Long
    With Application.Range("Table1")
        CurrentRow = 1 + CurrentRow Mod .Rows.Count
        Range("B1:B3") = WorksheetFunction.Transpose(.Rows(CurrentRow))
    End With
End Sub

You may want to read this:

p.s. Substitute Table1 (a table name with data) and B1:B3 (a range to hold a record) with appropriate values.

user_149096210
Related