Help with updating formula's with VBA

[greyangel]

Good morning VBA users,

Attached is a spreadsheet with formulas that refer to a separate workbook. Every month we create a new workbook and need to update the formulas to point to the new workbook. Is there an easy vba code that just replaces the file path in the formula to new file path. The columns highlighted in yellow are the ones that need to be changed and the cell highlighted in orange is the new file path.

Thank you.

[offthelip]

try somethign like this:


Sub changeform()
Dim newstr As String
Dim curt As String
oldstr = "J:\GaShare\LIFO\2017\2017 Q3\[FY2017 Q3 Index Calculation.xlsx]Bearings adj 8.2"
newstr = Cells(1, 1)


For i = 3 To 11 Step 2
inarr = Range(Cells(10, i), Cells(30, i)).Formula
For j = 1 To 21
curt = inarr(j, 1)
inarr(j, 1) = Replace(curt, oldstr, newstr, 1)
Next j
Range(Cells(10, i), Cells(30, i)).Formula = inarr

Next i

[greyangel]

try somethign like this:


Sub changeform()
Dim newstr As String
Dim curt As String
oldstr = "J:\GaShare\LIFO\2017\2017 Q3\[FY2017 Q3 Index Calculation.xlsx]Bearings adj 8.2"
newstr = Cells(1, 1)


For i = 3 To 11 Step 2
inarr = Range(Cells(10, i), Cells(30, i)).Formula
For j = 1 To 21
curt = inarr(j, 1)
inarr(j, 1) = Replace(curt, oldstr, newstr, 1)
Next j
Range(Cells(10, i), Cells(30, i)).Formula = inarr

Next i



That may work, however the variable "oldstr" will always be changing and will not stay "J:\GaShare\LIFO\2017\2017 Q3\[FY2017 Q3 Index Calculation.xlsx]Bearings adj 8.2." I mean they can go in there and change it every month but since non-technical people will be using this code as well I do not want them making changes to the code.

[offthelip]

what about saving the old string in cells A2, this can be done automatically when you run the code by saving "newstr" in cell A2. then

newstr=cells(1,1)
oldstr=Cells(2,1)
cells(2,1)=newstr

will do the trick

[greyangel]

Yes this will work great for right now, however is there some type of code that replaces everything between the ''? Thank you for all your help offthelip.

[offthelip]

this shoud do the tirck more or less;


Sub changeform()
Dim newstr As String
Dim curt As String
newstr = Cells(1, 1)


For i = 3 To 11 Step 2
inarr = Range(Cells(10, i), Cells(30, i)).Formula
For j = 1 To 21
curt = inarr(j, 1)
strt = InStr(2, curt, "=")
enst = InStr(curt, "!")
textstr = Left(curt, strt + 1) & newstr & Right(curt, Len(curt) - enst + 3)
inarr(j, 1) = "'" & textstr
Next j
Range(Cells(10, i), Cells(30, i)) = inarr

Next i

End Sub

[greyangel]

this shoud do the tirck more or less;


Sub changeform()
Dim newstr As String
Dim curt As String
newstr = Cells(1, 1)


For i = 3 To 11 Step 2
inarr = Range(Cells(10, i), Cells(30, i)).Formula
For j = 1 To 21
curt = inarr(j, 1)
strt = InStr(2, curt, "=")
enst = InStr(curt, "!")
textstr = Left(curt, strt + 1) & newstr & Right(curt, Len(curt) - enst + 3)
inarr(j, 1) = "'" & textstr
Next j
Range(Cells(10, i), Cells(30, i)) = inarr

Next i

End Sub




That new code turns the formula into this: '=B10='J:\GaShare\LIFO\2017\2017 Q4\FY2017 Q4 Index Calculation Final.xlsx2'!$H57 I feel like we are close though :).

[offthelip]

sorry I left my debugging character in there:

change


inarr(j, 1) = "'" & textstr

to


inarr(j, 1) = textstr

[YasserKhalil]

Hello greyangel
As for this string in A1 : "J:\GaShare\LIFO\2017\2017 Q4\FY2017 Q4 Index Calculation Final.xlsx" ...
Will it include the sheet name or not ? Or the sheet name has no change ..
You have to give example of existing formula and the string in A1 and the final output so as to be more specific ..

[YasserKhalil]

If the sheet name is fixed so you have to change this line in offthelip's code

textstr = Left(curt, strt + 1) & newstr & Right(curt, Len(curt) - enst + 3)
to be

textstr = Left(curt, strt + 1) & newstr & "]" & Split(curt, "]")(1)

[YasserKhalil]

I have adopted offthelip's code .. Try this modification


Sub ChangeFormulas()
Dim arr As Variant
Dim newstr As String
Dim curt As String
Dim textstr As String
Dim strt As Integer
Dim i As Integer
Dim j As Integer


Const fRow As Integer = 10
Const lRow As Integer = 30
Const fCol As Integer = 3
Const lCol As Integer = 11


newstr = Cells(1, 1).Value
strt = InStrRev(newstr, "\")
newstr = Left(newstr, strt) & "[" & Mid(newstr, strt + 1)


For i = fCol To lCol Step 2
arr = Range(Cells(fRow, i), Cells(lRow, i)).Formula
For j = LBound(arr, 1) To UBound(arr, 1)
curt = arr(j, 1)
strt = InStr(2, curt, "=")
textstr = Left(curt, strt + 1) & newstr & "]" & Split(curt, "]")(1)
arr(j, 1) = textstr
Next j
Range(Cells(fRow, i), Cells(lRow, i)) = arr
Next i
End Sub

[greyangel]

Hello greyangel
As for this string in A1 : "J:\GaShare\LIFO\2017\2017 Q4\FY2017 Q4 Index Calculation Final.xlsx" ...
Will it include the sheet name or not ? Or the sheet name has no change ..
You have to give example of existing formula and the string in A1 and the final output so as to be more specific ..

No it will never include the sheet name and the sheet name will never change. This code is apart of a much bigger file that actually assigns the file path in A1 to a variable.

[greyangel]

I have adopted offthelip's code .. Try this modification

This code will work great I will make some modifications to it and then repost to show my modifications. Thank you all.

[YasserKhalil]

Hello
Have you checked the code??!!

Just put the new path in A1 and run the code. Let me know about the results if possible

[greyangel]

Hello
Have you checked the code??!!

Just put the new path in A1 and run the code. Let me know about the results if possible

Yes the code does work. Below is your code along with some modification I made to fit my purposes. There are tables above the data that have different amounts of rows, so I made the row numbers variable



Sub ChangeFormulas()
fnd = "Matched back to Cost Index - Adj by pool "
frstcellvalue = Cells.Find(fnd, LookIn:=xlValues).Offset(1).Address
fRow = Cells.Find(fnd, LookIn:=xlValues).Offset(1).Row
lRow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Row
MsgBox lRow
Dim arr As Variant
Dim newstr As String
Dim curt As String
Dim textstr As String
Dim strt As Integer
Dim i As Integer
Dim j As Integer



Const fCol As Integer = 3
Const lCol As Integer = 11


newstr = Cells(1, 1).Value
strt = InStrRev(newstr, "\")
newstr = Left(newstr, strt) & "[" & Mid(newstr, strt + 1)


For i = fCol To lCol Step 2
arr = Range(Cells(fRow, i), Cells(lRow, i)).Formula
For j = LBound(arr, 1) To UBound(arr, 1)
curt = arr(j, 1)
strt = InStr(2, curt, "=")
textstr = Left(curt, strt + 1) & newstr & "]" & Split(curt, "]")(1)
arr(j, 1) = textstr
Next j
Range(Cells(fRow, i), Cells(lRow, i)) = arr
Next i
End Sub



Thank you for all of your help.

[YasserKhalil]

You're welcome. Glad I can offer some help
Please mark the thread as solved

[greyangel]

Would somebody be able to look at the following workbook and code and tell me where I am going wrong? On the line "textstr = Left(curt, strt +1) & newstr "]" & split(curt, "]")(1)" I keep getting a "Subscript out of range" error." This code worked great in the workbook that I originally posted to this forum post, however when I modified it for this other workbook it errored out on me.

Thank you for all your help.


Sub testing()

'updates formulas in column O for the subs
fluidpowers = "C:\Users\Jdoe\Documents\Working on macro\FP Summary-Checklist Q3 FY2017TEST1.xlsm"
Find = "Index"
fRow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Row
lrow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Offset(1).End(xlDown).Row
Dim arr As Variant
Dim newstr As String
Dim curt As String
Dim textstr As String
Dim strt As Integer
Dim i As Integer
Dim j As Integer



Const fCol As Integer = 15
Const lCol As Integer = 15


newstr = fluidpowers
strt = InStrRev(newstr, "\")
newstr = Left(newstr, strt) & "[" & Mid(newstr, strt + 1)
MsgBox newstr

For i = fCol To lCol Step 2
arr = Range(Cells(fRow, i), Cells(lrow, i)).Formula
For j = LBound(arr, 1) To UBound(arr, 1)
curt = arr(j, 1)
strt = InStr(2, curt, "=")
textstr = Left(curt, strt + 1) & newstr & "]" & Split(curt, "]")(1)
arr(j, 1) = textstr
Next j
Range(Cells(fRow, i), Cells(lrow, i)) = arr
Next i
End Sub

[YasserKhalil]

Hello
In your file the variable fRow=5 and lRow=49 and I think you need to deal with fRow=52 and lRow would 72
Why don't you keep on them as constants .. to avoid that ?

[greyangel]

Hello
In your file the variable fRow=5 and lRow=49 and I think you need to deal with fRow=52 and lRow would 72
Why don't you keep on them as constants .. to avoid that ?

I don't keep the rows as constants because rows are always being added and deleted to this spreadsheet.

[greyangel]

I looks like you're missing an opening "[" on that line.

If you look in the newstrng variable it adds the "["

[greyangel]

I was hoping the code would find the word "index" do a ctrl down and take the first row number. Then it would do another ctrl down to get the last row number with data.

[offthelip]

Would somebody be able to look at the following workbook and code and tell me where I am going wrong?

I have spotted an error in your code when you copied it across,
you changed "fnd"to "find" in one place:


Find = "Index"
fRow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Row
lrow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Offset(1).End(xlDown).Row


should be;

fnd = "Index"
fRow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Row
lrow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Offset(1).End(xlDown).Row

[greyangel]

should be;

fnd = "Index"
fRow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Row
lrow = Cells.Find(fnd, LookIn:=xlValues).End(xlDown).Offset(1).End(xlDown).Row


Thank you offthelip I cannot believe I didn't see this error. I believe I can official call this forums closed :D. Everything works as it should.