CODEWITHSUNDEEP
excelvbareferencecell
chrisphils26
chrisphils26

Reputation: 39

Referencing a Numerical Value From a Cell in Excel VBA Code

For the code below, I want the value of "x" to be variable based on the values of cells in Excel, say the years 2000 and 1995. Given these two years, I want the value of "x" to be 5 (2000 - 1995). I have tried a few different things, but haven't been able to get it to work.

Dim i As Integer 
For i = 1 To 5 
    rng.Select 
    Selection.End(xlDown).Select 
    ActiveCell.Offset(1, 0).Range("A1").Select 
    ActiveCell.FormulaR1C1 = "=R[-1]C/R122C" 
Next i

Upvotes: 0

Views: 331

Answers (1)

Darrell H
Darrell H

Reputation: 1886

The number of possibilities are endless as to ranges you want to loop. You can create variables and perform functions to find whatever numbers you want to find. You can count, sum, etc. Be aware that variables have limits (Integer versus Long). In the example below I created a MinYear and MaxYear assuming that the range contained years. If it was dates, you would have to find the formula to extract years. You can use results to define the loop or do the math to find the difference as suggested.

Sub LoopOnCellValues()

    Dim i As Integer
    Dim MinYear As Integer
    Dim MaxYear As Integer
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    MinYear = WorksheetFunction.Min(ws.Range(ws.Cells(2, 2), ws.Cells(2, 100)))
    MaxYear = WorksheetFunction.Max(ws.Range(ws.Cells(2, 2), ws.Cells(2, 100)))

    For i = MinYear To MaxYear
        'do something
    Next i

    'or

    For i = 1 To (MaxYear - MinYear)
        'do something
    Next i

End Sub

Upvotes: 1

Related Questions