Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text to a specific cell reference
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text to a specific cell reference
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text to a specific cell reference
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text to a specific cell reference
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help - Macro to copy a specific range | Excel Worksheet Functions | |||
Need help - Macro to copy a specific range | Excel Worksheet Functions | |||
copy text to specific cell | Excel Discussion (Misc queries) | |||
Need Macro to copy specific sheet | Excel Worksheet Functions | |||
Macro to copy down specific number of rows | Excel Programming |