Could you help improve recorded macro

[Sean15]

Sub Macro2()

'

' Macro2 Macro

'



'

Range("P1").Select

ActiveCell.FormulaR1C1 = "Cost Center"

Range("Q1").Select

ActiveCell.FormulaR1C1 = "Division"

Range("R2").Select

ActiveCell.FormulaR1C1 = "1"

Range("R2").Select

Selection.Copy

Range("C2:C8").Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _

SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

Range("P2").Select

ActiveCell.FormulaR1C1 = "=RIGHT(RC[-13],3)*1"

Range("P2").Select

Selection.AutoFill Destination:=Range("P2:P8"), Type:=xlFillDefault

Range("P2:P8").Select

Range("Q2").Select

ActiveCell.FormulaR1C1 = _

"=IFERROR(IFERROR(VLOOKUP(RC[-14],'lookup values'!R2C1:R27C2,2,0),VLOOKUP(RC[-1],'lookup values'!R2C1:R27C2,2,0)),"""")"

Range("Q2").Select

Selection.AutoFill Destination:=Range("Q2:Q8"), Type:=xlFillDefault

Range("Q2:Q8").Select

Range("M9").Select

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-7]C:R[-1]C)"

Range("M10").Select

End Sub

Column C – I’m converting numbers formatted as text to numbers so I’d like “PasteSpecial *1 to extend to the last row of data, and not a range.

Column P – I’d like to extend formula to the last row of data, and not a range.

Column R – I had to add 1 in row R2 to help execute the PastSpecial*1 in column C. Is there a more efficient way to execute the PastSpecial*1 without adding 1 in R2?

Column Q - I’d like to extend formula to the last row of data, and not a range.

S2 – I added VLookup formula in S2 to help insert formula in column Q. Is there a better way to do this?

M9 - could we add subtotal to the row following the last row of data in column M

Thank you for your help.

 

[petertenthije]

Column C is already numerical only. You can tell because there are no red triangles in the topleft corner (as is the case in column P and Q).

Column P will be numerical only as well with below VBA.

As for the summary being put in M10: that's because there is something in cell C9. Your printscreen from reply 8 confirms this as well. You might not be able to see it, but something is there. Probably a space or a tab.

Sub Macro1()

' https://www.mrexcel.com/board/threads/could-you-help-improve-recorded-macro.1264384/

' Set variables.
    WB = ActiveWorkbook.Name
    WS = ActiveSheet.Name
    Dim lastrow As Integer
    Dim lastrow_vlookup As Integer
    lastrow = Workbooks(WB).Sheets(WS).Cells(Sheets(WS).Rows.Count, "C").End(xlUp).Row
    lastrow_vlookup = Workbooks(WB).Sheets("Lookup values").Cells(Sheets(WS).Rows.Count, "A").End(xlUp).Row

' Add headers.
    Workbooks(WB).Sheets(WS).Range("P1").FormulaR1C1 = "Cost Center"
    Workbooks(WB).Sheets(WS).Range("Q1").FormulaR1C1 = "Division"

' Modify text to numbers in colu,n C.
    Set Rng = Workbooks(WB).Sheets(WS).Range("C2:C" & lastrow)
    For Each cel In Rng.Cells
        on error resume next
        cel.Value = CSng(cel.Value)
        on error goto 0
    Next cel

    Workbooks(WB).Sheets(WS).Range("P2:P" & lastrow).FormulaR1C1 = "=numbervalue(RIGHT(RC[-13],3))"

    Workbooks(WB).Sheets(WS).Range("Q2:Q" & lastrow).FormulaR1C1 = "=IFERROR(IFERROR(VLOOKUP(RC[-14],'lookup values'!R2C1:R" & lastrow_vlookup & "C2,2,0),VLOOKUP(RC[-1],'lookup values'!R2C1:R" & lastrow_vlookup & "C2,2,0)),"""")"


' Add subtotal below the last row of data in column M.
    Range("M" & lastrow + 1).FormulaR1C1 = "=SUBTOTAL(9,R2C:R" & lastrow & "C)"

End Sub

 

[Sean15]

The code in post #9 just needs a slight tweak. When I multiplying the stored values in column C by 1, and add *1 to formula in column P, the Vlookup formula on Col Q returns required value. We just need to automate those steps.

The code in post #11 is producing #Value in column P.

 

[petertenthije]

You do not need to do *1 in column P. The numbervalue but of the formula does that for you.

 

[Sean15]

Finally figured this out.
When I changed this line of code in post #9

Workbooks(WB).Sheets(WS).Range("P2:P" & lastrow).FormulaR1C1 = "=RIGHT(RC[-13],3)"

to

Workbooks(WB).Sheets(WS).Range("P2:P" & lastrow).FormulaR1C1 = "=RIGHT(RC[-13],3)*1"

Executed code work beautifully.

See minisheet below after code is executed

Cost Center calculator - TEST.xlsm
	A	B	C	D								L	M	N	O	P	Q
1	Element	Cost element name	Center	CO object name								Created on	Val.in rep.cur.	Posting Date	Document Date	Cost Center	Division
2	822000	Executive	621203	Chains								9/4/2024	-4,235.94	9/1/2024	9/4/2024	203
3	822000	Executive	621100	Chains								9/4/2024	-4,326.93	9/1/2024	9/4/2024	100
4	822000	Executive	621407	Chains								9/4/2024	-5,496.37	9/1/2024	9/4/2024	407
5	822000	Executive	621301	Chains								9/4/2024	-2,626.92	9/1/2024	9/4/2024	301	Miniatures
6	822000	Executive	621506	Chains								9/4/2024	-3,197.43	9/1/2024	9/4/2024	506	Blue Wildcats
7	822000	Executive	621301	Chains								9/4/2024	5,253.84	9/6/2024	9/4/2024	301	Miniatures
8	822000	Executive	621506	Chains								9/4/2024	6,394.85	9/6/2024	9/4/2024	506	Blue Wildcats
9													-8,234.90
10
Sheet1

Cell Formulas
Range	Formula
P2:P8	P2	=RIGHT(C2,3)*1
Q2:Q8	Q2	=IFERROR(IFERROR(VLOOKUP(C2,'lookup values'!$A$2:$B$27,2,0),VLOOKUP(P2,'lookup values'!$A$2:$B$27,2,0)),"")
M9	M9	=SUBTOTAL(9,M$2:M$8)

@petertenthije, thank you for your patience.

Regards,
Sean