Construct a string containing formulas in VBA?

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
This is a fun one.

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


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

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,990
Office Version
  1. 365
Platform
  1. Windows
Maybe
VBA Code:
ActiveCell.Offset(1, 0).Value = Format(EndmoNam, "mmm-yy") & " " & ActiveCell.Offset(-1, -1).Value & "(" & ActiveCell.Offset(-1, 1).Value & ")"
 

queuesmef

New Member
Joined
Dec 19, 2016
Messages
49
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

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Maybe
VBA Code:
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

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

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Perhaps:

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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Perhaps:

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

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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!

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,562
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Missed a couple of ampersands:

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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,144,388
Messages
5,724,071
Members
422,533
Latest member
garganod1

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