Construct a string containing formulas in VBA?

[RockandGrohl]

This is a fun one.

I'm updating a suite of numbers on a sheet with the new month's data, using VBA:

LUC = ActiveCell.Column
endmo = LUC - 1
EndmoNam = Cells(2, LUC)


met.Activate

x = 3

Range("B9").Activate

Do Until ActiveCell.Row > 32

    Do Until ActiveCell.Column > 6
    
    If ActiveCell.Column < 7 Then
        ActiveCell.FormulaR1C1 = "=+Workings!R" & x & "C" & LUC & ""
        'ActiveCell.Offset(1, 0).Value = Format(EndmoNam, "mmm-yy") & " " & ""
        x = x + 1
    End If
    
    ActiveCell.Offset(0, 2).Activate
    Loop
ActiveCell.Offset(5, -6).Activate
Loop

Above the commented line is where I update the main figure for each metric. So on the Workings tab, on row 3 (x = 3 to start) and in column "LUC" (Look up column) it finds the main figure.

Below that, where the commented line is, is where I want to update the secondary figure which is a string.

The string currently looks like this:

Jul-21 £98k (+32k)

To break it down, "Jul-21" is held as a number formatted as date (EndmoNam), the update for "£98k" held to the column left of LUC (endmo) and the figure in brackets is held to the right of the LUC.


So what I'm trying to do in effect is:

activecell.offset(1,0).value = Format(EndmoNam, "mmm-yy") & Workings!R" & x & "C" & Endmo & "(" & Workings!R" & x & "C" & LUC + 1 & ")"

But it's obviously not working because a value won't take a formula into consideration.

Either I need to use a formula, and somehow convert the value, or use .value and convert the formula.

Is there a way to take the EndMoNam (which may be the Clng of 01/07/2021) and convert it to a string?


Thanks.

 

[Fluff]

Maybe

ActiveCell.Offset(1, 0).Value = Format(EndmoNam, "mmm-yy") & " " & ActiveCell.Offset(-1, -1).Value & "(" & ActiveCell.Offset(-1, 1).Value & ")"

 

[queuesmef]

I have a date in Col A2 = 7/1/2021 / Col B has formula pulling the End of the month formula =EOMONTH(A2,0) Col C2 =TEXT(B2, "dd/mm/yyyy") ~ this will get you the date in TEXT format;
I'm not exactly sure what you're after...but you could take the value in Col C and Range Value it to where you need it. Look up Excel Text Function & EOMONTH for additional info.....no coding needed! once you have formula, copy it down, Range Value if needed.

 

[RockandGrohl]

Maybe

ActiveCell.Offset(1, 0).Value = Format(EndmoNam, "mmm-yy") & " " & ActiveCell.Offset(-1, -1).Value & "(" & ActiveCell.Offset(-1, 1).Value & ")"

Hi Fluff,

Not sure this would work as the offset -1 -1 field isn't on the correct sheet.

The metrics page is where the results are being printed, the workings sheet is where the results are found on a table of data.

So that's why in the first instance the calculation is

ActiveCell.FormulaR1C1 = "=+Workings!R" & x & "C" & LUC & ""

So I'm going off row (which starts at 3 and iterates upwards) and column (LUC)

 

[RoryA]

Perhaps:

activecell.offset(1,0).formular1c1= "=""" & Format(EndmoNam, "mmm-yy") & """ &Workings!R" & x & "C" & Endmo & """(""&Workings!R" & x & "C" & LUC + 1 & """)"""

 

[RockandGrohl]

Perhaps:

activecell.offset(1,0).formular1c1= "=""" & Format(EndmoNam, "mmm-yy") & """ &Workings!R" & x & "C" & Endmo & """(""&Workings!R" & x & "C" & LUC + 1 & """)"""

Hi Rory,

This looks hopeful but it gives me Application Defined or Object Defined error.

I've since changed it to this:

Private Sub FillMetrics_Button_Click()
Dim db As Worksheet, met As Worksheet, grf As Worksheet, wrk As Worksheet
Dim LUC As Long, Lastrow As Long, endmo As Long
Dim EndmoNam As String


Application.ScreenUpdating = False

ControlPanel.Hide

Set db = Worksheets("Dashboard")
Set met = Worksheets("Metrics")
Set grf = Worksheets("Graphs")
Set wrk = Worksheets("Workings")

' Take metrics from Workings tab
wrk.Activate
Range("A2").Activate

Do Until ActiveCell.Value = "Look Ups"
ActiveCell.Offset(0, 1).Activate
Loop

LUC = ActiveCell.Column
endmo = Cells(2, LUC - 1).Value
EndmoNam = Format(endmo, "mmm-yy")


met.Activate

x = 3

Range("B9").Activate

Do Until ActiveCell.Row > 32

    Do Until ActiveCell.Column > 6
   
    If ActiveCell.Column < 7 Then
        ActiveCell.FormulaR1C1 = "=+Workings!R" & x & "C" & LUC & ""
        ActiveCell.Offset(1, 0).FormulaR1C1 = "=""" & EndmoNam & """ & Workings!R" & x & "C" & endmo & """("" & Workings!R" & x & "C" & LUC + 1 & """)"""

        x = x + 1
    End If
   
    ActiveCell.Offset(0, 2).Activate
    Loop
ActiveCell.Offset(5, -6).Activate
Loop

Application.ScreenUpdating = True

End Sub

Which still gives me application defined or object error.

Values of variables are as such:

x starts as 3
Endmo = 44378
EndmoNam = "Jul-21"

 

[RoryA]

If EndMo is 44378 then you really can't use it as a column number, what with there only being 16384 columns available!

 

[RockandGrohl]

If EndMo is 44378 then you really can't use it as a column number, what with there only being 16384 columns available!

Very true!

ActiveCell.Offset(1, 0).FormulaR1C1 = "=""" & Format(endmo, "mmm-yy") & """ & Workings!R" & x & "C" & LUC & """("" & Workings!R" & x & "C" & LUC + 1 & """)"""

Amended, still 1004'ing with application/object error

 

[RoryA]

Missed a couple of ampersands:

ActiveCell.Offset(1, 0).FormulaR1C1 = "=""" & Format(endmo, "mmm-yy") & """ & Workings!R" & x & "C" & LUC & "&""("" & Workings!R" & x & "C" & LUC + 1 & "&"")"""

 

[RockandGrohl]

Rory comes in, out of left field, knocks it out of the park. That's a home run!

You're like the Babe Ruth of Excel. Cheers!

I might have some more questions on this later. All the data is there but it's clear each one of the "workings!" data sets needs formatting into pounds.