Issue with For Loop

[jbenfleming]

This is my first post on mrexcel, I have read the FAQ and posting guidelines, but I apologize if make some beginner mistakes.

I am writing a macro to add the value of a cell from one workbook, to the total of a cell on another workbook, based on whether two date values are equal. Here is my code:

Sub ainvestment_record()
Dim rownum As Double, totdays As Double, day As Double, settledate As Double, totrows As Double


'determine number of days
    settledate = Range("e2").Value
    Workbooks.Open ("filepathhere")
    Sheets("MAR17").Range("a3").Select
    If Range("a3").Value = "" Then
        MsgBox ("No Data Exists"): Exit Sub
    ElseIf Range("a3").Value <> "" And Range("a4").Value = "" Then
        totdays = 1
    ElseIf Range("a3").Value <> "" And Range("a4").Value <> "" Then
        Selection.End(xlDown).Select: totrows = ActiveCell.Row
        totdays = totrows - 3
    End If
 
    Sheets("MAR17").Range("a3").Select
    rownum = 3
    For day = 1 To totdays
        ActiveCell.Offset((rownum - 3), 0).Select
        If ActiveCell.Value = settledate Then
            ActiveCell.Offset(0, 3).Value = "yes"
        Else
            ActiveCell.Offset(0, 3).Value = "no"
        End If
        rownum = rownum + 1
    Next day
    
    
End Sub

It works fine for the first two days. Then it skips the next row before returning a value. Then it skips 2 rows before returning a value. Then it skips 3 rows, and this pattern continues on until it reaches the last day, which should be in a33 in this case. But because of the skipped rows pattern that last day ends up in cell a468. I've used this type of ForLoop before and never had this issue. Can anyone see where I am going wrong?

FYI, The "yes" and "no" in the second IF statement are placeholders for a formula I will be writing in later.

 

[Jeffrey Mahoney]

I'm not able to test. The problem you had was that every time you increased the rownum, you used the

ActiveCell.Offset((rownum - 3), 0).Select

So, if you change the activecell in your loop, you are adding that many more rows to the equation. Use the For loop "day" value offset from a constant range.

I messed with your code a little. Selecting cells and changing activecell is a slow method. Referencing cells is more efficient.

Sub ainvestment_record()
  Dim rownum As Double, totdays As Double, day As Double, settledate As Double, totrows As Double
  Dim Cel As Range
  Dim WB As Workbook
  Dim WS As Worksheet
  Dim R3 As Range
  Dim R4 As Range


  'determine number of days
    settledate = ThisWorkbook.ActiveSheet.Range("e2").Value
    Workbooks.Open ("filepathhere")
    Set WB = ActiveWorkbook
    Set WS = WB.Sheets("MAR17")
    Set R3 = WS.Range("a3")
    Set R4 = WS.Range("a4")
    If R3.Value = "" Then
        MsgBox ("No Data Exists"): Exit Sub
    ElseIf R3.Value <> "" And R4.Value = "" Then
        totdays = 1
    ElseIf R3.Value <> "" And R4.Value <> "" Then
      totrows = R3.End(xlDown).Row
      totdays = totrows - 3
    End If
 
    Set Cel = WS.Range("A2")
    For day = 1 To totdays
      If Cel.Offset(day, 0).Value = settledate Then
          Cel.Offset(day, 3).Value = "yes"
      Else
          Cel.Offset(day, 3).Value = "no"
      End If
    Next day
    
    
End Sub

 

[jbenfleming]

Thank you for your reply, Jeffrey. I won't be able to test it until tomorrow, but I'll let you know how it goes.

 

[jbenfleming]

Nevermind, I was able to test it now. Thanks for noticing where I went wrong. I just added in "Range("a3").Select" to the first line of the loop and it works fine now. For my future projects I'll definitely use cell references as you suggested. Thanks again!