VBA changing Rows while keeping the columns constant

Hi,


I have the below, in which lastcolumn is a variable, but once it is set (depending on user specifications) it will remain that. What I want to to is insert the index formula into the selected cell but have the last column remain fixed. Or in other words, if you were to insert it into the worksheet directly the dollar signs would be there indicating a fixed column, while the row moves. So in this example last column is AE9, I would want the AE to stay fixed. I know a for loop (while it doesn't put dollar signs) would work going down, but not sure how to do it across. I haven't be able to find how to do it, but pretty sure it can be done. Any help is greatly appreciated.

Please Login or Register  to view this content.

Thanks,



Tom

I think get rid of the square brackets,

Please Login or Register  to view this content.

Thanks, Steve.

That definitely works. It's just it won't let me do something like -LastColumn, like if I want to go back 75 columns. I tried multiplying by -1 and then inserting LastColumn, still won't work, any idea why?

Thanks,

Tom

Sorry, not with you. It has to be either relative or absolute. What are you trying to do?

Ok sorry about that, I should have explained what I am doing better. What I am doing is referencing two columns (keeping the columns the same) while changing the rows. So my code would look something like this. If I were to just enter it into excel.

Please Login or Register  to view this content.

With AE and AF columns staying constant. It's simple enough it's just that AE and AF can can change it can also span more then two columns as well (but they will always be consecutive columns, so I'm trying to write logic in VBA to take that into account. I will attach my worksheet...sorry I should have done that first. I manually have typed in the code to the cells. I start in DA9 It also has some VBA so you can see what I am attempting.



Thanks,


Tom

Tom - sorry, I am being slow. I still don't get what you are actually trying to achieve. Can you explain in plain English as there may be a better approach?