CODEWITHSUNDEEP
vba
Mastromichalis Alex
Mastromichalis Alex

Reputation: 1

sub or function not defined

My code is written below. When I run it, I get a

1004 Application-defined or object defined error

in this line:

Set MLNRange = Sheets("GJR GARCH").Range(Cells(2, j).Value, Cells(1063, j + 4).Value)

I am new to VBA and I don't understand what's going wrong.

Sub MLNRealworldtransfer()
Dim MLNRange As Range
Dim MLNDensity As Double
For i = 10 To 23
For j = 1 To 116
Set MLNRange = Sheets("GJR GARCH").Range(Cells(2, j).Value, Cells(1063, j + 4).Value)
MLNDensity = Application.WorksheetFunction.VLookup(Sheets("GJR GARCH").Cells(i, 122).Value, MLNRange, 5, "True")
MLNDensity = Sheets("GJR GARCH").Cells(i, 125)
i = i + 1
Next
j = j + 9
Next

Edit 1:

I made changes and I take compile error sub or function not defined in the following code

Sub MLNRealworldtran()
Dim MLNRange As Range
Dim MLNDensity As Double
For i = 10 To 23 Step 2
For j = 1 To 116 Step 10
Set MLNRange = Sheets("GJR GARCH").Range(Cells(2, j), Cells(1063, j + 4))
MLNDensity = Application.WorksheetFunction.VLookup(Sheets("GJR     GARCH").Cells(i, 122).Value, MLNRange, 5, "True")
MLNDensity = Sheets("GJR GARCH").Cells(i, 131)
Next i
Next j
End Sub

Edit 2: you had "nexti" and "nextj" which is what was throwing the error. Should be "Next i" and "Next j"

Upvotes: 0

Views: 195

Answers (2)

Kaz
Kaz

Reputation: 1332

You have multiple problems. First, 

Set MLNRange = Sheets("GJR GARCH").Range(Cells(2, j).Value, Cells(1063, j + 4).Value)

.value isn't a range, it's a property. Just use the cell references

    Set MLNRange = Sheets("GJR GARCH").Range(Cells(2, j), Cells(1063, j + 4))

Second, your increment variables aren't nested correctly

For i = 1 to 10
for j = 1 to 10
next i
next j

'/ should be

for i
for j

next j
next i

You can define steps in the for statement. E.g. 

For i = 10 to 23 step 2
for j = 1 to 116 step 10
'/ Code
next j
next i

i will then go 10, 12, 14, 16 etc. and j will go 1, 11, 21, 31 etc.

Rather than referencing Sheets("GJR GARCH") everywhere (which is messy and a pain to change). Define your worksheet as variable then reference that.

Dim wsData as Worksheet
    wsData = Sheets("GJR GARCH")

    MLNDensity = wsData.Cells(i, 125)

Upvotes: 1

Andre
Andre

Reputation: 27634

.Value don't belong in there:

Set MLNRange = Sheets("GJR GARCH").Range(Cells(2, j), Cells(1063, j + 4))

Also your incrementing of loop variables i and j inside the loops seems odd. You can write e.g.

For i = 10 To 23 Step 2

Upvotes: 0

Related Questions