Setting Cell as Calculated Value VBA Help

[JulieSB]

With the code below, I am trying to add two values and then set a cell as that value. Currently this code is putting a 0 in the target cell instead of the two defined values i am trying to add. I have underlined the areas i think are the issue. Any help on why this is not setting the cell to the added value would be appreciated.

Thanks!

Dim DateVal As Date
Dim OffsetVal As Long
Dim NewVal As Integer
Dim LoanVal As Long
Dim d As Long
Dim numDates As Long


Sheets("Balance Sheet Calculations").Select
Range("FirstDate2").Select
numDates = Range(Selection, Selection.End(xlToRight)).Columns.Count 'counts the number of dates


For d = 0 To numDates - 1 'looks at all dates in tab
Sheets("Balance Sheet Calculations").Select
Range("FirstDate2").Offset(0, d).Select 'selects and stores the date - offsets to next date every iteration
DateVal = ActiveCell.Value
OffsetVal = ActiveCell.Offset(10, 0).Value
If OffsetVal > 0 Then
With Range("InterCoIssueDate")
Set c = .Find(DateVal, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, 3) = LoanVal
NewVal = LoanVal + OffsetVal
Set ActiveCell.Value = NewVal
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End If


Next d

 

[JLGWhiz]

Do
 c.Offset(0, 3) = LoanVal
 NewVal = LoanVal + OffsetVal
 Set ActiveCell.Value = NewVal
 Loop While Not c Is Nothing And c.Address <> firstAddress

This loop is useless as currently written. When it reads the Loop line, it will exit the Do...Loop because the value of c.Address has not changed. To get it to loop through your 'InterCoIssueDate' range, you need a FindNext statement added in before the Loop line.

Set c = .FindNext(c)

I don't know why you got a zero value because I cannot see your worksheet to see what the value should be. You can step through the code, using the F8 key, to see what values the range variables contain as the highlight passes over them and initializes them. Just hover the mouse pointer over the variables and the tool tips should show the values. Or you can use the immediate window to monitor the values.

 

[JulieSB]

Thanks! This and a few other syntax tweaks did the trick

 

[JLGWhiz]

Thanks! This and a few other syntax tweaks did the trick

You're welcome,
Regards, JLG