Adjust code - identify number of months and the Min and Max months

[gheyman]

I have this code where currently the Number of Months is a number in a cell (I2) =ROUND((H2-G2)/365*12,0)+1 and the min date from calculated in (G2) =MIN(G$4:G$1048576) and the Max date is calculated in (H2) =MAX(H$3:H$1048576) and number of rows is calculated in (F2) =COUNTA((A:A))

Can these be incorporated into the VBA code below so that they are not on the sheet? can the code calculate these values and use them within the code?

Sub Button5_Click()

Range(Cells(3, 10), Cells(1000, 1000)).ClearContents
Dim min As Date
Dim no_of_months As Integer
no_of_months = Cells(2, 9)
min = Cells(2, 7)
Cells(3, 10) = no_of_months

For i = 0 To (no_of_months - 1)
    Cells(3, 10 + i) = DateAdd("m", i, min)
Next

Dim Spread_amount As Integer
Dim no_of_item_months As Integer

For x = 4 To Cells(2, 6)

    curve = Left(Cells(x, 5), 2)
    start_date_col = DateDiff("m", min, Cells(x, 7))
    Worksheets(curve).Cells(1, 1) = Cells(x, 9)
    no_of_item_months = DateDiff("m", Cells(x, 7), Cells(x, 8)) + 1
    Cells(1, 1) = no_of_item_months
    Worksheets(curve).Cells(3, 7) = no_of_item_months
    
    Dim steps As Integer
    Worksheets(curve).Cells(1, 2) = "Hello"
    Range(Worksheets(curve).Cells(4, 7), Worksheets(curve).Cells(1000, 7)).ClearContents
    Range(Worksheets(curve).Cells(4, 8), Worksheets(curve).Cells(1000, 8)).ClearContents
    Range(Worksheets(curve).Cells(4, 9), Worksheets(curve).Cells(1000, 9)).ClearContents
    steps = Worksheets(curve).Cells(3, 7)
    For i = 1 To steps
        Worksheets(curve).Cells(3 + i, 7) = i / steps
        Worksheets(curve).Cells(4, 5) = i / steps
        Worksheets(curve).Cells(i + 3, 8) = Worksheets(curve).Cells(4, 6)
    Next

    Sum_all = Application.WorksheetFunction.Sum(Range(Worksheets(curve).Cells(4, 8), Worksheets(curve).Cells(1000, 8)))

    For i = 1 To steps
        Worksheets(curve).Cells(i + 3, 9) = Application.WorksheetFunction.Round(Worksheets(curve).Cells(i + 3, 8) / Sum_all * Worksheets(curve).Cells(1, 1), 0)
        Next
    Max = Worksheets(curve).Cells(1, 11)
    dif = Worksheets(curve).Cells(2, 11)
    Worksheets(curve).Cells(Max, 9) = Worksheets(curve).Cells(Max, 9) - dif
    
    start_date_col = DateDiff("m", min, Cells(x, 7))
    Cells(2, 1) = start_date_col
    
        For g = 0 To no_of_item_months - 1
            Cells(x, 10 + start_date_col + g) = Worksheets(curve).Cells(g + 4, 9)
        Next
    
    Cells(1, 1) = curve
Next
End Sub

Thanks!

 

[Joe4]

You can use most Excel functions in VBA with "Application.WorksheetFunction..." functionality.
Actually, you are already using that method in your code:

Sum_all = Application.WorksheetFunction.Sum(Range(Worksheets(curve).Cells(4, 8), Worksheets(curve).Cells(1000, 8)))

So, you would just do the same thing, set your calculation equal to a variable.
Then you can use that variable anywhere in your code.
Here is some more detail on that (and they actually even use MIN in one of their examples!: Using Excel worksheet functions in Visual Basic).