formulaR1C1 with a variable and cell's value

[sybo67]

I would like help if someone can be so kind... I've been testing different ways long enough... no hair left... literally. Trying to make a formula that when pasted in a cell range will use a file path as a variable and a cell's value that will change according to its location....
Dim fileLOCATION As String
fileLOCATION = Application.ActiveWorkbook.Path
Dim Last_Row1 As String
Last_Row1 = ActiveSheet.Range("A1048576").End(xlUp).Row
Dim Last_Col1 As Sstring
Last_Col1 = .Cells(1, .Columns.Count).End(xlToLeft).Column
Dim myrange As String
myrange = "C2:" & "C" & Last_Row1
Range("C2").Select
ActiveCell.FormulaR1C1 = "=" & fileLOCATION & "" & (Range("B2").Value)

need B2 value to change as it's copied down but fileLOCATION will stay the same
Thanks for your help in advance,
sybo67

 

[MARK858]

What is in cell B2? a workbook name, sheet name and cell reference?
As you are using the files path in the formula is the workbook being referenced closed?
What is the working formula you use when do it manually?

P.S.
Dim Last_Row1 and Col1 should be a Long or Integer and not a String

Dim myrange As String
 myrange = "C2:" & "C" & Last_Row1

should be

Dim myrange As Range
Set myrange = Range("C2:C" & Last_Row1)

even though you aren't using it later in the code

and

you are better off using Rows.Count rather than A1048576 as it is compliant with all versions of Excel.

i.e ActiveSheet.Range("A1048576").End(xlUp).Row to ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

 

[sybo67]

What is in cell B2? a workbook name, sheet name and cell reference?
As you are using the files path in the formula is the workbook being referenced closed?
What is the working formula you use when do it manually?

P.S.
Dim Last_Row1 and Col1 should be a Long or Integer and not a String

Dim myrange As String
 myrange = "C2:" & "C" & Last_Row1

should be

Dim myrange As Range
Set myrange = Range("C2:C" & Last_Row1)

even though you aren't using it later in the code

and

you are better off using Rows.Count rather than A1048576 as it is compliant with all versions of Excel.

i.e ActiveSheet.Range("A1048576").End(xlUp).Row to ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Thank you so much for your reply... I really appreciate your help. I really was just trying to concatenate a string variable (ie this was the filelocation from code) and a cell "B2" string value. Your comment about how I would do this manually turned a light bulb on in my head. I just pasted the filelocation value into a column then

=A2&"\"&B2[CODE] worked like a charm. Was trying to create a formula that would simply join a varible (filelocation) and a cell (B2)... I'm sure there is a way but I must have "'s or ()'s or something out of place. thanks again for your efforts. sybo67

 

[MARK858]

Possibly either...

Sub xxx()
    Dim Last_Row1 As Long
    Dim fileLOCATION As String, myrange As Range

    fileLOCATION = ActiveWorkbook.Path

    Last_Row1 = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    Set myrange = ActiveSheet.Range("C2:C" & Last_Row1)
    myrange.FormulaR1C1 = "=""" & fileLOCATION & """&""\""&RC[-1]"

End Sub

or

Sub xxx2()
    Dim Last_Row1 As Long, myrange As Range

    Last_Row1 = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    
    Set myrange = ActiveSheet.Range("C2:C" & Last_Row1)
    myrange.FormulaR1C1 = "=R2C1&""\""&RC[-1]"
 
End Sub