CODEWITHSUNDEEP
excelvba
XLmatters
XLmatters

Reputation: 386

Calculate off current cell value, and place result into that same sell

I'm trying to calculate an answer based off the value of the cell and have the calculated value replace that current value upon macro execution. Example: If L8 has a text value of 123, I need to have a leading 8 and nth number of zeros to make it 8 characters long. So the result will look like 80000123. Below is the code I have thus far. Also, I only want to calculate and replace cells visible in visible rows, because a filter will be in place. Any help will be much appreciated.

Sub test()

Dim rng As Range
Dim cl As Range

Set rng = Range("L4:L" & Cells(Rows.Count, "L").End(xlUp).row)

For Each cl In rng.Cells
    If IsNumeric(cl.Value) And Len(cl.Value) > 0 Then
    cl.Formula = "=Text((rng), ""80000000""))"      'this line errors out
    End If
Next

End Sub

Upvotes: 1

Views: 39

Answers (1)

user4039065
user4039065

Reputation:

You need the value stitched into the formula.

cl.Formula = "=Text(" & cl.Value & ", ""80000000"")"

To use that formula's result as a true number add a double unary.

cl.Formula = "=--Text(" & cl.Value & ", ""80000000"")"
'alternate
cl.Formula = "=" & cl.Value & "+80000000"

Upvotes: 1

Related Questions