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

Question

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

Bathsheba · Accepted Answer · 2014-06-16 10:23:40Z

1

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.

answered Jun 16, 2014 at 10:23

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3619834 Over a year ago

Thanks for the Help, I am new to VBA Macros, Can u please help me how to set RMV to the Adjusted string.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related