Compile Error Variable not defined - VBA script MS Excel 2010

**aoberoi83** · 07-02-2013, 08:02 PM

HI I am new to this forum and new to VBA scripting s need some advice on a vba script I have written. It is giving me Compile Error: Variable not defined error message when I run it.

I am trying to work out a vba script for removing apostrophe sign from all the cells whether in middle or end or front of the text or a text cell with formula's . So I came with the below script which is giving me Comile error variable not defined.

Option explicit
Sub DeathToApostrophe()
'
Dim s As Range, temp As String
If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
Application.ScreenUpdating = False
For Each Cells In Sheets("Calc Sheet JDE").Select
If s.HasFormula = False Then
'Gets text and rewrites to same cell without the apostrophe.'
s.Value = s.Text
End If
Next s
Application.ScreenUpdating = True
End Sub

Apart from this I have got another query that if I want this script to run on the whole workbook whether it has 10 or 20 sheets in it. In that case should I just put "This Workbook" instead of "Calc Sheet JDE").

Please advice if I am right or not, any other suggestions you are welcome to advice.

Another script I have written where I am getting the same compile error meassage with saying end with if and syntax error

Sub ApostroRemove()

For Each currentcell In Selection
If currentcell.HasFormula = False Then 'Verifies that procedure does not change the 'cell with the active formula so that it contains 'only the value. currentcell.Formula = currentcell.Value For Each currentcell In Selection If currentcell.HasFormula = False Then 'Verifies that procedure does not change the 'cell with the active formula so that it contains 'only the value. currentcell.Formula = currentcell.Value End If Next End Sub
If Next End Sub

End Sub

Thank you

CHeers

Amarjeet

**TMS** · 07-02-2013, 08:10 PM

In this line:

Please Login or Register  to view this content.

... you refer to sCells which is not defined. The range definition is also wrong.

You probably need (untested):

Please Login or Register  to view this content.

If you want to check all the sheets, you'd need to define a sheet variable and loop through each of the sheets.

Regards, TMS

**aoberoi83** · 07-02-2013, 08:45 PM

I didn't got you there could you pls explain it more. This what i understood is replace the following line with the one u provided

(For Each sCells In Sheets("Calc Sheet JDE").Select ) replace this with your line of code

For Each s In ActiveSheet.UsedRange

Is that what you meant? Pls correct my code as I am really not getting it.

Thank you

**aoberoi83** · 07-02-2013, 08:57 PM

I have tested this code for the loop and inserted my code to remove apostrphe sign in between. It is giving errors invalid Next variable reference. but if I run the loop code without inserting my code to remove apostrophe it works perfectly.

Please help me out with this code.

Sub WorksheetLoop()

Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For I = 1 To WS_Count

' Sub DeathToApostrophe()
'
Dim s As Range, temp As String
If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
Application.ScreenUpdating = False
For Each cell In Sheets("Calc Sheet JDE").Select
If s.HasFormula = False Then
'Gets text and rewrites to same cell without the apostrophe.'
s.Value = s.Text
End If
Next s
Application.ScreenUpdating = True
End Sub

' The following line shows how to reference a sheet within
' the loop by displaying the worksheet name in a dialog box.
MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub

Thank you