CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Have formula to remove all characters to the right of the last char (there might be more then one) problems with vba

The formula =LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1) to remove all characters to the right of the last . (there might be more then one) works if I add it to a cell and copy down.

I can not figure out how add the formula to vba

I have the vba if there is only one .

(the cells to be evaluated are in column A and the output is to column E ) When I add the code ta a module c.Formula = "=LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)" it highlights in red and error Invalid Character with the | highligthed

Thanks

Sub NotWorking()
Dim c As range
Dim Lastrow As Long

With Sheets("sheet1")
    Lastrow = .range("C" & .Rows.Count).End(xlUp).Row
    For Each c In .range("E2:E" & Lastrow)
        c.Formula = "=LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)"
    Next
    .range("E1").Value = "Source"
End With

End Sub

Upvotes: 1

Views: 330

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

First tip:

when your are usign quotes in formula in VBA, you should use double qoutes. E.g.

instead 

Range("E1").Formula="=IF(A1="test",1,2)"

you should use

Range("E1").Formula="=IF(A1=""test"",1,2)"

Second tip:

There is no need to use loop when applying formula. There is a more efficient way to do it.

Next your code

For Each c In .range("E2:E" & Lastrow)
    c.Formula = "=LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)"
Next

gives you formula for E2 : =LEFT(A2,...), and formula for E3 again =LEFT(A2,...) (note in both formulas A2). I think, it's not what you expected. Use this one instead:

.Range("E2:E" & Lastrow).Formula="=LEFT(A2,FIND(""|"",SUBSTITUTE(A2,""."",""|"",LEN(A2)-LEN(SUBSTITUTE(A2,""."",""""))))-1)"

Above code applies formula to entire range. Formula would be adjusted for each row, e.g. formula for E2 would be =LEFT(A2,...), while formula for E3 would be =LEFT(A3,...) and so on.

But, if you need to have the same formula for entire column E (for E2 : =LEFT(A2,...), and for E3 again =LEFT(A2,...)), use absolute/mixed references (with $ sign):

.Range("E2:E" & Lastrow).Formula="=LEFT(A$2,FIND(""|"",SUBSTITUTE(A$2,""."",""|"",LEN(A$2)-LEN(SUBSTITUTE(A$2,""."",""""))))-1)"

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53126

In addition to the points @Simoco makes, you need to adjust the cell refernces in order to get the same effect copying a range gives you, as Excel will automatically adjust these for you (unless you use absolute refernces $), whereas VBA won't.

The easiest way to do this is to use the R1C1 version of cell references.

Try this

Sub Working()
    Dim rng As Range
    With Sheets("sheet1")
        Set rng = Range(.Cells(2, 5), .Cells(.Rows.Count, 3).End(xlUp).Offset(, 2))
    End With
    rng.FormulaR1C1 = "=LEFT(RC1,FIND(""|"",SUBSTITUTE(RC1,""."",""|"",LEN(RC1)-LEN(SUBSTITUTE(RC1,""."",""""))))-1)"

End Sub

Upvotes: 1

Related Questions