How to change data in a formula

[etradeguru]

Here is s snip of the code that I want to be able to enter a variable
into via a Input Box for the user to enter.

The piece I want to enter is the 20070904log.xls bit as this changes
on a daily basis.

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
Dir "",""Not a new Dir"")"

So any (polite!) suggestions would be welcome.

Thank you

Mark

 

[Guest]

dim fName as string

fname = InputBox("Please enter the filename", "File Name", "log.xls")
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\" & fname & "'!DirName,1,FALSE)),""This is a new or modified
Dir "",""Not a new Dir"")"

This does not do anything to check that what they entered is valid.

Peter Richardson

 

[JW]

Dim fInput As String
fInput = InputBox("Enter the file name", "Title Here")
If fInput = "" Or fInput = False Then Exit Sub
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\" & fInput & ".xls'!DirName,1,FALSE)),""This is a new or
modified Dir "",""Not a new Dir"")"

 

[JW]

ACK! Peter beat me to it.

-Jeff-

Dim fInput As String
fInput = InputBox("Enter the file name", "Title Here")
If fInput = "" Or fInput = False Then Exit Sub
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\" & fInput & ".xls'!DirName,1,FALSE)),""This is a new or
modified Dir "",""Not a new Dir"")"

Here is s snip of the code that I want to be able to enter a variable
into via a Input Box for the user to enter.

The piece I want to enter is the 20070904log.xls bit as this changes
on a daily basis.

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
Dir "",""Not a new Dir"")"

So any (polite!) suggestions would be welcome.

Thank you

Mark

 

[WhatUp]

Thanks a ton!

 

[Guest]

only by a couple minutes. That's the time it took you to add the check of
the input .

ACK! Peter beat me to it.

-Jeff-

Dim fInput As String
fInput = InputBox("Enter the file name", "Title Here")
If fInput = "" Or fInput = False Then Exit Sub
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\" & fInput & ".xls'!DirName,1,FALSE)),""This is a new or
modified Dir "",""Not a new Dir"")"

Here is s snip of the code that I want to be able to enter a variable
into via a Input Box for the user to enter.

The piece I want to enter is the 20070904log.xls bit as this changes
on a daily basis.

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-8],'N:\1._Document_Version_Control_Log
\Logs\20070904Log.xls'!DirName,1,FALSE)),""This is a new or modified
Dir "",""Not a new Dir"")"

So any (polite!) suggestions would be welcome.

Thank you

Mark

 

[Bill Renaud]

You could also try the following technique to get the filename (I prefer
this method, as it eliminates having to check the validity of the
filename):

Dim strFileName As String

strFileName = Application.GetOpenFilename(FileFilter:="Excel Files
(*.xls),*.xls", _
Title:="Select Document
Version Control Log")

This only gets the file name without actually opening the file. Just
remember that the value of strFileName will be "False" if the user
cancels out of the dialog box.