VBA - Populate columns automatically from complex formulas

Copper Contributor

Hello there,

I'm fighting with the file attached, and I don't manage to find an efficient solution. The file is composed by anonymized account numbers in Column A, other parameters in the following columns and then with:

- CRINT_MAX_RATE_T1 = Max rate, it's like a cap
- CRINT_MIN_RATE_T1 = Min rate, it's like a floor
- CRINT_BSRTVALT1 = Base rate, gross rate
- CRINT_SPRD_T1 = spread to be subtracted from the base rate

FINAL_BANDED_RATE_T1 = the final rate to be computed

As you will see from the file, there are some columns with the header in light blue: I would need to populate them, based on the results of the formulas that I applied. So basically I would need to "translate" the formulas that you will find under those headers in VBA.

My first idea was to do this with different loops, but I'm afraid that my pc doesn't have enough capacity to run them, and so it might block, especially given the amount of rows in the file is actually way higher than the amount you will find in the file attached.

Is there any other efficient way to do this? I was thinking about defining different functions for each column in scope instead of a unique sub, but not sure how to implement that.

Do you guys have any idea? Thanks a lot in advance for your time!

1 Reply

@antotom98 

Shorter formulas:

F2:

=IF(AND(ISNUMBER(SEARCH({"'T1:","'T2:"},D2))),"Tiered","Not tiered")

or

=IF(AND(ISNUMBER(SEARCH("'T"&SEQUENCE(2)&":",D2))),"Tiered","Not tiered")

G2:

=SUM(--ISNUMBER(SEARCH("'T"&{1,2,3,4,5}&":",D2)))

or

=SUM(--ISNUMBER(SEARCH("'T"&SEQUENCE(5)&":",D2)))

The formula in N2 is far too complicated for me to understand. Is there some kind of logic behind it?