Referencing User Selected Workbooks

  • 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.


    [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.

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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.

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!