adding new rows in excel without breaking a vba macro that uses Range.Value

Question

I've written a macro in VBA that simply fills in a given cell's value from another cell in that sheet. I do this for lots of cells in the sheet, and I'm doing it like so:

Range("B3").Value = Range("B200")
Range("B4").Value = Range("B201")
'etc.

Now, I am often adding values by inserting new rows, so I might insert a new row between B200 and B201, which will break the macro because it doesn't autoupdate when I insert the new row.

How can I code the macro so it autoupdates the cell references when I insert new rows or columns?

Solution 1

My suggestion would be to make sure the ROW you want to retrieve values from has a unique value in it that you can .FIND anytime you want, then grab your values from column B of that found cell's row. So right now you want to get a value in B200 and A200 always has the text in it: "Final Total" and that is unique.

Dim MyRNG As Range

Set MyRNG = Range("A:A").Find("Final Total", LookIn:=xlValues, LookAt:=xlWhole)
Range("B3").Value = Range("B" & MyRNG.Row)
Range("B4").Value = Range("B" & MyRNG.Row + 1)

Solution 2

Try giving a name to the range. If you refer to the range by name Excel searches for it and retrieves the rows that defines it. Range names update their definition when new rows are added.

Adding to the above, i think this tutorial illustrates my point: http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html this is how to define the name of the range.

This tutorial explains how to use it on macros and vba: http://excel.tips.net/T003106_Using_Named_Ranges_in_a_Macro.html

I hope this helps :D

Solution 3

This is not an answer but an alternative.

Naming your range is the way to go as Shiin suggested but then if you have 500 cells then like I mentioned earlier, naming 500 cells and using them in your code can be very painful. The alternative is to use smart code. Let's take an example

Let's say you have a code like this

Sub Sample()
    Range("B3").Value = Range("B200")
    Range("B4").Value = Range("B201")
    Range("B5").Value = Range("B201")
    ' And
    ' So On
    ' till
    Range("B500").Value = Range("B697")
End Sub

The best way to write this code is like this

Sub Sample()
    Dim i As Long

    For i = 200 To 697
        Range("B" & i - 197).Value = Range("B" & i)
    Next i
End Sub

and say if you insert a line at say row 300 then simply break the above code in two parts

Sub Sample()
    Dim i As Long

    For i = 200 To 299
        Range("B" & i - 197).Value = Range("B" & i)
    Next i

    For i = 301 To 698
        Range("B" & i - 197).Value = Range("B" & i)
    Next i
End Sub

So every time you insert a row, simply break the for loop into an extra part. This looks tedious but is much better than naming 500 cells and using them in your code.

If you are planning to use the macro only once (i.e for 1 time use) then read ahead.

If you are worried that when the user inserts the row then the cells are not updated then you can instead of assigning a value, assign a formula.

For example

Range("B3").Formula = "=B200"

This will put a formula =B200 in cell B3. So next time when you insert a row so that the 200th row moves it's position, you will notice that the formula automatically gets updated in cell B3

HTH