Referencing User Selected Workbooks

[alanive]

Hi,

Having a problem referencing a user selected worksheet in my macro. I would like the user to select two files, the macro then inputs a formula that includes vlookup to the other sheet. However i am not sure how to reference each workbook.

Here is the code.

 
Option Explicit
Sub InsertLocationContents()
Dim aRng As Range
Dim LastRow As Long
Dim dbfFN As String
Dim csvFN As String
'OPEN CSV FILE WITH LOCATION CONTENTS
 
csvFN = Application.GetOpenFilename(Title:="Select Location Contents csv file")
If csvFN = vbNullString Then
'They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=csvFN
    Workbooks.OpenText Filename:= _
        csvFN, Origin:=437 _
        , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
        6, 1), Array(35, 1), Array(44, 1), Array(53, 1), Array(62, 1)), TrailingMinusNumbers:= _
        True
End If
 
Set aRng = ActiveSheet.Range("A1:C18")
 
 
dbfFN = Application.GetOpenFilename(Title:="Select shapes dbf file")
If dbfFN = "" Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=dbfFN
    Workbooks.OpenText Filename:= _
        dbfFN, Origin:=437 _
        , StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _
        6, 1), Array(35, 1), Array(44, 1), Array(53, 1), Array(62, 1)), TrailingMinusNumbers:= _
        True
End If
 
'Workbooks(dbfFN).Activate
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row ' finds the very last cell row used in column e
 
    Range("R" & LastRow).Select
    ActiveCell.FormulaR1C1 = _
        "=IF(TRUE=ISERROR(VLOOKUP(RC[-15],aRng,3,FALSE)),0,(VLOOKUP(RC[-15],aRng,3,FALSE)))"
    Selection.AutoFill Destination:=Range("R2:R" & LastRow), Type:=xlFillDefault
End With
End Sub

The code runs through ok but just places aRng in the formula box rather than the actual physical range.

Any help appreciated

Alan.

[Bob Phillips]

When you open each workbook assign a workbokk variable,

Set wb1 = Workbooks.Open(...

and then reference through these variables.

[alanive]

i have assigned each workbook a workbook variable however how do i then reference these variables in either this line

 
Set aRng = wb1.ActiveSheet.Range("A1:C18")

Or this line

 
ActiveCell.FormulaR1C1 = _
        "=IF(TRUE=ISERROR(VLOOKUP(RC[-15],aRng,3,FALSE)),0,(VLOOKUP(RC[-15],aRng,3,FALSE)))"

As i still am only getting in to put the word aRng in the formula rather than the actual range.

[Bob Phillips]

ActiveCell.FormulaR1C1 = _
     "=IF(ISNA(VLOOKUP(RC[-15]," & aRng.Address(, , xlR1C1) & _
     ",3,FALSE)),0,VLOOKUP(RC[-15]," & aRng.Address(, , xlR1C1) & ",3,FALSE))"

[alanive]

I have just tried this and it returns the range on the same sheet rather than the range on the other file that i need.

[Bob Phillips]

Try

ActiveCell.FormulaR1C1 = _ 
"=IF(ISNA(VLOOKUP(RC[-15]," & aRng.Address(, , xlR1C1,True) & _ 
",3,FALSE)),0,VLOOKUP(RC[-15]," & aRng.Address(, , xlR1C1,True) & ",3,FALSE))"

[alanive]

sorted! Thanks very much.

Alan.