Macro to change cell references

SaneMan · #5 09-10-2013, 01:36 AM

Thanks again Bob. You're very good at explaining this to someone as inexperienced as myself!

Unfortunately the cell references are completely random. This spreadsheet was not set up by me and the cell references are structured so that the data reads in a specific way to make the data clearer. This means it is completely re-ordered from the original data which is why I went ahead with VBA.

Your code definitely seems to be almost there. This is what it looks like now:

Quote:

Sub newmacro4()
Workbooks.Open ("H:\Desktop\Z1 assessment for CP data_v3.xlsx")
Range("A1:BF85").Select
For Each co In Selection
vf = co.Formula
co.Formula = Left(vf, pr - 1) & Mid(vf, pr) + 2
Next co
ThisFile = Range("D1").Value
ActiveWorkbook.SaveAs Filename:="1 " + ThisFile
ActiveWorkbook.Close
End Sub

It just seems to be the following line which doesn't work:

Quote:

'co.Formula = Left(vf, pr - 1) & Mid(vf, pr) + 2

I tried changing this line with a simple code to replace each cell with 'blah' and the macro worked fine, so it's something in this line which VBA doesn't seem to like. A dialogue comes up when I run it saying "invalid procedure cell or argument". There is no option to debug, only 'ok' or 'help' which doesn't reveal anything helpful, at least from what I can understand.

You are correct that it's the cell letter that I wish to change and not also the number. Any ideas?

I can't stress how grateful I am for you taking the time to answer my queries! Life saver.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating a Macro to change the shading of a cell	Triscia	Word VBA	3	01-30-2013 04:18 PM
Change formula cell range based on cell value	Scoth	Excel	4	10-25-2012 07:51 AM
Formula that references cell with text in different workbook	no1texan	Excel	3	07-17-2012 05:58 AM
Detect a cell has un-approved change or not?	LongTTH	Excel Programming	1	05-20-2012 02:22 AM
how to change number in cell	ubns	Excel	5	05-14-2012 04:21 PM