11

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

 3 years ago
source link: https://www.codesd.com/item/adding-new-rows-in-excel-without-breaking-a-vba-macro-that-uses-range-value.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

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

advertisements

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?

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)

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK