What is the Cause of this Error Q

[Sean]

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

 

[Guest]

Hi Sean,

Need more info. Can you post your code please?

Regards,

OssieMac

 

[Dave Peterson]

Is your worksheet protected?

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

 

[Sean]

Is your worksheet protected?

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

.Range("AI3:AI4").Cells.SpecialCells(xlCellTypeConstants)
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(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)

 

[Dave Peterson]

Is your worksheet protected?

On Sep 23, 1:34 pm, Dave Peterson <[email protected]> wrote:

<<snipped>>

 

[Sean]

<<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]

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(xlCellTypeConstants)

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(xlCellTypeConstants)
on error goto 0

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

 

[Sean]

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(xlCellTypeConstants)

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(xlCellTypeConstants)
on error goto 0

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









--

Dave Peterson- Hide quoted text -

- Show quoted text -

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

'(e-mail address removed)
'(e-mail address removed)

but it still fails.

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

 

[Dave Peterson]

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]

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.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

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

 

[Dave Peterson]

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.