Replies

Wouldn’t Dir be simpler as it accepts wildcards already (unless you’re on a Mac)?

You didn’t mention you were using code to save the text file for some reason. For that you need to use the cell’s Text property rather than Value.

You cannot customise the built-in form in any way, I’m afraid.

In that case, sorting the table on currency and then date in ascending order, the formula is:

=IF(A2=”GBP”,1,LOOKUP(1,1/(RATE_CURR=A2)/(EXC_DATE<=B2),EXCHANGE_RATES))

Note that you don't have any 1/1/2016 data which is why you will get errors for any non-GBP currency for the first few rows.

Is it OK to sort the exchange rate table by currency code and date? If so, the formula is pretty simple.

As far as I can see, the logic boils down to:
If there’s 100, return 100; if there’s 120, return 120; otherwise return 85. In which case, E2 could contain:
=IF(ISNUMBER(FIND(100,D3)),100,IF(ISNUMBER(FIND(120,D3)),120,85))
and copy down.
Or in E2:
=IFERROR(LOOKUP(1E+100,FIND({100,120},D2),{100,120}),85)

Right-click the file, choose properties, then you should see an unblock option if the file has been blocked by the security update.

There is a recent security update to Office that can cause this. You need to locate the add-in file in explorer, right-click it and then unblock it.

It’s always worth looking in the Language spec document. It takes a little while to get used to some of its syntax, but it’s a mine of useful information!

Per the VBA Language specification for Array Dimension and Bounds (sect 5.2.3.1.3):

array-dim = “(“ [bounds-list] “)”
bounds-list = dim-spec *(“,” dim-spec)
dim-spec = [lower-bound] upper-bound
lower-bound = constant-expression “to”
upper-bound = constant-expression

Static Semantics

[*]An <array-dim that does not have a <bounds-list designates a resizable array.
[*]A may contain at most 60 elements.
[*]An with a designates a fixed-size array with a number of dimensions equal to the number of elements in the .
[*]The in an or must evaluate to a data value that is let-coercible to the declared type Long.
[*]The upper bound of a dimension is specified by the Long data value of the of the that corresponds to the dimension.
[*]If the is present, its provides the lower bound Long data value for the corresponding dimension.
[*]If the is not present the lower bound for the corresponding dimension is the default lower bound for the containing module as specified in Section 5.2.1.2.

For each dimension, the lower bound value must be less than or equal to the upper bound value.

Note that the lower bound is optional, the upper is not.

Hence, any array declaration that only provides one number for a dimension, is providing the upper boundary, as Maud said, not the number of elements, which will be dependent on the Option Base.

Thank you. 🙂

It would help if you posted your code. There is no Excel setting for ‘create workbooks if they don’t exist’ and Workbooks.Open has always failed if you pass a non-existent file path, so I suspect your issue is with an Access command like DoCmd.Transferspreadsheet.

As for your question, it should count 1 in B and 0 in C.

In that case I think you need:

Code:

=SUMPRODUCT((C3:C7"")*(SUBTOTAL(3,OFFSET($B$3,ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7)),0,1,COLUMNS($B2:C2)))=1))

I don’t think any of the prior formulas will calculate like that.

To do it without a helper column:

=SUMPRODUCT((C3:C7″”)*(SUBTOTAL(3,OFFSET($B$3:$F$3,ROW($B$3:$B$7)-MIN(ROW($B$3:$B$7)),0))=1))

One question though: if there is no value in col A, but a value in col B and col D, should it count as 1 in col B and 0 in col D, or 0 in both?

You don’t need any. Simply adding the command bar controls should make the addins tab visible.