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 use
enters the suplier number and the discount and comments field ar
displayed.

I will have another comments box on the info sheet where users ca
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 tha
the user entered would then be copied and pasted to the respectiv
comment field on the data tab.

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

I came up with this macro: this will always paste the data into cel
d2. However I will need this data to be entered into the comments fiel
for that respective supplier. I thought I could have a formula tha
displays the cell location of where the data should be pasted and us
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 th
location of where to paste the data)
ActiveSheet.Paste

Many many thanks

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

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/

Is that the whole code needed or do I insert that instead of;
Range("d2")Select

It does not seem to be working though - I get the following erro
message on this line.

set rng = Worksheets("Data").Range("A:A).Find( _
Range ("B7")



Compile error:
Expected: List seperator or )


Once again many thank

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

Seriously, if you can't debug that, should you be using macros?

There is a missing paren on the end.

Sub MacroAABB()
Dim rng as Range
set rng = Worksheets("Data").Range("A:A).Find( _
Range("B7").Value)
if not rng is nothing then
Range("B8").copy Destination:=rng.offset(0,3)
Else
msgbox Range("B7").Value & " supplier no was not found"
End if
End Sub


You should look in help at the Find method and become conversant with the
other available arguments.

--
Regards,
Tom Ogilvy


"shanman_lmtd " wrote in
message ...
Is that the whole code needed or do I insert that instead of;
Range("d2")Select

It does not seem to be working though - I get the following error
message on this line.

set rng = Worksheets("Data").Range("A:A).Find( _
Range ("B7")



Compile error:
Expected: List seperator or )


Once again many thanks


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