How to change data in a formula
 

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

How to change data in a formula
 

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
" wrote:

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

How to change data in a formula
 

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"")"

wrote:
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

How to change data in a formula
 

ACK! Peter beat me to it. :)

-Jeff-
JW wrote:
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"")"

wrote:
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

How to change data in a formula
 

Thanks a ton!

How to change data in a formula
 

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

"JW" wrote:

ACK! Peter beat me to it. :)

-Jeff-
JW wrote:
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"")"

wrote:
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

How to change data in a formula
 

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.
--
Regards,
Bill Renaud