Error - SpecialCells Property of Range Class

[Pineapple_Crazy]

Hello All,

I'm trying to paste some values in a column removing any blanks that are copied with the following code shown below. I figured I could using special cells with xlCellTypeConstants to do so, but keep receiving the error "Unable to get the SpecialCells property of the Range class". I've done some research, but haven't been able to figure out a fix. Can some advise as to what I am doing incorrectly here? Thanks in advance!!

Sub FindingValues2()



Dim findrow As Long, findrow2 As Long
Dim find As Range
Dim StrFile As String
Dim StrPath As String
Dim r1 As Long, wb As Workbook




StrPath = "Y:\Finance\BI\Pete\Pete Documents\Misc\"
StrFile = Dir(StrPath & "Vendor*" & "*.xls*")


'msg box to enter vendor ID
myValue = InputBox("Please Enter the Vendor Name", "VENDOR NAME", "MACK001")


'Opens file
Set wb = Workbooks.Open(Filename:=StrPath & StrFile)
wb.Activate


'Finding values
findrow = Range("A:A").find(myValue, Range("A1")).Row
findrow2 = Range("H:H").find("Modified:", Range("H" & findrow)).Row


r1 = findrow


Do
    wb.Activate
    findrow = Range("A:A").find(what:=myValue, after:=Cells(findrow, 1)).Row
    findrow2 = Range("H:H").find(what:="Modified:", after:=Range("H" & findrow)).Row
    Range("A" & findrow + 0 & ":A" & findrow2 + 0).Offset(0, 8)[B].SpecialCells(xlCellTypeConstants).Cells.Copy[/B]
 


    'Shifts focus back to macro workbook and pastes data in given worksheet. Puts cursor at bottom of A (5000) and searches up for last data entered.
    Windows("FinalReport_Vendor.xlsm").Activate
    Sheets("Data").Activate
    ActiveSheet.Range("Y5000").End(xlUp).Offset(1, 0).Select
    'ActiveSheet.Paste
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=True, Transpose:=False
Loop While findrow > 1 And findrow <> r1




End Sub

 

[RoryA]

If you get that error, it means there aren't any cells of the type you specified.

 

[Pineapple_Crazy]

If you get that error, it means there aren't any cells of the type you specified.

Hi Rory,

Yeah, the problem is that sometimes there are values there and sometimes there is not. I'm just not sure how to fix the issue with that. Seems like it may not be possible. Thanks.

 

[RoryA]

Use a variable and an error handler:

Dim rgCopy as Range
On Error Resume Next
Set rgCopy = Range("A" & findrow + 0 & ":A" & findrow2 + 0).Offset(0, 8).SpecialCells(xlCellTypeConstants)
On Error Goto 0
If not rgcopy is nothing then
   rgcopy.copy
   ' pasting code goes here
   
   set rgcopy = nothing
End if
Loop while ...

 

[Pineapple_Crazy]

Use a variable and an error handler:

Dim rgCopy as Range
On Error Resume Next
Set rgCopy = Range("A" & findrow + 0 & ":A" & findrow2 + 0).Offset(0, 8).SpecialCells(xlCellTypeConstants)
On Error Goto 0
If not rgcopy is nothing then
   rgcopy.copy
   ' pasting code goes here
   
   set rgcopy = nothing
End if
Loop while ...

Ahhh, I see. Thanks a bunch Rory, I'll try this out soon and let you know.

 

[Pineapple_Crazy]

Works great RoryA, thanks again for the help!!

 

[RoryA]

Glad to help.