Incrementing a variable containing a cell reference in excel formula using VBA

0
votes

I am building a macro to allow me to reproduce a formula across or down a spreadsheet with different cell references moving in different directions (eg first cell reference is static (as if fixed with $), second moves one to the right with every column (eg Excel standard), third moves down with every column, fourth moves 3 to the right, etc). These are very big, very complicated formula, so this should save time/user error manually editing the formula for everything that isn't covered by use of $ and dragging the formula across cells.

I have the macro extracting all the cell references, options to select which direction and how many cells the cell reference needs to move with each cell to the right/down it is copied, and then I plan to rebuild the formula with the updated cell references and paste it into the appropriate cells.

The problem I am struggling with, is how to increment the cell references without disassembling them, updating the Alpha or the numeric (if update is across or down), and then reassembling them to put them back in the formula.

Some cell references will have $, some won't, some will be ranges. Is there a VBA standard way of taking a variable containing a cell reference and incrementing row/incrementing column?

If I need to, I can disassemble the references to achieve the required outcome, I just wondered if there was a more straightforward method.

1
excelvba
Use Replace to replace a certain character that you mimic the row/column reference with. Maybe a question mark? – JvdV
An interesting suggestion, I can see this working to help me update the reference if there isn't a more straightforward function to do it, thank you. – user1606927
Take a look at FormulaR1C1, as in Activecell.FormulaR1C1 – Doug Glancy
Initial research into this looks very useful for my problem. It's something I've seen in passing before but never really understood, looks like I'm about to become a lot more familiar! Thank you. – user1606927
You're welcome. Good luck! For future reference, you can direct a comment like @dglancy, then the person will be notified. – Doug Glancy

1 Answers

0
votes

You can use for loop with increasing counter like For last row in column A With activesheet Last row = .cells (rows.count,”A”).end(clip).row End with For I =1 to last row Vartest = activesheet.range(“A” & I).value Next I

Let me know if it helps .else try paste some code about what you are doing.will try to help