Finding end of data in Excel recordset

[TimGoff]

Hi,

I've got one column of data in Excel...from another application i would like to scroll through each record until the end, in the way you work through a text file until the end using

Do While EOF(#1)


Loop

etc

How could i do this?

Thanks
Tim

 

[originalxavier]

What are you trying to do with the data?
Xavier ----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rich Cook
----------------------------------------

 

[TimGoff]

Hi,

Just reading each line and checking it again a variable - if one row doesn't match, move to the next row until the end of the data....

 

[originalxavier]

One method you could use is a numrowscount so that it will count all of the rows that are in the column and then you could set the code to repeat x amount of times where x is the number of rows counted. ----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rich Cook
----------------------------------------

 

[SkipVought]

Hi,
Assuming that you data starts in A1 == Cells(1, 1)...

Sub OneCol()
   Dim r As Range, vValue, lRow as Long, iCol as Integer
   For Each r In Range(Cells(1, 1).CurrentRegion)
      With r
         vValue = .Value
         lRow = .Row
         iColumn = .Column
      End With
   Next
End Sub

You have the VALUE, ROW & COLUMN values ...

and there's lots mor that can be had Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[originalxavier]

You can use the following code to count the number of rows and repeat the code as many times as there are rows.


Dim numrows As Integer

'Count the Number of Rows
Range("a2&quot.Select
Range(Selection, Selection.End(xlDown)).Select
numrows = Selection.Rows.Count

'The following will repeat a conditional format for rows 1 - numrows
For RowI = 1 To numrows
If Cells(RowI, 26).Value = "Complete" Then
Range(Cells(RowI, 1), Cells(RowI, 28)).Select
Selection.Interior.ColorIndex = 33
Else: Range(Cells(RowI, 1), Cells(RowI, 28)).Select
Selection.Interior.ColorIndex = 6
End If
Next


Hope this helps...
Xavier
----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rich Cook
----------------------------------------

 

[xlbo]

IMHO It would be much faster to use the FIND method.

Sub findvariable()
myVar = range("A1&quot.text
'find last row of data
lRow = sheets("Sheet2&quot.range("A65536&quot.end(xlup).row
'initialise find method
with sheets("Sheet2&quot.range("A1:A" & lRow)
set fCell = .find(myVar,lookin:=xlvalues,lookat:=xlwhole)
if not fCell is nothing then
firstAddress = fCell.Address
Do
'process whatever you need to do with the found varaible
Set fCell = .FindNext(fCell)
Loop While Not fCell Is Nothing And fCell.Address <> firstAddress
else
msgbox &quot;Variable not found&quot;
End If
end with Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[TimGoff]

Thanks Fella's - plenty to look at. I'll have a look today

Thanks again

Tim