none
Referencing successive cells in Loop RRS feed

  • Question

  •  I have written a program but there is a problem in that for which i need some immediate help and advise possible. 
    I have pasted the code below:
    Problem: column E2 should be the product of D2-C2 but though it looks to be a simplest program the referencing thing not happening for successive row range. 
    Please help.

    Option Explicit
    Sub main()
    Dim val As Double
    Dim Firstrow As Integer
    Dim Lastrow As Integer
    Dim totscore1 As Integer
    Dim i As Integer, j As Integer
    Sheets("sheet1").Select
    Range("D2").Select
    Firstrow = ActiveCell.row
    Selection.End(xlDown).Select
    Lastrow = ActiveCell.row
    totscore1 = Lastrow - Firstrow
    Range("E2").Select
    For i = 0 To totscore1
    val = Cells(2, 3).Value - Cells(2, 4).Value
    ActiveCell.Value = val
    ActiveCell.Offset(1, 0).Select
    Next i

    End Sub

    I get the same value -75 printed throughout. 
    Monday, March 28, 2016 10:48 AM

Answers

  • The line

    val = Cells(2, 3).Value - Cells(2, 4).Value

    uses row 2 throughout the entire loop. It should be

    val = Cells(FirstRow + i, 3).Value - Cells(FirstRow + i, 4).Value

    A more efficient version:

    Sub main()
        Dim val As Double
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim r As Long
        Application.ScreenUpdating = False
        Sheets("sheet1").Select
        Firstrow = Range("D2").Row
        Lastrow = Range("D2").End(xlDown).Row
        For r = Firstrow To Lastrow
            Cells(r, 5).Value = Cells(r, 3).Value - Cells(r, 4).Value
        Next r
        Application.ScreenUpdating = True
    End Sub

    This version avoids selecting cells, and doesn't update the screen until all values have been written.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ifabi Monday, March 28, 2016 12:27 PM
    Monday, March 28, 2016 11:44 AM

All replies

  • The line

    val = Cells(2, 3).Value - Cells(2, 4).Value

    uses row 2 throughout the entire loop. It should be

    val = Cells(FirstRow + i, 3).Value - Cells(FirstRow + i, 4).Value

    A more efficient version:

    Sub main()
        Dim val As Double
        Dim Firstrow As Long
        Dim Lastrow As Long
        Dim r As Long
        Application.ScreenUpdating = False
        Sheets("sheet1").Select
        Firstrow = Range("D2").Row
        Lastrow = Range("D2").End(xlDown).Row
        For r = Firstrow To Lastrow
            Cells(r, 5).Value = Cells(r, 3).Value - Cells(r, 4).Value
        Next r
        Application.ScreenUpdating = True
    End Sub

    This version avoids selecting cells, and doesn't update the screen until all values have been written.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by ifabi Monday, March 28, 2016 12:27 PM
    Monday, March 28, 2016 11:44 AM
  • thanks Hans.
    Monday, March 28, 2016 12:27 PM