formulaR1C1 with a variable and cell's value

sybo67

New Member
Joined
Jun 9, 2015
Messages
6
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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

Code:
Dim myrange As String
 myrange = "C2:" & "C" & Last_Row1
should be
Code:
Dim myrange As Range
Set myrange = Range("C2:C" & Last_Row1)
even though you aren't using it later in the code :confused:

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
 
Last edited:
Upvote 0
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

Code:
Dim myrange As String
 myrange = "C2:" & "C" & Last_Row1
should be
Code:
Dim myrange As Range
Set myrange = Range("C2:C" & Last_Row1)
even though you aren't using it later in the code :confused:

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
Code:
=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
 
Upvote 0
Possibly either...

Code:
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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

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
Back
Top