from excel formula to vba ...

[Nekkidbuns]

Hey guys,

I recorded a simple macro that inserted a column at A:A, took whatever was in column X and placed " E" after it. Formula looked like this A2=X2&" E"

Because at some point the data in X might move columns, I wanted to make it a bit more dynamic by replacing column "X" with the column title header "MONTHS".

I got to the point where I intify the cell containing "MONTHS" but how do I continue? also, the formula has to be expanded as far as there is data in column B.

This is probably simple, but I'm really lost @_@
Thanks in advance!

[Bob Phillips]

Sub AddFormula()

With ActiveSheet

.Columns(1).Insert
.Range("A2:A100").Formula = "=INDEX(B2:IV2,MATCH(""MONTHS"",$1:$1,0)-1)&""E"""
End With
End Sub

[Nekkidbuns]

Ohw ... thats a lot less lines than I had :P
Thanks!

[Nekkidbuns]

Your piece of code was very helpful and I made some changes to make it even more dynamic

"=INDIRECT(ADDRESS(ROW(R[0]C[1]),(MATCH(""MONTHS"",R1,0))))&"" E"""

I had no idea I could use excel formulas in VBA this way, so inspired by what you showed me I used the record macro option to convert almost all of my formulas.

My last formula, however is a bit complex (well ... not so much complex as just having too many characters) and cannot be recorded.

=(IF((IF((IF((IF(V2="TBA";S2;V2))="ANY BANK";Q2;(IF((IF(V2="TBA";S2;V2))="TO THE ORDER OF ANY BANK";Q2;(IF(V2="TBA";S2;V2))))))="TBA";T2;(IF((IF(V2="TBA";S2;V2))="ANY BANK";Q2;(IF((IF(V2="TBA";S2;V2))="TO THE ORDER OF ANY BANK";Q2;(IF(V2="TBA";S2;V2))))))))="TBA";P2;(IF((IF((IF(V2="TBA";S2;V2))="ANY BANK";Q2;(IF((IF(V2="TBA";S2;V2))="TO THE ORDER OF ANY BANK";Q2;(IF(V2="TBA";S2;V2))))))="TBA";T2;(IF((IF(V2="TBA";S2;V2))="ANY BANK";Q2;(IF((IF(V2="TBA";S2;V2))="TO THE ORDER OF ANY BANK";Q2;(IF(V2="TBA";S2;V2))))))))))

The idea with above formula is that the columns P, Q, S, T and V contain names. Sometimes names are not added and the user will put in "N/A" or "TBA" or whatever. There is a certain hierarchy in columns:

1 V
2 S
3 Q
4 P
5 T

I cut it up a bit to figure out how to convert the formula. This is what the first part looks like in VBA:

"=(IF(R[-1]C[21]=""TBA"",R[-1]C[18],(IF(R[-1]C[21]=""N/A"",R[-1]C[18],R[-1]C[21]))))"

The thing I got stuck on again was that the columns can move around. A reference has to be made to the headers in row 1. The whole R[]C[] thing is throwing me off and I cant seem to figure out how to use the MATCH function here ...

I've been at it for a while now, but I'm stuck. Any ideas on how to tackle this ... challenge?

[mdmackillop]

You could retrieve the column numbers with a simple Find and use that in your formula.


Sub Banks()
Dim MM As Long, TT As Long
MM = Rows(1).Find("Months").Column
TT = Rows(1).Find("TBA").Column

End Sub

[Nekkidbuns]

Thanks for your thoughts!
But what would the syntax be? I cannot use the R[] here right?
I can also not just 'step out' the excel formula and add MM somewhere, can I?

I guess I need to do this per row?
something like


Dim iLastRow As Integer
Dim iRow As Integer

iLastRow = Range("E65536:AB65536").End(xlUp).Row

For iRow = 2 To iLastRow

'STUFF IN BETWEEN

Next iRow

[Nekkidbuns]

Ahh I got it,
I'll post it when I'm done.
Thanks!