Referencing User Selected Workbooks

alanive · Aug 5th 2008

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.

Code

Sub InsertLocationContents()


Dim rng As Range
Dim LastRow As Long


'OPEN CSV FILE WITH LOCATION CONTENTS
 
csvFN = Application.GetOpenFilename(Title:="Select Location Contents csv file")
If csvFN = False 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


csvSheetName = ActiveSheet.Name
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("R1C1:R18C3")




dbfFN = Application.GetOpenFilename(Title:="Select shapes dbf file")
If dbfFN = False 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


With dbfFN
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],rng,3,FALSE)),0,(VLOOKUP(RC[-15],rng,3,FALSE)))"
    Selection.AutoFill Destination:=Range("R2:R" & LastRow), Type:=xlFillDefault


End With
End With


End Sub

Display More

[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I forgot to mention that the error occurs on the 'set rng' line.

Thanks for any possible help.

IanDunnett · Aug 5th 2008

Re: Referencing User Selected Workbooks

I think by using the reference method you are it's best to use normal referencing rather than the R1C1 variety.

Try changing the line where the error occurs to;

Code

Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")

Hope that helps,
Ian

alanive · Aug 5th 2008

Re: Referencing User Selected Workbooks

Thank you for your swift response. Unfortunately that still returns the same error. - run-time error 9. Subscript out of range.

IanDunnett · Aug 5th 2008

Re: Referencing User Selected Workbooks

It might be that although you've opened the workbook it is not yet active so you're previous line is not selecting a sheet wihtin the workbook you've just opened.

Try either inserting a line to activate the workbook first;

Code

Workbooks(csvFN).Activate
csvSheetName = ActiveSheet.Name 
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")

or accessing the active sheet in the workbook you've just opened;

Code

csvSheetName = Workbooks(csvFN).ActiveSheet.Name 
Set rng = Workbooks(csvFN).Sheets(csvSheetName).Range("A1:C18")

Cheers,
Ian

alanive · Aug 5th 2008

Re: Referencing User Selected Workbooks

I am still getting the same error with both of your suggestions.

Cheers

Alan.

colin_l · Aug 5th 2008

Re: Referencing User Selected Workbooks

Hi,

The first step to fixing this is to declare all of your variables!

Put an option explicit statement at the top of your code module, dimension all of your variables (and not as variants/objects), and try stepping through your code. You'll get various type mismatch and other errors which you need to fix. Once those are repaired it will be easier to track down any remaining bugs.

HTH
Colin

alanive · Aug 5th 2008

Re: Referencing User Selected Workbooks

I have now dimensioned my variables as Strings. I had an data mismatch error on the

Code

If csvFN = False Then

line. However changing this to

Code

If csvFN = vbNullString Then

sorted it.

However i am still stopped at the same point with the same error. Apologies if my explanation is poor as i'm pretty new to VBA.

Alan.

alanive · Aug 6th 2008

Re: Referencing User Selected Workbooks

I have now changed the set range line to this

Code

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

which results in no error, however when using the variable in my vlookup formula it does not return the actual range, rather the letters aRng

Do i have the correct syntax here?

Code

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

Cheers

Alan.

colin_l · Aug 7th 2008

Re: Referencing User Selected Workbooks

Hi Alan,

To construct your formula, you'll need to concatenate the address of your aRng variable. Here's a simple example so you get the idea:

Code

Sub Example()
    Dim rng As Range
    
    Set rng = Range("A1:C18")
    
    Range("E1").Formula = "=VLOOKUP(D1," & rng.Address & ",3,0)"
End Sub

This example puts this formula in cell E1:
=VLOOKUP(D1,$A$1:$C$18,3,0)

HTH
Colin

alanive · Aug 7th 2008

Re: Referencing User Selected Workbooks

excellent. it now works.

Thank you for all your help.
Alan.

Referencing User Selected Workbooks

Participate now!

Share