Application defined or object error

[Krishna2385]

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).

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

 

[NARAYANK991]

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]

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]

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]

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

 

[Krishna2385]

Yet there is an error.....

 

[NARAYANK991]

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]

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