PDA

View Full Version : [SOLVED] Referencing User Selected Workbooks



alanive
08-06-2008, 07:33 AM
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
08-06-2008, 08:21 AM
When you open each workbook assign a workbokk variable,


Set wb1 = Workbooks.Open(...


and then reference through these variables.

alanive
08-07-2008, 12:31 AM
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
08-07-2008, 12:51 AM
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-15]," & aRng.Address(, , xlR1C1) & _
",3,FALSE)),0,VLOOKUP(RC[-15]," & aRng.Address(, , xlR1C1) & ",3,FALSE))"

alanive
08-07-2008, 02:29 AM
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
08-07-2008, 03:29 AM
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
08-07-2008, 03:37 AM
sorted! Thanks very much.

Alan.