Home |
Search |
Today's Posts |
#1
|
|||
|
|||
re Macro - too hard for me !!
Thanks to those who offered help but I still am not quite there with the
correct solution. R.Venkataraman's code was almost correct but I don't think I explained myself correctly. In my sheet1 the user inputs numbers (1-50) in column A.(pluss loads of other data in columns B:P) what I require is the macro to ask the user to input the 'job' they want to print. So if the user inputs <9 then column A in sheet 1 is searched for the number 9 then the contents of that particular row are copied (without formula - just cell values) and the data pasted onto row A2 of sheet 2. Each time the button is selected the data is always pasted to the same cell ref in sheet 2, thus overwriting any data already there. The code I have so far is this, Sub test() Dim i As Integer Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long On Error GoTo err_handler Set wks1 = Worksheets("sheet1") Set wks2 = Worksheets("sheet2") Set wks3 = Worksheets("sheet3") i = InputBox("type the row number desired") Worksheets("sheet1").Activate ActiveSheet.Cells(i, 1).EntireRow.Copy Destination _ :=Worksheets("sheet2").Cells(i, 1) wks3.PrintOut preview:=True Exit Sub err_handler: MsgBox "An error has ocurred, please try again" End Sub but if the user enters <9 as his/her selected request row A9 is copied and pasted. I want column A to be searched for the number <9 then whatever row this is, (any row from A5:A1000) copy this whole row and paste it into A2 of sheet 2. Sorry for the waffle but please can anybody help, as my head hurts!!! many thanks Anthony |
#2
|
|||
|
|||
try this sub- do you get what you want
=============== Public Sub test() Dim i As Single i = InputBox("type the number") Worksheets("sheet1").Activate On Error GoTo line1 ActiveSheet.Columns("a:a").Cells.Find(i, LookIn:=xlValue, after:=Range("a1")).Activate ActiveCell.EntireRow.Copy Destination _ :=Worksheets("sheet2").Range("a2") Worksheets("sheet2").Activate Range("a1").Select GoTo line2 line1: MsgBox "the particular number is available" line2: End Sub ============== if you have more than one such number in the columnA this macro picks up the first availabale number after A1. which row do you want to copy in that case may need some modification to sub - remove $$$ from email addresss to send email ================================================== = Anthony wrote in message ... Thanks to those who offered help but I still am not quite there with the correct solution. R.Venkataraman's code was almost correct but I don't think I explained myself correctly. In my sheet1 the user inputs numbers (1-50) in column A.(pluss loads of other data in columns B:P) what I require is the macro to ask the user to input the 'job' they want to print. So if the user inputs <9 then column A in sheet 1 is searched for the number 9 then the contents of that particular row are copied (without formula - just cell values) and the data pasted onto row A2 of sheet 2. Each time the button is selected the data is always pasted to the same cell ref in sheet 2, thus overwriting any data already there. The code I have so far is this, Sub test() Dim i As Integer Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long On Error GoTo err_handler Set wks1 = Worksheets("sheet1") Set wks2 = Worksheets("sheet2") Set wks3 = Worksheets("sheet3") i = InputBox("type the row number desired") Worksheets("sheet1").Activate ActiveSheet.Cells(i, 1).EntireRow.Copy Destination _ :=Worksheets("sheet2").Cells(i, 1) wks3.PrintOut preview:=True Exit Sub err_handler: MsgBox "An error has ocurred, please try again" End Sub but if the user enters <9 as his/her selected request row A9 is copied and pasted. I want column A to be searched for the number <9 then whatever row this is, (any row from A5:A1000) copy this whole row and paste it into A2 of sheet 2. Sorry for the waffle but please can anybody help, as my head hurts!!! many thanks Anthony |
#3
|
|||
|
|||
Hi Anthony,
You don't need a macro for it. You can do it by using worksheet functions. What you are trying to do is, you want to fetch data in a database by using an index right? That is easy. Use this formula: =INDIRECT("G" & MATCH(A1,F:F,0)) F:F is the index column, and G contains data. Why use macros when it can be done using worksheet functions.... with all the security warnings? Shafiee. "Anthony" wrote: Thanks to those who offered help but I still am not quite there with the correct solution. R.Venkataraman's code was almost correct but I don't think I explained myself correctly. In my sheet1 the user inputs numbers (1-50) in column A.(pluss loads of other data in columns B:P) what I require is the macro to ask the user to input the 'job' they want to print. So if the user inputs <9 then column A in sheet 1 is searched for the number 9 then the contents of that particular row are copied (without formula - just cell values) and the data pasted onto row A2 of sheet 2. Each time the button is selected the data is always pasted to the same cell ref in sheet 2, thus overwriting any data already there. The code I have so far is this, Sub test() Dim i As Integer Dim wks1 As Worksheet, wks2 As Worksheet, wks3 As Worksheet Dim lLastRow As Long On Error GoTo err_handler Set wks1 = Worksheets("sheet1") Set wks2 = Worksheets("sheet2") Set wks3 = Worksheets("sheet3") i = InputBox("type the row number desired") Worksheets("sheet1").Activate ActiveSheet.Cells(i, 1).EntireRow.Copy Destination _ :=Worksheets("sheet2").Cells(i, 1) wks3.PrintOut preview:=True Exit Sub err_handler: MsgBox "An error has ocurred, please try again" End Sub but if the user enters <9 as his/her selected request row A9 is copied and pasted. I want column A to be searched for the number <9 then whatever row this is, (any row from A5:A1000) copy this whole row and paste it into A2 of sheet 2. Sorry for the waffle but please can anybody help, as my head hurts!!! many thanks Anthony |
#4
|
|||
|
|||
You can do it with macro too... Try recording it. That is the easiest way.
And then you can modify it so that the user won't see cells being selected by the macro. Here is what you have to do. Select Tools - Macros - Record New Macro and then click ok Select the index column Press Ctrl + F Type an index number Click the stop button By doing that, you'll get a macro like this: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 2/28/2005 by Shaafee ' ' Columns("A:A").Select Selection.Find(What:="4", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate End Sub Now change the macro like this: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 2/28/2005 by Shaafee ' ' Dim idx idx = Sheets("Sheet1").Columns("A:A").Find(What:=InputBo x("Please enter the index number", "Index Prompt", 1), After:=Range("A1"), LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Value MsgBox Sheets("Sheet1").Range("B" & idx).Value Set idx = Nothing End Sub Now you might want to put the data in another cell of another sheet. Just change the second last line to: Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("B" & idx).Value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - Too hard for me!! | Excel Discussion (Misc queries) | |||
Macro - Too hard for me!! | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |