Amend Hard Code Of File Location

Delta Star

Board Regular
Joined
Oct 28, 2009
Messages
184
I've setup a macro but need to amend it but cannot get it to work. I'm trying to incorporate the Pickfolder Function but am struggling with this line

Code:
"=IF(COUNTIF(Summary!R10C2:R89C2,R2C)>0,'C:\Documents and Settings\Desktop\Current Models\[Monday.xls]Template'!R[-1]C,0)"

This is what I'm currently using and am trying to get rid of the hardcode of the file location.

Code:
    UserFile = PickFolder(strStartDir)
    If UserFile = "" Then
        Exit Sub
    End If
'
If Sheets("Summary").Range("A10") = True Then
    Sheets("Sheet4").Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(COUNTIF(Summary!R10C2:R89C2,R2C)>0,'C:\Documents and Settings\Desktop\Current Models\[Monday.xls]Template'!R[-1]C,0)"
    Selection.Copy
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End If
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try

Code:
    ActiveCell.FormulaR1C1 = _
        "=IF(COUNTIF(Summary!R10C2:R89C2,R2C)>0,'" & UserFile & "\[Monday.xls]Template'!R[-1]C,0)"
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,758
Members
417,109
Latest member
996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top