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
– dwirony, Commented Sep 18, 2017 at 15:28
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
– user4039065, Commented Sep 18, 2017 at 15:31

Victor K · Accepted Answer · 2017-09-18 15:41:02Z

3

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.

edited Sep 18, 2017 at 15:41

answered Sep 18, 2017 at 15:28

Victor K

1,0492 gold badges11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

dwirony Over a year ago

Hi Viktor, I edited your post to change SET to Set just for clarity, as SET in capitals isn't valid syntax (and will be changed by the IDE anyways).

Ravi Kant Agarwal Over a year ago

Hi Victor , dvirony and Jeeped thanks for your quick help . I am surprised with the speed of response itself from you all . I removed the same however got type mismatch. I thought PPT is a Microsoft object also hence changed my variable names and removed Set as well. Now the error is "Object Doesnt support this property or method

Ravi Kant Agarwal Over a year ago

Private Sub Simulation_Click() Dim ppt1 As String Dim Term1 As String 'MsgBox (" Sample Text") ppt1 = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G2").Value Term1 = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G3").Value For i = 1 To ppt1 For j = 1 To Term1 ActiveWorkbook.ActiveSheet("Sheet1").Select Range("G2").Value = 1 Range("G3").Value = 1 Range("c3").Value = Application.Worksheet("Sheet1").Function.Max(Range("N7:n31")).Value Next j Next i End Sub

dwirony Over a year ago

@RaviKantAgarwal As Jeeped commented above, you need to change your variables from String to Long, as you can't run a loop from 1 to a String

Ravi Kant Agarwal Over a year ago

Thanks .. error resolved .. While I am still facing another issue but I will prefer to first bang head on stack overflow so that I can learn and not bother you experts thanks everyone once again !!

|

Collectives™ on Stack Overflow

Excel VBA - Compile Error - Object Required

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related