Verifying lines with other workbook
 

Hi

I am using Excel Programming to clean up and verify spreadsheets to prepare
them to be run on robot computer with a certain setup. The verification
involves various plans customers may have chosen and these plans are listed
in a file named plans.xls. The ones i have in the original file may be
entered incorrectly so they need to be verified that they are in fact a
valid plan before the file can loaded onto the robot. Could anyone please
explain the simplest way of doing this?

Also to determin how many lines there are in a spreadsheet I have used the
While value"" then linecount=linecount+1 in a For Next loop and simply
counting each line individually. Is there a quicker way to determine the
number of lines in a file?

Thanks in advance for your help

Charlotte


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11.08.2004

Verifying lines with other workbook
 

set rng = Cells(rows.count,1).End(xlup)

msgbox rng.row

would tell you the last row with a filled cell in column 1

to validate the entry for plan, you can

Dim rng as range, cell as range, res a variant
With workbooks("Plan.xls").Worksheets(1)
set rng = .Range(.cells(1,1),.cells(1,1).end(xldown))
End with

With Activesheet
for each cell in .Range(.Cells(1,1),.Cells(1,1).End(xldown))
res = application.Match(cell,rng,0)
if iserror(res) then
' doesn't match a plan in Plan.xls
end if
Next
End With

--
Regards,
Tom Ogilvy




"Charlotte Hagen" wrote in message
...
Hi

I am using Excel Programming to clean up and verify spreadsheets to
prepare
them to be run on robot computer with a certain setup. The verification
involves various plans customers may have chosen and these plans are
listed
in a file named plans.xls. The ones i have in the original file may be
entered incorrectly so they need to be verified that they are in fact a
valid plan before the file can loaded onto the robot. Could anyone please
explain the simplest way of doing this?

Also to determin how many lines there are in a spreadsheet I have used the
While value"" then linecount=linecount+1 in a For Next loop and simply
counting each line individually. Is there a quicker way to determine the
number of lines in a file?

Thanks in advance for your help

Charlotte


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11.08.2004

Verifying lines with other workbook
 

Oh, one more thing... I would also like to use the list in plans.xls to
generate the values of the ComboBox so the user has the option of correcting
the invalid plan. Could someone explain how I would import the plans on the
fly? The reason this needs to be done this way is that the plans may change
from time to time and updating the list is easier than modifiying the
script.

Thanks again!!!

Charlotte



"Charlotte Hagen" skrev i melding
...
Hi

I am using Excel Programming to clean up and verify spreadsheets to
prepare
them to be run on robot computer with a certain setup. The verification
involves various plans customers may have chosen and these plans are
listed
in a file named plans.xls. The ones i have in the original file may be
entered incorrectly so they need to be verified that they are in fact a
valid plan before the file can loaded onto the robot. Could anyone please
explain the simplest way of doing this?

Also to determin how many lines there are in a spreadsheet I have used the
While value"" then linecount=linecount+1 in a For Next loop and simply
counting each line individually. Is there a quicker way to determine the
number of lines in a file?

Thanks in advance for your help

Charlotte


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11.08.2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11.08.2004

Verifying lines with other workbook
 

for an activeX combobox on a userform:

With workbooks("Plans.xls").Worksheets(1)
set rng = .Range(.cells(1,1),.cells(1,1).end(xldown))
End with
Combobox1.rowSource = rng.Address(external:=True)

--
Regards,
Tom Ogilvy

"Charlotte Hagen" wrote in message
...
Oh, one more thing... I would also like to use the list in plans.xls to
generate the values of the ComboBox so the user has the option of
correcting
the invalid plan. Could someone explain how I would import the plans on
the
fly? The reason this needs to be done this way is that the plans may
change
from time to time and updating the list is easier than modifiying the
script.

Thanks again!!!

Charlotte



"Charlotte Hagen" skrev i melding
...
Hi

I am using Excel Programming to clean up and verify spreadsheets to
prepare
them to be run on robot computer with a certain setup. The verification
involves various plans customers may have chosen and these plans are
listed
in a file named plans.xls. The ones i have in the original file may be
entered incorrectly so they need to be verified that they are in fact a
valid plan before the file can loaded onto the robot. Could anyone
please
explain the simplest way of doing this?

Also to determin how many lines there are in a spreadsheet I have used
the
While value"" then linecount=linecount+1 in a For Next loop and simply
counting each line individually. Is there a quicker way to determine the
number of lines in a file?

Thanks in advance for your help

Charlotte


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11.08.2004




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.737 / Virus Database: 491 - Release Date: 11.08.2004