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

Subscript out of range

Krishna2385

New Member
Error while trying to activate the worksheet, am able to get the open the workbook run the lookup , however when trying to activate the same worksheet from which I have done the lookup, it throws an error as Subscript out of range

<<< use code -tags >>>
Code:
MsgBox ("Choose PDMT")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set PDMT = Workbooks(FIleNm)
Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row
Range("P2", "P" & Lrow).Formula = "= Vlookup(O2,'[" & FIleNm & "]Original data'!$O:$P,2,0)"
PDMT.Activate
Sheets("Original Data").Select
Lrow = Range("A4").End(xlDown).Row
Range("A4", "Q" & Lrow).ClearContents
 
Last edited by a moderator:

Krishna2385

New Member
Workingfile is the one where I do the copy paste and lookup activities and paste the final output on to PDMT file on Sheet 2 i.e "Original data"

Havent pasted the entire code … below is the entire code



Code:
Sub Macro()

Dim Workingfile As Workbook
Dim PSdata As Workbook
Dim PDMT As Workbook
Dim Commodityfile As Workbook
Dim Segmentfile As Workbook
Dim Marketfile As Workbook
Dim Lrow As Long
Set Workingfile = ActiveWorkbook



'fetching PSdata into workingfile
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
Lrow = Range("A4").End(xlDown).Row
Range("A4", "L" & Lrow).SpecialCells(xlCellTypeVisible).Copy


Workingfile.Activate
Sheets("Sheet1").Activate

Range("A1").PasteSpecial xlPasteFormulasAndNumberFormats

PSdata.Close
Workingfile.Activate

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


MsgBox ("Choose Commodityfile")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set Commodityfile = Workbooks(FIleNm)

Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("C2").End(xlDown).Row

'Range("D2", "D" & Lrow).Formula = "= Vlookup(C2,'[" & FileNm & "]Sheet1'!$A:$C,3,0)"
Range("D2", "D" & Lrow).Formula = "=C2&"" - ""&Vlookup(C2,'[" & FIleNm & "]Sheet1'!$A:$C,3,0)"
Range("E2", "E" & Lrow).Formula = "= Vlookup(C2,'[" & FIleNm & "]Sheet1'!$A:$D,4,0)"

Commodityfile.Close

MsgBox ("Choose Segmentfile")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set Segmentfile = Workbooks(FIleNm)

Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row

Range("K2", "K" & Lrow).Formula = "= Vlookup(J2,'[" & FIleNm & "]Sheet1'!$B:$M,12,0)"

Segmentfile.Close

MsgBox ("Choose Marketfile")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set Marketfile = Workbooks(FIleNm)

Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row

Range("L2", "L" & Lrow).Formula = "= Vlookup(K2,'[" & FIleNm & "]Sheet1'!$A:$B,2,0)"

Marketfile.Close


MsgBox ("Choose PDMT")
FilePath = Application.GetOpenFilename
FilePos = InStrRev(FilePath, "\")
FIleNm = Right(FilePath, Len(FilePath) - FilePos)
Workbooks.Open Filename:=FilePath
Set PDMT = Workbooks(FIleNm)

Workingfile.Activate
Sheets("Sheet1").Activate
Lrow = Range("J2").End(xlDown).Row

Range("P2", "P" & Lrow).Formula = "= Vlookup(O2,'[" & FIleNm & "]Original data'!$O:$P,2,0)"

PDMT.Activate
Sheets("Original Data").Select
Lrow = Range("A4").End(xlDown).Row
Range("A4", "Q" & Lrow).ClearContents

Workingfile.Activate
Sheets("Sheet1").Activate
Range("A2", "Q" & Lrow).Copy

PDMT.Activate
Sheets("Original data").Activate
Range("A4").PasteSpecial xlPasteValues



End Sub
 
Last edited by a moderator:
Top