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"
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.
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"
Code:
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
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.
Last edited: