simonphillips
New Member
- Joined
- Mar 25, 2008
- Messages
- 32
- Office Version
- 365
- Platform
- Windows
Hi Everyone,
Thanks in advance for the support solving my error below and this great service - Love Mrexcel
I have a UDF being called from an .xlsm file. It is defined as follows:
Public Function LookupExternalData( _
ByVal FilePath As String, _
ByVal ProjectArray As Range, _
ByVal ColumnArray As Range) _
As Variant
The first time I call the function it works and returns values in the .xlsm (array of 4 by 49 values). The function call is as follows:
=LookupExternalData(CQ13,$A$17:$A$66, CQ15:CT15)
where
CQ13 = C:\Users\E100465\OneDrive - RMIT University\PIR\Projects due to close\Projects due to close in 2021 - 121Apr2021.xlsx
$A$17:$A$66 = range of values shown at the end of post
CQ15:CT15 = {52,67,4,8}
The problem comes when I attempt the same function call with different parameters, it returns "#SPHILL!" (My name in Simon Phillips).
The second function call is as follows:
=LookupExternalData(CM13,$A$17:$A$66, CM15:CP15)
where
CM13 = C:\Users\E100465\OneDrive - RMIT University\PIR\Projects due to close\Projects due to close in 2021 - 25Mar2021.xlsx
$A$17:$A$66 = range of values shown at the end of post
CM15:CP15 = {52,67,4,8}
I have stepped through the debugger in VBA and there are no errors thrown. Prior to function exit I can see that the LookupExternalData array is correctly filled out in the watches
I cannot understand what is different in the 2 files being referenced (CQ13 versus CM13) and why the UDF fails in the second case.
=============VALUES in cells $A$17:$A$66 =========================================
Thanks in advance for the support solving my error below and this great service - Love Mrexcel
I have a UDF being called from an .xlsm file. It is defined as follows:
Public Function LookupExternalData( _
ByVal FilePath As String, _
ByVal ProjectArray As Range, _
ByVal ColumnArray As Range) _
As Variant
The first time I call the function it works and returns values in the .xlsm (array of 4 by 49 values). The function call is as follows:
=LookupExternalData(CQ13,$A$17:$A$66, CQ15:CT15)
where
CQ13 = C:\Users\E100465\OneDrive - RMIT University\PIR\Projects due to close\Projects due to close in 2021 - 121Apr2021.xlsx
$A$17:$A$66 = range of values shown at the end of post
CQ15:CT15 = {52,67,4,8}
The problem comes when I attempt the same function call with different parameters, it returns "#SPHILL!" (My name in Simon Phillips).
The second function call is as follows:
=LookupExternalData(CM13,$A$17:$A$66, CM15:CP15)
where
CM13 = C:\Users\E100465\OneDrive - RMIT University\PIR\Projects due to close\Projects due to close in 2021 - 25Mar2021.xlsx
$A$17:$A$66 = range of values shown at the end of post
CM15:CP15 = {52,67,4,8}
I have stepped through the debugger in VBA and there are no errors thrown. Prior to function exit I can see that the LookupExternalData array is correctly filled out in the watches
I cannot understand what is different in the 2 files being referenced (CQ13 versus CM13) and why the UDF fails in the second case.
=============VALUES in cells $A$17:$A$66 =========================================
| PSG0675 |
| ITS0513 |
| ITS0397 |
| PSG0638 |
| ITS0492 |
| COL0102 |
| COL0112 |
| PSG0624 |
| PSG0672 |
| PSG0496 |
| SIM206 |
| PSG0651 |
| PSG0673 |
| ITS0529 |
| PSG0655 |
| PSG0666 |
| PSG0653 |
| ITS0522 |
| ITS0524 |
| ITS0552 |
| PSG0664 |
| COL0104 |
| COL0090 |
| ITS0547 |
| ITS0572 |
| ITS0582 |
| PSG0662 |
| PSG0669 |
| PSG0393 |
| COL0123 |
| PSG0630 |
| COL0120 |
| PSG0684 |
| PSG0644 |
| ITS0601 |
| ITS0554 |
| ITS0566 |
| SIM210 |
| ITS0523 |
| SIM208 |
| SIM202 |
| COL0122 |
| ITS0542 |
| ITS0525 |
| PSG0680 |
| COL0127 |
| ITS0543 |
| ITS0575 |
| PSG0681 |
| ITS0540 |
