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 <peter...@verizonXSPAM.net> 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(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)

> > Is your worksheet protected?

Sean wrote:
>
> On Sep 23, 1:34 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
<<snipped>>

On Sep 23, 2:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Is your worksheet protected?
> Sean wrote:
>
> > On Sep 23, 1:34 pm, Dave Peterson <peter...@verizonXSPAM.net> 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(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 wrote:
>
> On Sep 23, 2:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > Is your worksheet protected?
> > Sean wrote:
> >
> > > On Sep 23, 1:34 pm, Dave Peterson <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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(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 wrote:
>
> > On Sep 23, 2:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > Is your worksheet protected?
> > > Sean wrote:
>
> > > > On Sep 23, 1:34 pm, Dave Peterson <peter...@verizonXSPAM.net> 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

'(E-Mail Removed)
'(E-Mail Removed)

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 <peter...@verizonXSPAM.net> 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(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 wrote:
> >
> > > On Sep 23, 2:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > > Is your worksheet protected?
> > > > Sean wrote:
> >
> > > > > On Sep 23, 1:34 pm, Dave Peterson <peter...@verizonXSPAM.net> 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
>
> '(E-Mail Removed)
> '(E-Mail Removed)
>
> 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 <peter...@verizonXSPAM.net> 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 <peter...@verizonXSPAM.net> 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(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 wrote:
>
> > > > On Sep 23, 2:08 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > > > Is your worksheet protected?
> > > > > Sean wrote:
>
> > > > > > On Sep 23, 1:34 pm, Dave Peterson <peter...@verizonXSPAM.net> 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
>
> > 'joe.blo...@abc.com
> > 'jack.blo...@abc.com
>
> > 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