RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 788
- Office Version
- 2010
- Platform
- Windows
This is a fun one.
I'm updating a suite of numbers on a sheet with the new month's data, using VBA:
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:
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:
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.
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.