Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up a value from a row after verifying two other values | Excel Worksheet Functions | |||
Verifying value of a cell | Excel Discussion (Misc queries) | |||
Verifying Dates | Excel Worksheet Functions | |||
Verifying all data in each row is filled in | Excel Programming | |||
verifying date | Excel Programming |