change formula using macro

[tintin1012000]

='\\IEMAFS001\VOL1\APM\Prodqty\Electronic shift book\8882\Line 1\[wk26.xls]Graphs'!$A$46

Hi All,

I have a formula above and I want to change the week number using a macro, How do I go about this. Ideally i want to type 25 into a cell and then run a macro that will change [wk26.xls] to [wk25.xls]

 

[mikerickson]

Are there always 2 digit week numbers, i.e. is it [wk8.xls] or [wk08.xls]

 

[TiaXL]

='\\IEMAFS001\VOL1\APM\Prodqty\Electronic shift book\8882\Line 1\[wk26.xls]Graphs'!$A$46

Hi All,

I have a formula above and I want to change the week number using a macro, How do I go about this. Ideally i want to type 25 into a cell and then run a macro that will change [wk26.xls] to [wk25.xls]

sub macro()
 
dim sWK as string
 
sWK = SHeets("Sheet1").range("A1").Value
 
range("a2").formula = [URL="file://\\IEMAFS001\VOL1\APM\Prodqty\Electronic shift book\8882\Line 1\"]\\IEMAFS001\VOL1\APM\Prodqty\Electronic shift book\8882\Line 1\[/URL][wk" & sWK & ".xls]Graphs'!$A$46"

then you can use further code to fill other cells with this formula

 

[tintin1012000]

yes always 2 digits

 

[tintin1012000]

Im sorry TiaXL, Im very new to this, can you explain your reply !!

Thanks

 

[mikerickson]

If you have 34 in B1, this will change A1 from ...[wkxx.xls]... to ..[xk34.xls]...

Sub test()
    Dim strFormula As String
    Dim cutPoint As Long
    
    strFormula = Range("A1").FormulaR1C1
    cutPoint = InStr(1, strFormula, "[wk", vbTextCompare) + 2
    
    Range("A1").FormulaR1C1 = Left(strFormula, cutPoint) & Format(Range("B1"), "00") & Mid(strFormula, cutPoint + 3)
End Sub

 

[tintin1012000]

Thanks mikerickson,
Your formula worked, how do I extend it to multiple cells

I want it to go from b6 to E19, I tried below but it did not work

Thanks again


strFormula = Range("B6:E19").FormulaR1C1
</pre>

 

[mikerickson]

Sub test2()
    Dim oneCell as Range
    Dim strFormula As String
    Dim cutPoint As Long
    
    For Each oneCell in Range("B6:E19"(
        With oneCell
            strFormula = .FormulaR1C1
            cutPoint = InStr(1, strFormula, "[wk", vbTextCompare) + 2
    
            .FormulaR1C1 = Left(strFormula, cutPoint) & Format(Range("B1"), "00") & Mid(strFormula, cutPoint + 3)
        With oneCell
    Next oneCell
End Sub