Forum Discussion

PMHunt1955's avatar
PMHunt1955
Brass Contributor
Apr 03, 2019
Solved

Correct use of Worksheets statement in VBA

I have a function where I am seeking to look across from  a worksheet in one workbook to a worksheet in another workbook.   I have created a name reference across to the other sheet, the name is Su...
excel
Macros and VBA
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Apr 04, 2019

    PMHunt1955 

    I wonder whether opening the target workbook and then working step by step might provide a starting point.

     

    Set wb = Workbooks("Genealogical_Service_Users_Tracing_Project_Families.xlsm")
    Set ws = wb.Worksheets("Names in Alpha Order")
    Test_Surname = ws.Cells(3, 4).Value

     

    Something radically different might be to ditch the usual sheet name and cell address approach and go for a Table using Power Query?

JKPieterse's avatar
JKPieterse
Silver Contributor
Apr 04, 2019

Hi,
Your question is quite difficult to read through and hence to answer. Can you try to reduce the question to a slightly simpler problem please? I am confident what you are trying to achieve isn;t that hard in VBA, but the complexity of the question makes it very hard to understand the setup.

PMHunt1955's avatar
PMHunt1955
Brass Contributor
Apr 04, 2019

JKPieterse 

Dear Jan

Thank you for replying.

I feel that the essence of my problem is stated quite clearly in the first sentence but I will slightly reword it, with the hope that this helps.

I have a function which is called from a worksheet in one workbook, where the function needs to view, in succesion, within a couple of nested for-next loops, the contents of a series of cells (one at a time), which are located in a worksheet in another workbook. Both workbooks are open.

The following statement is where I am getting the subscript out of range error - I have even tried to do the statement in the immediate window (within debug), but using literals, rather than the variable name; I still get the same error.

[CODE]
Set fExt_Workbook = Excel.Workbooks(fExt_Workbook_Name)

[/CODE]

I have tried to follow the guidance that I have read through a number of other threads on a few different Excel support bulletin boards, which have discussed similar situations, but I am just not doing exactly the right thing, obviously.

I apologise for what you feel is the long windedness of my question; I have, though, often found that 'helpers' ask to see the full code, so that is why it is there.

As you say I am also fairly confident that what I am trying to achieve is not that difficult in Excel, but I have just got the syntax wrong somewhere.

Best regards

Philip

P. S. I used to be a mainframe / mini programmer in PL/1, COBOL and dBase, so that is why I often try to do more tricky things, eh.

Resources