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.