Verifying lines with other workbook

  • Thread starter Thread starter Charlotte Hagen
  • Start date Start date
C

Charlotte Hagen

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
 
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
 
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
 
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)
 
Back
Top