CODEWITHSUNDEEP
excelvba
user1996971
user1996971

Reputation: 543

In VBA, what is the syntax for writing a formula relative to a range?

In Excel, I've written a short script that should remove the first character of each cell containing a "*"symbol at the start of it. What I have so far is 

Sub Macro5()

Dim Rng As Range
Dim i As Long
i = 1

While i <= 20000
Set Rng = Range("A" & i)

    If InStr(Rng, "*") > 0 Then
        Rng.Offset(0, 1).Formula = "=Right(Rng,LEN(Rng)-1)"
        i = i + 1
    Else: i = i + 1

End If
Wend
End Sub

The line to call the script seems to work, but the formula getting placed into column B is "=Right(Rng,LEN(Rng)-1)", which gives a 'NAME?' error. How do I define the LEN formula to use Rng as a range, rather than as a 'word' on the spreadsheet?

Upvotes: 0

Views: 333

Answers (1)

iDevlop
iDevlop

Reputation: 25272

Using R1C1 type formulae makes life much easier in that situation.

Sub RemoveFirstStar()
    Dim rng As Range, c As Range

    Set rng = Range("A1:A2000")

    For Each c In rng.Cells
        If Left(c, 1) = "*" Then
            c.Offset(0, 1).FormulaR1C1 = "=mid(rc[-1],2,1000)"
        End If
    Next c
End Sub

For your particular code example, change the line after the IF:

Rng.Offset(0, 1).FormulaR1C1 = "=Right(RC[-1],LEN(RC[-1])-1)"

Upvotes: 1

Related Questions