VBA Vlookup

[Paddy1979]

Hello All,

Please can you help me .

The below Vlookup searches for this filename 'INNS148_-_Service_Provider_Stocks_Detail.xls .However the filename may now vary crashing the macro.

Can i remove the filename from the code by replacing it with 'newactive' or something similar, i have tried but i couldnt get it to work?

Sub Max_Stock()

Dim strFileName As Variant
Dim jstFileName As Variant

Set OldActive = ActiveSheet

strFileName = Application.GetOpenFilename(Title:="Open INNS148 Report")
If strFileName = False Then
MsgBox "No File Selected"
Exit Sub
End If

openfile
Workbooks.OpenText Filename:=strFileName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)

Set newactive = ActiveSheet

OldActive.Activate 'Return to sheet you came from

'Insert formulas
With Range("D9:D2999")
.Value = "=VLOOKUP(RC[-3],'[INNS148_-_Service_Provider_Stocks_Detail.xls]SP Stock Items at Part Level'!R4C5:R5000C9,5,0)/13*1.2,0)))"


Cheers
Paddy

 

[Norie]

Paddy

What do you want to replace the filenamewith?

Is it the file that has been returned by GetOpenFilename?

 With Range("D9:D2999")
.Value = "=VLOOKUP(RC[-3],'[" & Dir(strFileName) &"]SP Stock Items at Part Level'!R4C5:R5000C9,5,0)/13*1.2,0)))"

 

[Paddy1979]

Hi Norie , thanks for helping

it is the file that has been returned by GetOpenFilename, could you rename it as 'stock'?

 

[Norie]

Sorry I don't understand what you mean.

 

[Paddy1979]

Hi Norie ,

I cant get the new bit of code to work.

I didnt put all my code down before to save space, but this is it to the vlookup, can you see why the vlookup is debugging,

Can you see why it is not working

thanks a lot

Sub Max_Stock()

Dim strFileName As Variant
Dim jstFileName As Variant

Set OldActive = ActiveSheet

MsgBox ("Please open the INNS148 Spreadsheet")

'select file
strFileName = Application.GetOpenFilename(Title:="Open INNS148 Report")
If strFileName = False Then
MsgBox "No File Selected"
Exit Sub
End If

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'openfile
Workbooks.OpenText Filename:=strFileName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)

'Remove leading string from file name

jstFileName = strFileName
strfnlen = Len(strFileName)
intcpos = 1
Do While intcpos > 0
intcpos = InStr(1, jstFileName, "\")
jstFileName = Right(jstFileName, strfnlen - intcpos)
strfnlen = Len(jstFileName)
Loop

Set newactive = ActiveSheet

OldActive.Activate 'Return to sheet you came from

'Insert headers
Sheets("Sheet1").Visible = True
Sheets("Sheet1").Range("A1:D1").Copy Sheets("Order Form").Range("D7:G7")
Application.CutCopyMode = False
Sheets("Sheet1").Visible = False

'Insert formulas
With Range("D9:D2999")
.Value = "=IF(ISBLANK(RC[-2]),"""",IF(LEFT(RC[-3],1)=""R"","" "",ROUND(VLOOKUP(RC[-3]],'[" & Dir(jstFileName) & "]SP Stock Items at Part Level'!R4C5:R5000C9,5,0)/13*1.2,0)

 

[Norie]

Why did you change strFileName to jstFileName?

The reason I used Dir was to extract only the filename, just a different technique to what you are doing here.

 Do While intcpos > 0
intcpos = InStr(1, jstFileName, "\")
jstFileName = Right(jstFileName, strfnlen - intcpos)
strfnlen = Len(jstFileName)
Loop

By the way if you are using Excel 2000 or later you might want to look at InStrRev.

 

[Paddy1979]

Thanks for the reply.

Not sure as I didnt right this code originally so i dont know why the filename goes from str to jst, i have just been asked to make some changes.