• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Application defined or object error

Krishna2385

New Member
I know this might be a silly issue but I tried all things which I learned from here.
I am receiving a Application defined error, and the Vlookup formula was working in my previous macros however this time it is a frustrating issue.

well I am trying to do a vlookup between two different workbooks and this is the code ( Please note I haven't completed the entire code I have a habit of executing and verifying if the code is working fine or ot and this is where I am receiving the error).

Code:
Sub macro()
Dim Workingfile As Workbook
Dim PDMT As Workbook
Dim PSdata As Workbook
Dim Commodityfile As Workbook
Dim Segmentfile  As Workbook
Dim Lrow As Integer
'Dim rng As String
'Dim Filepath As String
'Dim Filepos As String

Set Workingfile = Application.ActiveWorkbook
'Peoplesoft data
MsgBox ("Choose PSdata")
Filepath = Application.GetOpenFilename
Filepos = InStrRev(Filepath, "\")
Filenm = Right(Filepath, Len(Filepath) - Filepos)
Workbooks.Open Filename:=Filepath
Set PSdata = Workbooks(Filenm)
PSdata.Activate
LastRw = Range("A4").End(xlDown).Row
Range("A4", "L" & LastRw).SpecialCells(xlCellTypeVisible).Copy
Workingfile.Activate
Sheets("sheet1").Activate
Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Columns("D:D").Insert Shift:=xlToRight
Columns("E:E").Insert Shift:=xlToRight
Columns("K:K").Insert Shift:=xlToRight
Columns("L:L").Insert Shift:=xlToRight
Columns("P: P").Insert Shift:=xlToRight
'commodity descp & tower lookup
MsgBox ("Choose Commodityfile")
Filepath = Application.GetOpenFilename
Filepos = InStrRev(Filepath, "\")
Filenm1 = Right(Filepath, Len(Filepath) - Filepos)
Workbooks.Open Filename:=Filepath
Set Commodityfile = Workbooks(Filenm1)
Workingfile.Activate
Sheets("sheet1").Activate
Range("D5:D" & Lrow).Formula = "=VLOOKUP(C5,'[" & Filenm1 & "]Sheet1'!$A:$C,3,0)"

End Sub
 
Last edited by a moderator:

NARAYANK991

Excel Ninja
Hi ,

Which is the line in the code which generates the error ?

Have you tried debugging and seeing what the variables which are involved in the error generating statement contain ?

Narayan
 

Krishna2385

New Member
Hi it’s the last line where I am receiving the error .... It’s not giving an debug option , it’s crashing out with the message ...
 

NARAYANK991

Excel Ninja
Hi ,

These 3 statements should tell you the problem :

Dim Lrow As Integer

LastRw = Range("A4").End(xlDown).Row

Range("D5:D" & Lrow).Formula = "=VLOOKUP(C5,'[" & Filenm1 & "]Sheet1'!$A:$C,3,0)"

Always use the Option Explicit statement in your code.

Narayan
 

Krishna2385

New Member
Code:
Option Explicit
Sub macro()
Dim Workingfile As Workbook
Dim PDMT As Workbook
Dim PSdata As Workbook
Dim Commodityfile As Workbook
Dim Segmentfile  As Workbook
Dim Lrow As Integer
Dim rng As String
Dim Filepath As String
Dim Filepos As String
Dim Filenm As String
Dim Filenm1 As String



Set Workingfile = Application.ActiveWorkbook
'Peoplesoft data
MsgBox ("Choose PSdata")
Filepath = Application.GetOpenFilename
Filepos = InStrRev(Filepath, "\")
Filenm = Right(Filepath, Len(Filepath) - Filepos)
Workbooks.Open Filename:=Filepath
Set PSdata = Workbooks(Filenm)
PSdata.Activate
LastRw = Range("A4").End(xlDown).Row
Range("A4", "L" & LastRw).SpecialCells(xlCellTypeVisible).Copy
Workingfile.Activate
Sheets("sheet1").Activate
Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Columns("D:D").Insert Shift:=xlToRight
Columns("E:E").Insert Shift:=xlToRight
Columns("K:K").Insert Shift:=xlToRight
Columns("L:L").Insert Shift:=xlToRight
Columns("P:p").Insert Shift:=xlToRight
'commodity descp & tower lookup
MsgBox ("Choose Commodityfile")
Filepath = Application.GetOpenFilename
Filepos = InStrRev(Filepath, "\")
Filenm1 = Right(Filepath, Len(Filepath) - Filepos)
Workbooks.Open Filename:=Filepath
Set Commodityfile = Workbooks(Filenm1)
Workingfile.Activate
Sheets("sheet1").Activate
Range("D5:D" & Lrow).Formula = "=VLOOKUP(C5,'[" & Filenm1 & "]Sheet1'!$A:$C,3,0)"

End Sub
 
Last edited by a moderator:

NARAYANK991

Excel Ninja
Hi ,

Because you are declaring the variable named LRow , and initializing the variable named LastRw , and then using the variable LRow.

The declared variable LRow , because it has not been initialized , is by default having a value of 0.

When you try to assign a value to a range whose row number is 0, Excel errors out.

Narayan
 

Krishna2385

New Member
When I am declaring the variable Lrow in the entire code and initializing was giving Overflow error , so declared Lrow as Long .. its working

thank you Narayan.. was really helpful
 
Top