Need Help with something to do with macros
Posted by Excel n00b on January 23, 2002 7:11 AM
i am trying to make a simple ordering menu, i.e.
in cell D3 to D10 user can enter the no. code for a particualer item ( e.g 001), and in H3 to H10 user can enter the amount they want for each one(e.g. - 4), and the user need to enter the user no.(e.g. - 10) in cell F1. and i wanna if it is possible to make a macro so that it will 1st copy the value cell F1 and paste it in a cell which will depends on what the value in F1 is on another sheet (say sheet2) e.g if the value in F1, is 2, then it will paste it in the 4th colum and row 2, if it is 3 then 6th colum and row 2, if 4, thne 8the coloum and row 2 etc. then the value in D3:D10 will also copy and paste under the user no. and the value in H3:H10 will copy and paste it next to the place on the right hand side of D3:D10, so if the user no. is 3, then the the no. 3 will be copy into sheet2 in cell F2, and B3:B10 will copy to F3:F10 and H3:H10 will copy to G3:G10.
so that then i can store the data in shee2 and clear the cells in sheet1 and for another user to enter things in and store it etc.
so is it possible to make a macro does that??
if not is there other way to store the data easily?
plz help
Posted by Tom Dickinson on January 23, 2002 8:04 PM
This should do it.
Sub TkeOrdr()
Dim Cnt As Integer
Range("'Sheet2'!A2").Offset(, Range("F1") * 2 - 1) = Range("F1")
For Cnt = 3 To 10
Range("'Sheet2'!A" & Cnt).Offset(, Range("F1") * 2 - 1) = Range("D" & Cnt)
Range("'Sheet2'!A" & Cnt).Offset(, Range("F1") * 2) = Range("H" & Cnt)
Next
End Sub
Run it from the page where the orders are initially entered. I would put a button on the sheet where the orders are first entered, and connect it to this macro. You will need to change the Sheet2 designation to the name of the sheet where it is being copied (keep the single and double quotes in there, and also the exclamation point). If you want to get really fancy, create a form instead of using the spreadsheet for entering the orders.
Posted by Excel n00b on January 24, 2002 2:39 AM
it didnt work ;/
it got a erro msg :
run time error "1004":
Method 'Range of object' _ Global failed
---
btw do u know is there is any ste which teach how to use forms and stuff??
Posted by . on January 24, 2002 2:46 AM
.
Posted by Tom Dickinson on January 24, 2002 6:55 PM
I ran a test before I posted this and it ran perfectly. The error message indicates something is not being seen. This could be due to a apostrophe or quotation mark being omitted.
Copy the macro to your macro sheet, then run it and let me know what happens.
