Complicated contract pricing vba formula

[hfler]

Dim contractmonth As Variant
Dim px1 As Variant
Dim px2 As Variant
Dim i As Variant
 
Range("firstpay").Value = contractmonth
Range("firstpay").Offset(0, 1).Value = px1
Range("firstpay").Offset(0, 1).Activate
For i = 0 To WorksheetFunction.CountA(Range("froncontract", _
Range("frontcontract").End(xlDown))) Step 1
If WorksheetFunction.EDate(contractmonth, i + 1) < ActiveCell.Offset(i + 1, 0).Value Then
px2 = px1 + ActiveCell.Value
px2 = px1
Else: Next i
px2 = px1 + ActiveCell.Offset(i, 0).Value
px2 = px1
ActiveCell.Offset(i, 0).Activate
contractmonth = ActiveCell.Value
End If

I will preface this by saying I am a very, very green VBA user. This is a complicated loop with (I'm sure) many problems. I've summarized my intentions and observations below:

-First, it's apparent that you can't have an IF inside of FOR loop - what's the solution to this?

-The CountA function in the loop is simply serving to have the loop stop when it reaches the last cell in the column - it works.

-The contract months occur on uneven months - so if next month is less than the next contract in the future, the code should stick with the previous months contract.

-At the end of each month, if the code does not move on to the next month's contract, it will add the value from the previous month's contract to the existing price. That's why I need a second price variable (px2), so that px1 can reset to the new value (i.e. all of the previous month's contracts added to the new contract).

-If next month is greater than the next month's contract date, the code should roll into the next contract. This will reset the contractmonth to the ActiveCell, allowing the code to repeat.

Any advice would be infinitely appreciated. As a green user, this is really perplexing to me. I think my biggest issue is not being able to use IFs inside of FOR loops. Thanks so much for any insight!

 

[Andrew Poulsom]

You can have an If clause within a For loop, but like this:

For i = 1 To 10
    If a = b Then
'        Code if True
    Else
'        Code if False
    End If
Next i

 

[hfler]

Andrew thank you very much, your advice worked. My code still does not (as I said, I think there are quite a few errors). Here's what I have now:

Dim contractmonth As Variant
Dim px1 As Variant
Dim px2 As Variant
Dim i As Variant

Range("firstpay").Value = contractmonth
Range("average").Value = px1
Range("firstpay").Activate
'active cell should always be in futures first notice date column
For i = 0 To WorksheetFunction.CountA(Range("frontcontract", Range("frontcontract").End(xlDown))) Step 1
    If WorksheetFunction.EDate(contractmonth, i + 1) < ActiveCell.Value Then
    px2 = px1 + ActiveCell.Offset(i, 1).Value
    px2 = px1
    Else:
    px2 = px1 + ActiveCell.Offset(i + 1, 1).Value
    px2 = px1
    ActiveCell.Offset(0, 1).Activate
    contractmonth = ActiveCell.Value
    End If
Next i
Range("sumprice").Activate
ActiveCell.Value = px1

Okay, I figured out how to incorporate my IF statement into the loop. All this code ends up doing though is making the "firstpay" and "average" cells blank, and then breaking and saying "Type Mismatch". Once again, any advice, insight, or thoughts are much appreciated. Thank you!

 

[hfler]

Okay, I think I've identified why the "firstpay" and "average" ranges go blank - it's because I'm setting them equal to variables that have no value. How do I set the variables equal to the values in those cells?

 

[Andrew Poulsom]

Try changing:

Range("firstpay").Value = contractmonth
Range("average").Value = px1

to:

contractmonth = Range("firstpay").Value
px1 = Range("average").Value

In an assignment statement the variable to be assigned a value is on the left and the value to assign is on the right.

 

[hfler]

That worked. Thanks!