CODEWITHSUNDEEP
excelfunctionvba
user3619834
user3619834

Reputation: 1

Excel VBA Function Giving # Value Error when trying to use in Excel formulas

I am using the below code to remove vowels from a text and when i run it using VBA it works fine. But when i use the same formula =RMV(Text) in Excel it gives me an Error #Value. Below is the code, pls Help 

Function RMV(Text) As String
'RMV = RemoveVowels
Dim I As Long
Dim J As Long
Dim Ws As Worksheet
Dim Cell As Range

Set Ws = ActiveWorkbook.Sheets("sheet1")
I = 2
J = 1
Lastrow = Ws.Cells(Rows.Count, 1).End(xlUp).Row

For I = 2 To Lastrow  '' For Loop to cover all the cells in the range
    RMV = ""
    If Ws.Cells(I, 1) <> "" Then ''If condition to select each cell
        Text = Ws.Cells(I, 1)  '' Assigning the cell Value to variable Text
        For J = 1 To Len(Text) '' FOR loop to go scan each letter in the Text
            If Not UCase(Mid(Text, J, 1)) Like "[AEIOU]" Then 
                RMV = RMV & Mid(Text, J, 1) 
            End If
        Next J
            Lastrow1 = Ws.Cells(Rows.Count, 2).End(xlUp).Row 
            Cells(I, 2) = RMV 
    End If
Next I

End Function

Upvotes: 0

Views: 1066

Answers (1)

Bathsheba
Bathsheba

Reputation: 234695

Cells(I, 2) = RMV is your problem. During a function, you cannot write directly back to the worksheet (otherwise the calculation process would break).

The only thing you can do is to set RMV to the adjusted string. That is returned to the worksheet.

Upvotes: 1

Related Questions