Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL VBA in VB6: "Unable to get SpecialCells"?
I'm trying to re-write several macros written in XL2000 into an application
written in VB6. I am barely competent in VBA, and much less so in VB. I have a macro (given by Tom Ogilvy, I believe) that works great in Excel VBA. The basic idea is to search through only the visible cells of a filtered worksheet and hide any row that does not contain the search string value. Like I said, it's fast and great in VBA. In VB, though, I get an error - "Unable to get the SpecialCells property of the Range class." The VB code follows. Any and all suggestions are most appreciated. Ed Dim MyTarget As String Dim myFind As Object Dim i As Integer Dim rng As Object Dim cntRows As Long MyTarget = "" MyTarget = InputBox("What text are you searching for?") If MyTarget = "" Or MyTarget = "False" Then GoTo Bye If objWkbk.Sheets("Sheet1").AutoFilterMode Then Set rng = objWkbk.Sheets("Sheet1").AutoFilter.Range Else cntRows = objWkbk.Sheets("Sheet1").UsedRange.Rows.Count MsgBox "This is set for TEST ONLY! Change before distributing." Set rng = objWkbk.Sheets("Sheet1").Range("A1", "D" & cntRows) End If ' **ERROR ON THIS LINE** For Each Cell In rng.Cells.SpecialCells(xlCellTypeVisible) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL VBA in VB6: "Unable to get SpecialCells"?
That error you mention occurs if there are no visible cells. Is that a
possibility? This works for me in VB6 Dim xl As Excel.Application Set xl = New Excel.Application Dim wkb As Excel.Workbook Dim wks As Excel.Worksheet Dim cell As Excel.Range Dim rngVis As Excel.Range xl.Visible = True Set wkb = xl.Workbooks.Add(1) Set wks = wkb.Worksheets(1) wks.Range("A1:A100").EntireRow.Hidden = True On Error Resume Next Set rngVis = wks.Range("A1:A101").Cells. _ SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rngVis Is Nothing Then For Each cell In rngVis.Cells Debug.Print "Found a visible cell: " & cell.Address Next Else Debug.Print "No visible cells found." End If "Ed" wrote in message ... I'm trying to re-write several macros written in XL2000 into an application written in VB6. I am barely competent in VBA, and much less so in VB. I have a macro (given by Tom Ogilvy, I believe) that works great in Excel VBA. The basic idea is to search through only the visible cells of a filtered worksheet and hide any row that does not contain the search string value. Like I said, it's fast and great in VBA. In VB, though, I get an error - "Unable to get the SpecialCells property of the Range class." The VB code follows. Any and all suggestions are most appreciated. Ed Dim MyTarget As String Dim myFind As Object Dim i As Integer Dim rng As Object Dim cntRows As Long MyTarget = "" MyTarget = InputBox("What text are you searching for?") If MyTarget = "" Or MyTarget = "False" Then GoTo Bye If objWkbk.Sheets("Sheet1").AutoFilterMode Then Set rng = objWkbk.Sheets("Sheet1").AutoFilter.Range Else cntRows = objWkbk.Sheets("Sheet1").UsedRange.Rows.Count MsgBox "This is set for TEST ONLY! Change before distributing." Set rng = objWkbk.Sheets("Sheet1").Range("A1", "D" & cntRows) End If ' **ERROR ON THIS LINE** For Each Cell In rng.Cells.SpecialCells(xlCellTypeVisible) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL VBA in VB6: "Unable to get SpecialCells"?
Hi, Tim. Thanks for answering.
"Tim Zych" wrote in message ... That error you mention occurs if there are no visible cells. Is that a possibility? No, it happens whether I have the sheet filtered or not. There are always data-filled cells visible in the range. The code worked fine in Excel VBA. It's in VB Im having problems. When I changed Dim rng As Object to Dim rng As Excel.Object and tried to "Make <project.exe", VB says "User-defined type not defined". I tried copying your code in as is and got the same error. I have VB 6.0(SP5) Learning Edition. Any suggestions are welcome. Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Excel Discussion (Misc queries) | |||
Unable to open a 82 KB XLSM file due to "Too many different cellformats" & "Converter failed to open the file." errors. | Setting up and Configuration of Excel | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
XP VBA: Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select | Excel Programming |