CODEWITHSUNDEEP
variablesexcelexcel-formulavba
Burton Guster
Burton Guster

Reputation: 2223

Variable in ActiveCell.FormulaR1C1

I want my formula to yield the value in my column to the left a period and the variable Name, which refers to the sheet name. For example, if the column to the left was 10 and the worksheet name was 12. My value would be 10.12

I've tried the following, but I can't get Name to show up as the variable rather than "Name"

Dim ws As Worksheet
Dim Name As Integer

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Name = ws.Name

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]&""."" & Name "
Next ws

Thanks for any help.

Upvotes: 1

Views: 10150

Answers (1)

sous2817
sous2817

Reputation: 3960

This seems to get you closer:

Dim Name As String
Dim Output As String

Name = ActiveSheet.Name


Output = ActiveCell.Offset(0, -1).Value & "." & Name


ActiveCell.FormulaR1C1 = Output

Here is your code without all the selecting:

Sub foo()
Dim ws As Worksheet
Dim Name As String
Dim Output As String

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    Name = ws.Name
    Output = ws.Range("C2").Offset(0, -1).Value & "." & Name
    ws.Range("C2").FormulaR1C1 = Output
Next ws
End Sub

Upvotes: 2

Related Questions