VBA Vlookup

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Paddy

What do you want to replace the filenamewith?

Is it the file that has been returned by GetOpenFilename?
Code:
 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

Well-known Member
Joined
Sep 23, 2005
Messages
608
Hi Norie , thanks for helping

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
Sorry I don't understand what you mean.
 

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,915
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
 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

Well-known Member
Joined
Sep 23, 2005
Messages
608
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,916
Messages
5,545,022
Members
410,647
Latest member
bernardazar
Top