I am hitting debug 1004

"Unable to get th SpecialCells property of the Range class"

Any suggestions on what is causing this and how do I amend?

Thanks

Hi Sean,

Need more info. Can you post your code please?

Regards,

OssieMac

"Sean" wrote:

I am hitting debug 1004

"Unable to get th SpecialCells property of the Range class"

Any suggestions on what is causing this and how do I amend?

Thanks

Is your worksheet protected?



Sean wrote:

I am hitting debug 1004

"Unable to get th SpecialCells property of the Range class"

Any suggestions on what is causing this and how do I amend?

Thanks

--

Dave Peterson

On Sep 23, 1:34 pm, Dave Peterson wrote:
Is your worksheet protected?

Sean wrote:

I am hitting debug 1004

"Unable to get th SpecialCells property of the Range class"

Any suggestions on what is causing this and how do I amend?

Thanks

--

Dave Peterson


Code is pretty long, but see below, the problem area is in

.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"

This is where is obtains an e-mail address (which on my sheet are
valid). It has worked fine in the past but now its suddenly thrown up
this error


Sub Mail_New_Version()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

Sheets("E-Figures").Visible = True
Sheets("E-Access").Visible = True

Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

'Copy the sheets to a new workbook
Sourcewb.Sheets(Array("E-Figures", "E-Access", "Rules")).Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
'We exit the sub when your answer is NO in the security
dialog that you only
'see when you copy a sheet from a xlsm file with macro's
disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With

' 'Change all cells in the worksheets to values if you want
' For Each sh In Destwb.Worksheets
' sh.Select
' With sh.UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
' Destwb.Worksheets(1).Select
' Next sh

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-
mmm-yy hh-mm")

ActiveWindow.TabRatio = 0.908

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)


Sheets("E-Figures").Activate
Range("A1").Select

For Each cell In ThisWorkbook.Sheets("E-
Figures").Range("BJ1:BJ18")
strbody = strbody & cell.Value & vbNewLine
Next


For Each cell In ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

Is your worksheet protected?

Sean wrote:

On Sep 23, 1:34 pm, Dave Peterson wrote:
<<snipped

On Sep 23, 2:08 pm, Dave Peterson wrote:
Is your worksheet protected?
Sean wrote:

On Sep 23, 1:34 pm, Dave Peterson wrote:

<<snipped

Dave it is but I unprotect it towards the start of the code

Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

E-Figures is where the e-mail address in A13:AI4 reside, where I'm
having the problem

If there are no constants in that range, then this will fail...

For Each cell In ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)

Maybe you could check first

dim RngToCheck as range
....

set rngtocheck = nothing
on error resume next
set rngtocheck = ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)
on error goto 0

if rngtocheck is nothing then
'what should happen
else
For Each cell In rngtocheck.cells
...



Sean wrote:

On Sep 23, 2:08 pm, Dave Peterson wrote:
Is your worksheet protected?
Sean wrote:

On Sep 23, 1:34 pm, Dave Peterson wrote:

<<snipped

Dave it is but I unprotect it towards the start of the code

Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

E-Figures is where the e-mail address in A13:AI4 reside, where I'm
having the problem

--

Dave Peterson

On Sep 23, 4:49 pm, Dave Peterson wrote:
If there are no constants in that range, then this will fail...

For Each cell In ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)

Maybe you could check first

dim RngToCheck as range
...

set rngtocheck = nothing
on error resume next
set rngtocheck = ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)
on error goto 0

if rngtocheck is nothing then
'what should happen
else
For Each cell In rngtocheck.cells
...





Sean wrote:

On Sep 23, 2:08 pm, Dave Peterson wrote:
Is your worksheet protected?
Sean wrote:

On Sep 23, 1:34 pm, Dave Peterson wrote:

<<snipped

Dave it is but I unprotect it towards the start of the code

Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

E-Figures is where the e-mail address in A13:AI4 reside, where I'm
having the problem

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, not entirely sure what you mean. The contents of the cells
AI3:AI4 are




but it still fails.

BTW it worked previously with no problems, but in doesn't any more

I'm saying that if that range AI3:AI4 contains formulas or is empty, then that
code will fail. It's looking for constants. If there are no constants, then it
won't work.

Maybe you should double check that range once more.

Sean wrote:

On Sep 23, 4:49 pm, Dave Peterson wrote:
If there are no constants in that range, then this will fail...

For Each cell In ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)

Maybe you could check first

dim RngToCheck as range
...

set rngtocheck = nothing
on error resume next
set rngtocheck = ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)
on error goto 0

if rngtocheck is nothing then
'what should happen
else
For Each cell In rngtocheck.cells
...





Sean wrote:

On Sep 23, 2:08 pm, Dave Peterson wrote:
Is your worksheet protected?
Sean wrote:

On Sep 23, 1:34 pm, Dave Peterson wrote:

<<snipped

Dave it is but I unprotect it towards the start of the code

Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

E-Figures is where the e-mail address in A13:AI4 reside, where I'm
having the problem

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, not entirely sure what you mean. The contents of the cells
AI3:AI4 are




but it still fails.

BTW it worked previously with no problems, but in doesn't any more

--

Dave Peterson

On Sep 23, 6:55 pm, Dave Peterson wrote:
I'm saying that if that range AI3:AI4 contains formulas or is empty, then that
code will fail. It's looking for constants. If there are no constants, then it
won't work.

Maybe you should double check that range once more.





Sean wrote:

On Sep 23, 4:49 pm, Dave Peterson wrote:
If there are no constants in that range, then this will fail...

For Each cell In ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)

Maybe you could check first

dim RngToCheck as range
...

set rngtocheck = nothing
on error resume next
set rngtocheck = ThisWorkbook.Sheets("E-Figures") _
.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeCon stants)
on error goto 0

if rngtocheck is nothing then
'what should happen
else
For Each cell In rngtocheck.cells
...

Sean wrote:

On Sep 23, 2:08 pm, Dave Peterson wrote:
Is your worksheet protected?
Sean wrote:

On Sep 23, 1:34 pm, Dave Peterson wrote:

<<snipped

Dave it is but I unprotect it towards the start of the code

Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

E-Figures is where the e-mail address in A13:AI4 reside, where I'm
having the problem

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave, not entirely sure what you mean. The contents of the cells
AI3:AI4 are




but it still fails.

BTW it worked previously with no problems, but in doesn't any more

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Definitely Dave, there are values in AI3:AI4 in sheet E-Figures

Maybe you're unprotecting the wrong sheet:

Replace:
Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="123"

With

Sheets("E-Figures").Unprotect Password:="123"

or even

thisworkbook.Sheets("E-Figures").Unprotect Password:="123"

======
The only time I've seen .specialcells fail is when the sheet is protected or
there are no constants in that range.

I still think it's your data--not the code.


Sean wrote:

- Show quoted text -

Definitely Dave, there are values in AI3:AI4 in sheet E-Figures

--

Dave Peterson