Help with modifying this code

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
Someone gave me this code and I am trying to use it in another workbook where the location of items are different.

I modified most of it successfully. But its putting the results in Columns A and B

I need it to put these values in R and S (its currently putting the results in the correct row - its just the columns I cant seen to adjust).

Code:
Private Sub CommandButton1_Click()
    'Changed
'    Range("R8:S501").ClearContents
    
    Application.Calculation = xlManual

    Dim CurRow As Integer
    Dim NumMonths As Integer
    Dim Amount As Double
    Dim Dcurv As Integer
         
    'Changed
    Dcurv = Cells(2, 19).Value
    NumMonths = Cells(6, 19).Value
    Amount = Cells(3, 19).Value
    
    Range(Cells(8, 18), Cells(200, 19)).ClearContents
    
    CurRow = 7

    a = 1 / NumMonths
    W = Amount
    Min = 1
    'All other curves-------------
    BOG = 0.01 * Dcurv
    S = BOG
    For i = 1 To 40
       S = Sqr(BOG / (3 - (2 * S)))
    Next
    p = 0
    CL = 0
    For K = 1 To NumMonths
       Cells(K + CurRow, 1).Value = K
       p = p + a
       X = (1 - 2 * S) * p * (((-4 * p + 8) * p - 3) * p) + 2 * S * p
       C = X * X * (3 - 2 * X)
       XX = Min
       Cells(K + CurRow, 2).Value = ((C - CL) * XX * W)
       CL = C
       
       If K = 1 Then
        Cells(K + CurRow, 1).Formula = "=EOMONTH(S4,0)"
        Cells(K + CurRow, 1).NumberFormat = "mmm-yy"
       Else
        Cells(K + CurRow, 1).FormulaR1C1 = "=EOMONTH(R[-1]C,1)"
        Cells(K + CurRow, 1).NumberFormat = "mmm-yy"
       End If
     Next
End Sub

Thanks!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

KyleX

New Member
Joined
Jul 11, 2017
Messages
14
See where it says

Rich (BB code):
cells(K + CurRow, 2)

The cells function is the range of a single cell that works like

Code:
cells(row#, column#)

The 2 in the column position equates to column B and the 1 you see in other parts of your code is column A.
I believe R is column 18 and S is 19.

Where it says

Rich (BB code):
Cells(K + CurRow, 2).Value = 

Whatever comes after that equals sign will be the value for that range.
 
Last edited:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,742
Office Version
  1. 365
Platform
  1. Windows
Thank you, I appreciate the help. I was able to fix it with your explanation.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,075
Messages
5,526,667
Members
409,714
Latest member
diamondjoechubbs

This Week's Hot Topics

Top