One way (with some assumptions):
Option Explicit
Sub testme()
Dim myFileNames As Variant
Dim myPath As String
Dim iCtr As Long
Dim wks As Worksheet
Dim nextCol As Long
Dim lastRow As Long
Dim myFormulaR1C1 As String
myPath = "C:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If
myFileNames = Array("book1.xls", "book2.xls", "book3.xls", _
"book4.xls", "book5.xls")
myFormulaR1C1 =
"VLOOKUP(RC1,'C:\[id.xls]Sheet1'!C1:C2,2,FALSE)"
For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set wks = Nothing
On Error Resume Next
Set wks = Workbooks.Open _
(Filename:=myPath & myFileNames(iCtr)).Worksheets(1)
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Problem opening: " & myFileNames(iCtr)
Else
With wks
nextCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Cells(1, nextCol).Value = "ID"
.Range(.Cells(2, nextCol), _
.Cells(lastRow, nextCol)).FormulaR1C1 _
= "=IF(ISNA(" & myFormulaR1C1 & "),""""," _
& myFormulaR1C1 & ")"
.Parent.Save
.Parent.Close savechanges:=False
End With
End If
Next iCtr
End Sub
Adjust the myFilenames to be the 5 that you need.
I assumed that I could determine the last row by looking at column A. And I
assumed that this formula would go to the right of the last used column. And
that I could find that last used column by looking at row 1.
And I assumed that the formula started in row 2 (you started with A4???).
If you want to start with row 4, change this:
.Range(.Cells(2, nextCol), _
to
.Range(.Cells(4, nextCol), _
And that the formula gets added to the first worksheet in the workbook.
Hello All
I have this formula
=IF(ISNA(VLOOKUP(A4,'C:\[Id.xls]Sheet1'!$A:$B,2,2)),""
"",VLOOKUP(A4,'C:\[Id.xls]Sheet1'!$A:$B,2,2))
I have 5 different files in a folder, i need to open each file and add
a column called ID and add the above formula till the end of file and
then save the file.
The number of rows in each file is different.
Thank you for any help.
