CODEWITHSUNDEEP
excelvbaaccpac
Fenny
Fenny

Reputation: 41

VBA xlUp error in macro

can anyone help me? i have this syntax

Dim oExcel As Variant
Dim oWB As Variant

'Set oWB = CreateObject("Object.Workbook")
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("Z:\MPR\Maret 2017\31 - 03 -2017\DF\PAL - DF.xlsx")
oWB.Sheets(1).select
Dim oNumRow As Integer

'oNumRow = oWB.Sheets(1).UsedRange.Count
With oWB
    oNumRow = oWB.Sheets(1).Cells(oWB.Sheets(1).Rows.Count, "A").End(xlUp).row 'error
End With
oNumRow = oWB.Sheets(1).Cells(oWB.Sheets(1).Rows.Count, 1).End.row 'error
oNumRow = oWB.Sheets(1).Range("A1", oWB.Sheets(1).Range("A1").End(xlDown).row).Rows.Count'error

if i change the Dim oExcel As Variant to Dim oExcel As Excel.Application, the program error in there. Can anyone suggest any idea? I'm new in programing vba for macro

Upvotes: -1

Views: 1399

Answers (1)

YowE3K
YowE3K

Reputation: 23974

If you are not running your code in Excel VBA, or otherwise have a reference to the Microsoft Excel Object Library, then none of the constants defined in that library will be available to your code. You will have to set their values yourself.

You could add the following lines to the start of your code:

Const xlUp As Long = -4162
Const xlDown As Long = -4121

They were the only two constants I could see in your posted code but, if you have others, they should be added to your code as well.

Upvotes: 0

Related Questions