UsedRange gives Type Mismatch, but declared as 'Range'? 1

[fsteeman]

Excel + Visual Basic does not look like a happy marriage, unless you are aware of all the pitfalls... Now this:

I cannot seem to use the UsedRange property no matter how I declare it, as Range, as Property, as Variant, nothing works! All I get is a Type mismatch all the time. Could someone please help!

Below follows the code:


Sub LowerTaxaAdd(ByVal Node As MSComctlLib.Node)

'Maketree declarations
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, t As Integer
Dim nNode As Node, RNode As Node
'Excel declarations
Dim xlapp As Excel.Application
Dim xlbook As Workbook
Dim xlsheet As Worksheet
Dim myRange As Range

'taxon declarations
Dim phylum As String
Dim xlTaxon(1 To 16, 1 To 1900) As String
Dim taxcat(1 To 16) As String
Dim taxparent As String, taxkey As String, parentstack As String

'set up Excel file
Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Open("F:\Hierarchy\FaEu_Animalia3.2.xls&quot
xlapp.Visible = False

'set up Treeview
TWC.ImageList = Nothing
TWC.Visible = True
cmdRefresh.Left = 1080
cmdRefresh.Width = 4095
lblLVinfo.Visible = False
shpLVinfo.Visible = False
LV.Visible = False
LVTaxStat.Visible = False
lblTaxStat.Caption = ""


'*Add other taxa
l = 4
i = Mid(Node.Key, 3, Len(Node.Key) - 2)
taxparent = Node.Key
'determine taxon level
Set xlsheet = xlbook.Worksheets(i)
Set myRange = xlsheet.UsedRange
Debug.Print "range xl sheet: " & myRange

[...snip...]

End Sub

 

[combo]

A nice piece of code. Note that you try to print range instead of address. The last line with:
Debug.Print "range xl sheet: " & myRange.Address
will work.

 

[fsteeman]

Thanks a million! It works!

 

[fsteeman]

I have another question, though, as I am using a rather time-consuming routine to check each cell in a single column to see if it is empty, if not, move on to the next row. Some sheets have almost two thousand rows. Is there any way to skip the empty cells until the next one that contains something (Like you very easily can do within Excel by using 'Ctrl-arrowdown')?

 

[fsteeman]

Darn it! I keep on stumbling upon new problems with this!

Now I get a Type Mismatch a completely different place. Below is the entire code of the procedure, with the offending line in red:


Sub LowerTaxaAdd(ByVal Node As MSComctlLib.Node)

lblTaxStat.Caption = "Please wait...."

'Maketree declarations
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer, t As Integer
Dim nNode As Node, RNode As Node
'Excel declarations
Dim xlapp As Excel.Application
Dim xlbook As Workbook
Dim xlsheet As Worksheet
Dim xlRange As Variant
Dim xlRow As Row
Dim strRange As String
'taxon declarations
Dim phylum As String
Dim xlTaxon(1 To 16, 1 To 1900) As String
Dim taxcat(1 To 16) As String
Dim taxparent As String, taxkey As String, parentstack As String

'set variables
taxcat(1) = "Kingdom"
taxcat(2) = "Phylum"
taxcat(3) = "Subphylum"
taxcat(4) = "Infraphylum"
taxcat(5) = "Class"
taxcat(6) = "Subclass"
taxcat(7) = "Infraclass"
taxcat(8) = "Superorder"
taxcat(9) = "Order"
taxcat(10) = "Suborder"
taxcat(11) = "Infraorder"
taxcat(12) = "Superfamily"
taxcat(13) = "Family"

'set up Excel file
Set xlapp = New Excel.Application
'Set xlbook = xlapp.Workbooks.Open("F:\Hierarchy\FaEu_Animalia3.2.xls&quot
Set xlbook = xlapp.Workbooks.Open("C:\Documents and Settings\fsteeman\Desktop\FaEu_Animalia3.2.xls&quot
xlapp.Visible = False

'set up Treeview
TWC.ImageList = Nothing
TWC.Visible = True
cmdRefresh.Left = 1080
cmdRefresh.Width = 4095
lblLVinfo.Visible = False
shpLVinfo.Visible = False
LV.Visible = False
LVTaxStat.Visible = False

'*Add other taxa
l = 4
i = Mid(Node.Key, 3, 2)
taxparent = Node.Key
'determine taxon level
Set xlsheet = xlbook.Worksheets(i)
Set xlRange = xlsheet.UsedRange
xlRange.NumberFormat = "0.0"
Debug.Print "range xl sheet: " & xlRange.Address
For t = 3 To 13
Debug.Print "(" & Chr(t + 64) & l & &quot: " & xlsheet.Cells(l, t)
If xlsheet.Cells(l, t) <> &quot;&quot; Then
'current taxon level is 't'
Exit For
End If
Next t
Debug.Print &quot;current taxon level: &quot; & t & &quot; (&quot; & taxcat(t) & &quot&quot;
For Each xlRow In xlRange.Rows
If xlsheet.Cells(l, t) <> &quot;&quot; Then
xlTaxon(t, l) = xlsheet.Cells(l, t) & &quot; (&quot; & taxcat(t) & &quot&quot;
taxkey = &quot;th&quot; & IIf(i < 10, &quot;0&quot;, &quot;&quot & i & Chr(t + 64) & l
Debug.Print taxparent, taxkey, xlTaxon(t, l)
Set nNode = TWC.Nodes.Add(taxparent, tvwChild, taxkey, xlTaxon(t, l))
End If
l = l + 1
Next xlRow
nNode.Expanded = True
lblTaxStat.Caption = &quot;&quot;

Set xlsheet = Nothing
Set xlbook = Nothing
xlapp.Quit

End Sub

 

[Danp129]

When stepping through, I couldn't Dim XlRow as Row. After commenting that line I was able to run your For statement..

 

[combo]

The first problem:
use specialcells property of range object:
myrange.specialcells(xlCellTypeConstants) - only cells with values
myrange.specialcells(xlCellTypeConstants) - only cells with formulas

The second - Danp129 is right, there is no row object.

See excel vb editor and excel vba help for specialcells details (also some info about working with ranges).

combo

 

[fsteeman]

Well, I still had to declare xlRow as something so I declared it as Variant and that worked... Thanks!

And thank you for the specialcells.property! I will be investigating how I can use this to make my routine more efficient...

Cheers,

Fedor

 

[combo]

Following excel, Rows property returns range object representing all rows in of the object (range, worksheet). So, xlRow should be declared as range.
When used with index, Rows(i), returns i-th row of the range, as range.
Row property returns the number of first row in the first area.

For Each.. is a good way to loop through cells:
Dim c As Range
For Each c In UsedRange.SpecialCells(xlCellTypeConstants).Cells
MsgBox c.Row
MsgBox c.Column
Next c

Set JoinRanges=Application.Union(Range(rng1).Range(rng2)) returns union of ranges.

Hope this will help.

combo