Need Help Converting Formula to VBA Code

[Robotrixx]

Hi All,

I have been using these sub-array formulas quite frequently and have no issues with them in day to day usage. I am however trying to write a macro that will automatically create a new section in my excel file, and all formulas will automatically be input into their respective cells. Below is an example of the formula I'm currently struggling to get VBA to automatically input. I have highlighted the two functions that I am incapable of capturing in my VBA code, my gut tells me it's probably some sort of loop...

Ultimately, if my new section possesses 5 rows, the two highlighted sections should grow in order of magnitude of 1. i.e:

Row 1: Functions should = Rows($1:1) & Row(1:1) respectively
Row 2: Functions should = Rows($1:2) & Row(2:2) respectively
Row 3: Functions should = Rows($1:3) & Row(3:3) respectively

etc...

Please Login or Register  to view this content.

If something is unclear or you need additional information please let me know. Thank you in advance for all the help!

Thank you,

Konrad

[PaulSP8]

You could do with uploading a Sample workbook (Go Advanced > Manage Attachments) with some sample data and the Code you have already.

It sounds like might be able to use Autofill to avoid a loop, you do that with something like;

Please Login or Register  to view this content.

If you've got the last row of the range stored in a variable then change to;

Please Login or Register  to view this content.

Other than that it's hard to advise without seeing what you've got already.

If that doesn't help then upload a Workbook and I dare say someone will be able to get this sorted for you

[Robotrixx]

Hey!

Thanks for the idea, I actually managed to make it work with an iteration of your suggestion above, very much appreciated! All formulas are now scrolling through the appropriate "Row" values