struggling with range objects

[snowblizz]

Hi, warming up my Excels skills I decided to try and implement a set of rules in Excel.
I'm taking data from an XML file into a worksheet and now I'm getting tripped up by the range objects. Or so I believe.
As far as I can see both "units.Columns(1)" and "test" are the same thing. Debug.print reports the same range address. However it seems it is not because were I to change the "units" to "test" in the 'for each' statement I get a "type mismatch error 13"

Set units = ActiveSheet.ListObjects(1).DataBodyRange
Set test = ActiveSheet.ListObjects(1).DataBodyRange.Columns(1)

Debug.Print (units.Columns(1).Address) 'gives $A$3:$A$19
Debug.Print (test.Address) 'gives $A$3:$A$19
Debug.Print (ActiveSheet.ListObjects(1).DataBodyRange.Address) 'gives $A$3:$H$19
For Each u In units
unitname = u.Value
Select Case unitname
Case "G"
u.Offset(columnoffset:=colcount).Value = u.Offset(columnoffset:=2).Value * 3
Case "T"
ty = 1
Case "H"
If cHu = 0 Then
cHu = cHu + 1
u.Offset(columnoffset:=colcount).Value = 200
End If
End Select
Next
End Sub

As it is, it loops through all cells in A3:H19 and I'd like to be able to tell it to just look in the A column.
The amount of rows varies from file to file, and that's really where my problems stem from. I'd like to be able to find the last cell in the .databodyrange (or anything equivalent,ie. the XML table) and add values or perform other methods on cells relative to this. In this case last cell is "H19" so I'd like to be able to add text and values to "I19, I20 or I21".

Oh, and yes this IS part of a larger "application" that I'm making but I've got to start somewhere so I tried to pick the simplest set of rule to implement first.

 

[RoryA]

What have you declared u, units and test as?

 

[snowblizz]

What have you declared u, units and test as?

units & test as Range
u is not "defined" at all as it usually works without doing so, in previous fore each nexts I've done.

could it be "unitname" that is a problem as well, it is not defined either? that's usually the line it stops at i.e:

unitname = u.Value
</pre>
Would it matter much where I declare them?
Since I'm not a programmer I've never quite grasped the importance of declaring variables. VBA has been pretty lenient so far with me.

 

[RoryA]

Do you have any errors in the table? That is a common cause if you get an error reading the Value property.
Generally you should declare and type all variables (and I recommend setting the option in the VBE to require variable declaration). It will make your code easier to understand and avoid typos. If you don't use Option Explicit then VBA will simply create any required variables for you if you mistype a variable name. That can cause you hours of grief!