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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
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
76,303
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
76,303
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.
 

Forum statistics

Threads
1,140,937
Messages
5,703,260
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top