Assume the supplier no is entered in B7, and column A of the sheet named
"data" is the column with the supplier numbers
set rng = Worksheets("Data").Range("A:A).Find( _
Range("B7")
if not rng is nothing then
Range("B8").copy Destination:=rng.offset(0,3)
Else
msgbox Range("B7") & " supplier no was not found"
End if

--
Regards,
Tom Ogilvy



"shanman_lmtd " wrote in
message ...
Lets say we have the following on one worksheet (lets call it data):

Supplier No Discount Comment1
12345 5%
67890 10%

On the second worksheet (called info) I will have a vlookup so the user
enters the suplier number and the discount and comments field are
displayed.

I will have another comments box on the info sheet where users can
enter comments if the comments field (on the data worksheet) is blank,
hence being blank from the lookup.


The user would then click a button to enter the data. This data that
the user entered would then be copied and pasted to the respective
comment field on the data tab.

The reason for doing it like this is that there are over 700 suppliers
on any given weekly report and it currently takes a long time to sort
through the supplier data fields.

I came up with this macro: this will always paste the data into cell
d2. However I will need this data to be entered into the comments field
for that respective supplier. I thought I could have a formula that
displays the cell location of where the data should be pasted and use
that in the macro??

Range("B8").Select (This is the user input field)
Selection.Copy
Sheets("Data").Select
Range("d2")Select (Can I put a cell location here that has the
location of where to paste the data)
ActiveSheet.Paste

Many many thanks.


---
Message posted from http://www.ExcelForum.com/