Formula within code

[Smythe]

Hi guys

I am looking for the correct code that will allow me to take data from column A, extract the last 5 characters from the cell and add "980" to the start.
So if cell A1 is ZXZXZ83654, I would like cell B1 to be 98083654.

Does this make sense? I was experimenting with

ActiveCell.FormulaR1C1 = "="980"& RIGHT(RC[-24))"

but VBA does not approve!

Cheers

 

[Andrew Poulsom]

Try:

ActiveCell.FormulaR1C1 = "=980&RIGHT(RC[-24))"

 

[Mike Blackman]

Hi,

maybe;

=(980&RIGHT(A1,5))+0

or;

ActiveCell.Formula = "=(980&RIGHT(A1,5))+0"

 

[VoG]

Try

Range("B1").Value = "980" & Right(Range("A1").Value, 5)

 

[Smythe]

Thanks guys!

 

[Smythe]

Hmmm....oooops....

I forgot to mention that I need the formula to apply all the way down the column - so for every intance of data in column A, column B will have the formula applied?


Cheers
Andy

 

[Mike Blackman]

Hi,

Something like this?

Sub InsertFormula()
    
    Dim lngLastRow As Long
    
    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Range("B1:B" & lngLastRow).Formula = "=(980&RIGHT(A1,5))+0"

End Sub

 

[Andrew Poulsom]

You can't put that formula in column B because it doesn't have 24 columns to the left of it. To put it in column Y:

Sub Test()
    Dim LR As Long
    With ActiveSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("Y2:Y" & LR).FormulaR1C1 = "=980&RIGHT(RC[-24],5)"
    End With
End Sub