Excel VBA - Compile Error - Object Required

Question

This is my first post to this wonderful forum . I am new to VBA and have been writing my first meaningful macro beside doing some practice while learning.

While the WIP code is reproduced below, let me give you a background.

Have a a sheet to calculation investment maturity based on various inputs like Term of Payment and term of Maturity.

This sheet has all the formule in place to compute the maturity value.

Now on sheet 2, I am creating a matrix structure with a intersection of payment term and term of maturity. For ex combination of 1 Yr of Payment term with maturity ( 1...25) and next Payment term and so on.

I am writing a macro which will pick up the user choice from sheet 1 and then loop the macro in matrix and capture the result and paste it to sheet two .

I am still far away from what i have to achive but got a code in first few line of selecting and setting the payment term and maturity terms

Error is Compile error- Object required. I am not able to figure out what it it. WIP code is reproduced below

Private Sub Simulation_Click()
Dim PPT As String
Dim Term As String
MsgBox (" The Programme is starting now")

Set PPT = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G2").Value
Set Term = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G3").Value

For PPT = 1 To PPT  
    For Term = 1 To Term  
     ' Do something here  
    Next Term  
Next PPT  

End Sub

Please help EDIT # 1- September 19,2017, 3.10 AM IST Hi Experts, Finally my first VBA code is running . Its slow but its running. One last problem I have issue in Code line "Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2").Copy Workbooks("himanshu.xlsm").Sheets("What if").Cells(cols, Rowsh)" where while to source carries the right value( checked in debug Mode) but when it is pasted to target, it is pasted as 0. When I checked the target sheet , somehow it is showing me a formula (= Max( R1C1 :X1Y1). Please note that from where I am copyiong the value ( Source) , its a excel calculation which is arrived as a formula of selecting max of col J . I suspect that code is copying the formula instead of value. Please advice

Option Explicit
Sub macro21()
'MsgBox ("Start")
Dim i As Integer, j As Integer
Dim PPT As Integer
Dim Term As Integer
Dim pptrange As Range    ' Address of the Cell where PPT is maintained in Sheet 1
Dim termrange As Range   ' Address of the Cell where Term is maintained in Sheet 1
Dim cols As Integer
Dim Rowsh As Integer

PPT = 3
Term = 5
cols = 3
Rowsh = 3
For i = 1 To PPT

    For j = 1 To Term
        
        Set pptrange = Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("G2")  ' Set pptrange and termrange locations
        Set termrange = Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("G3")   ' Set pptrange and termrange locations
        pptrange.Value = i
        termrange.Value = j
        ***Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2").Copy Workbooks("himanshu.xlsm").Sheets("What if").Cells(cols, Rowsh)***
'        MsgBox ("Value is " & Cells(cols, Rowsh).Value)
        MsgBox ("Value is " & Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2"))
        
        
        If j < Term Then
            cols = cols
            Rowsh = Rowsh + 1
        End If
    Next j
    cols = cols + 1
    Rowsh = 3
Next i

End Sub

I have issue in Code line "Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2").Copy Workbooks("himanshu.xlsm").Sheets("What if").Cells(cols, Rowsh)" where while to source carries the right value( checked in debug Mode) but when it is pasted to target, it is pasted as 0. When I checked the target sheet , somehow it is showing me a formula (= Max( R1C1 :X1Y1). Please note that from where I am copyiong the value ( Source) , its a excel calculation which is arrived as a formula of selecting max of col J . I suspect that code is copying the formula instead of value. Please advice

For strings, don't use Set. Just do PPT = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G2").Value and Term = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G3").Value — dwirony
After you get rid of Set, change Dim PPT As String to Dim PPT As Long and Dim Term As String to Dim Term As Long. — user4039065

Victor K Victor K · Accepted Answer · 2017-09-18T15:28:30

PPT is a string so you should use:

PPT = ...

and not

 Set PPT =

Set is used to assign object variables such as workbooks, worksheets, etc.

Edit: as Jeeped mentioned in his comment you have to change data type. You cannot use string in a loop like that. Change Term and PPT to Integer or Long.

Excel VBA - Compile Error - Object Required

1 Answers