Macro - more help pls !!!!

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

0
Anthony2219 (255)
2/27/2005 10:35:02 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
286 Views

Similar Articles

[PageSpeed] 31

Anthony

try:

Sub test()
Dim i As Integer
Dim iRow 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")
iRow = Columns("A:A").Find _
      (What:=i, _
       After:=Range("A1"), _
       LookIn:=xlFormulas, _
       LookAt:=xlPart, _
       SearchOrder:=xlByColumns, _
       SearchDirection:=xlNext, _
       MatchCase:=False, _
       SearchFormat:=False).Row
Worksheets("sheet1").Activate
ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
    :=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
    MsgBox "An error has ocurred, please try again"
End Sub

Regards

Trevor


"Anthony" <Anthony@discussions.microsoft.com> wrote in message 
news:35F2230F-4532-42D1-AF03-CEE96D90206C@microsoft.com...
> 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
> 


0
Trevor9259 (673)
2/27/2005 11:12:16 PM
Trevor,
Your reply works, I will have to tweak it a little (hope it works as I'm a 
novice), but thanks so much as this is the final piece of the jigsaw in my 
workbook.

Many thanks, 

"Trevor Shuttleworth" wrote:

> Anthony
> 
> try:
> 
> Sub test()
> Dim i As Integer
> Dim iRow 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")
> iRow = Columns("A:A").Find _
>       (What:=i, _
>        After:=Range("A1"), _
>        LookIn:=xlFormulas, _
>        LookAt:=xlPart, _
>        SearchOrder:=xlByColumns, _
>        SearchDirection:=xlNext, _
>        MatchCase:=False, _
>        SearchFormat:=False).Row
> Worksheets("sheet1").Activate
> ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
>     :=Worksheets("sheet2").Cells(2, 1)
> 
> wks3.PrintOut preview:=True
> Exit Sub
> 
> err_handler:
>     MsgBox "An error has ocurred, please try again"
> End Sub
> 
> Regards
> 
> Trevor
> 
> 
> "Anthony" <Anthony@discussions.microsoft.com> wrote in message 
> news:35F2230F-4532-42D1-AF03-CEE96D90206C@microsoft.com...
> > 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
> > 
> 
> 
> 
0
Anthony2219 (255)
2/27/2005 11:33:04 PM
Anthony

<<Your reply works>> ... I knew that, but you're very welcome.  How so 
<<tweak it a little>>, it does what you said you wanted ... find the row and 
copy it to row 2 on sheet 2.  If you need any more help, post back.

Good luck with your workbook.

Regards

Trevor


"Anthony" <Anthony@discussions.microsoft.com> wrote in message 
news:EB89B5AF-3A42-4700-979D-072DEAA536E1@microsoft.com...
> Trevor,
> Your reply works, I will have to tweak it a little (hope it works as I'm a
> novice), but thanks so much as this is the final piece of the jigsaw in my
> workbook.
>
> Many thanks,
>
> "Trevor Shuttleworth" wrote:
>
>> Anthony
>>
>> try:
>>
>> Sub test()
>> Dim i As Integer
>> Dim iRow 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")
>> iRow = Columns("A:A").Find _
>>       (What:=i, _
>>        After:=Range("A1"), _
>>        LookIn:=xlFormulas, _
>>        LookAt:=xlPart, _
>>        SearchOrder:=xlByColumns, _
>>        SearchDirection:=xlNext, _
>>        MatchCase:=False, _
>>        SearchFormat:=False).Row
>> Worksheets("sheet1").Activate
>> ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
>>     :=Worksheets("sheet2").Cells(2, 1)
>>
>> wks3.PrintOut preview:=True
>> Exit Sub
>>
>> err_handler:
>>     MsgBox "An error has ocurred, please try again"
>> End Sub
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Anthony" <Anthony@discussions.microsoft.com> wrote in message
>> news:35F2230F-4532-42D1-AF03-CEE96D90206C@microsoft.com...
>> > 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
>> >
>>
>>
>> 


0
Trevor9259 (673)
2/27/2005 11:49:13 PM
Trevor,
By tweak I ment just change the "sheet" names and wording on the message 
boxes, oh and add the number input by the user to the error message box.
This I have done and it does all work, so thanks again
You shud be a MVP !!
rgds
Anthony

"Trevor Shuttleworth" wrote:

> Anthony
> 
> <<Your reply works>> ... I knew that, but you're very welcome.  How so 
> <<tweak it a little>>, it does what you said you wanted ... find the row and 
> copy it to row 2 on sheet 2.  If you need any more help, post back.
> 
> Good luck with your workbook.
> 
> Regards
> 
> Trevor
> 
> 
> "Anthony" <Anthony@discussions.microsoft.com> wrote in message 
> news:EB89B5AF-3A42-4700-979D-072DEAA536E1@microsoft.com...
> > Trevor,
> > Your reply works, I will have to tweak it a little (hope it works as I'm a
> > novice), but thanks so much as this is the final piece of the jigsaw in my
> > workbook.
> >
> > Many thanks,
> >
> > "Trevor Shuttleworth" wrote:
> >
> >> Anthony
> >>
> >> try:
> >>
> >> Sub test()
> >> Dim i As Integer
> >> Dim iRow 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")
> >> iRow = Columns("A:A").Find _
> >>       (What:=i, _
> >>        After:=Range("A1"), _
> >>        LookIn:=xlFormulas, _
> >>        LookAt:=xlPart, _
> >>        SearchOrder:=xlByColumns, _
> >>        SearchDirection:=xlNext, _
> >>        MatchCase:=False, _
> >>        SearchFormat:=False).Row
> >> Worksheets("sheet1").Activate
> >> ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
> >>     :=Worksheets("sheet2").Cells(2, 1)
> >>
> >> wks3.PrintOut preview:=True
> >> Exit Sub
> >>
> >> err_handler:
> >>     MsgBox "An error has ocurred, please try again"
> >> End Sub
> >>
> >> Regards
> >>
> >> Trevor
> >>
> >>
> >> "Anthony" <Anthony@discussions.microsoft.com> wrote in message
> >> news:35F2230F-4532-42D1-AF03-CEE96D90206C@microsoft.com...
> >> > 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
> >> >
> >>
> >>
> >> 
> 
> 
> 
0
Anthony2219 (255)
2/27/2005 11:57:02 PM
Thanks ... I wish!  But there's much more talent and experience out there 
than I've got ... I'm still learning

Regards

Trevor


"Anthony" <Anthony@discussions.microsoft.com> wrote in message 
news:9BE36862-4EAF-4A3A-A7DB-2496EF8FD7CB@microsoft.com...
> Trevor,
> By tweak I ment just change the "sheet" names and wording on the message
> boxes, oh and add the number input by the user to the error message box.
> This I have done and it does all work, so thanks again
> You shud be a MVP !!
> rgds
> Anthony
>
> "Trevor Shuttleworth" wrote:
>
>> Anthony
>>
>> <<Your reply works>> ... I knew that, but you're very welcome.  How so
>> <<tweak it a little>>, it does what you said you wanted ... find the row 
>> and
>> copy it to row 2 on sheet 2.  If you need any more help, post back.
>>
>> Good luck with your workbook.
>>
>> Regards
>>
>> Trevor
>>
>>
>> "Anthony" <Anthony@discussions.microsoft.com> wrote in message
>> news:EB89B5AF-3A42-4700-979D-072DEAA536E1@microsoft.com...
>> > Trevor,
>> > Your reply works, I will have to tweak it a little (hope it works as 
>> > I'm a
>> > novice), but thanks so much as this is the final piece of the jigsaw in 
>> > my
>> > workbook.
>> >
>> > Many thanks,
>> >
>> > "Trevor Shuttleworth" wrote:
>> >
>> >> Anthony
>> >>
>> >> try:
>> >>
>> >> Sub test()
>> >> Dim i As Integer
>> >> Dim iRow 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")
>> >> iRow = Columns("A:A").Find _
>> >>       (What:=i, _
>> >>        After:=Range("A1"), _
>> >>        LookIn:=xlFormulas, _
>> >>        LookAt:=xlPart, _
>> >>        SearchOrder:=xlByColumns, _
>> >>        SearchDirection:=xlNext, _
>> >>        MatchCase:=False, _
>> >>        SearchFormat:=False).Row
>> >> Worksheets("sheet1").Activate
>> >> ActiveSheet.Cells(iRow, 1).EntireRow.Copy Destination _
>> >>     :=Worksheets("sheet2").Cells(2, 1)
>> >>
>> >> wks3.PrintOut preview:=True
>> >> Exit Sub
>> >>
>> >> err_handler:
>> >>     MsgBox "An error has ocurred, please try again"
>> >> End Sub
>> >>
>> >> Regards
>> >>
>> >> Trevor
>> >>
>> >>
>> >> "Anthony" <Anthony@discussions.microsoft.com> wrote in message
>> >> news:35F2230F-4532-42D1-AF03-CEE96D90206C@microsoft.com...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>> 


0
Trevor9259 (673)
2/28/2005 12:07:43 AM
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.
0
Shafiee1 (6)
2/28/2005 5:25:02 AM
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:=InputBox("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
0
Shafiee1 (6)
2/28/2005 5:27:01 AM
Hi Trevor,

Just wanted to fine tune your macro..... :D
Your macro will work faster now.


Sub test()
Dim i As Integer
Dim iRow 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")
iRow = wks1.Columns("A:A").Find _
      (What:=i, _
       After:=Range("A1"), _
       LookIn:=xlFormulas, _
       LookAt:=xlPart, _
       SearchOrder:=xlByColumns, _
       SearchDirection:=xlNext, _
       MatchCase:=False, _
       SearchFormat:=False).Row
Worksheets("sheet1").Cells(iRow, 1).EntireRow.Copy Destination _
    :=Worksheets("sheet2").Cells(2, 1)

wks3.PrintOut preview:=True
Exit Sub

err_handler:
    MsgBox "An error has ocurred, please try again"
End Sub


Shafiee.
0
Shafiee1 (6)
2/28/2005 5:39:01 AM
Reply:

Similar Artilces:

userform help
I'm trying to insert data into the first empty cell. My code keeps overwriting the last entry...could someone help me figure out where I've messed up? Thank you in advance Dawna Private Sub CommandButton2_Click() Dim rRng As Range Set rRng = Range("A" & Range("A" & Rows.Count).End(xlUp).Row) With rRng.Offset(rRng.Count + 1, 0).Select rRng.Offset(1, 1) = Me.TbDate rRng.Offset(1, 3) = Me.cboname rRng.Offset(1, 5) = Me.TbPO rRng.Offset(1, 6) = Me.TbCustom Private Sub CommandButton2_Click() Dim rRng As Range LastRow = Range("A&qu...

RFR Interface Errors HELP!
Help! I keep getting the following errors on our Exchange 2k3 server. It's running on a Win2k3 DC that is also a Global Catalog server. It's been in this configuration since Exchange 2k3 was released. Suddenly, I'm getting these odd errors: Event Type: Error Event Source: MSExchangeSA Event Category: RFR Interface Event ID: 9143 Date: 4/19/2006 Time: 7:15:17 AM User: N/A Computer: SERVER_NAME_HERE Description: Referral Interface cannot contact any Global Catalog that supports the NSPI Service. Clients making RFR requests will fail to connect until a Global Catalog becomes av...

help balancing account
I'm misunderstanding something fundamental about balancing accounts in MS Money 2004. To get started I downloaded several ofx files from my bank and imported them. I marked all the transactions as reconciled and tried to balance the account. My statements show a starting and ending date and a beginning and ending balance. I have tried entering a statement starting date for "Statement date:" and the starting and ending balance from that statment. The result I get is: "Money has calculeated a different starting balance than the one you entered." Why is it asking me for...

Send Direct Email does nothing
I've been searching for a solution to this issue for a few days now, so whoever can help me resolve this will get a large supply of kudos! Here's the deal: This sytem was recently upgraded from 1.2 to 3.0. I did the upgrade myself and my user belongs to the powerful groups in both Active Directory and CRM so I'm pretty darn sure I've got the correct privledges. I created a Contact in CRM (another version of myself with an email at an external domain) and clicked 'Send Direct Email'. I received it and it was logged into history properly. Yeah! I then created anoth...

Help with a batch file
I have an application to image my hard disk partitions. I can run the application from commandline, specifying, among othjer options, the partition to target, and the location and name of the output image file. Here's what I want to do. List all the existing image files in the output path. They will be in the format C_Drive-001.tbi, and C_Drive-002.tbi etc. I want to find the seqence number of the last file, and use it to generate the filename for the next file. In this case it will be C_Drive-003.tbi. On the succesfull completion of the image, then delete the oldest ima...

HELP!!! Can't get forumla figured out!
Hello, This is my first posting to this forum, and I haven't been able to search using the right criteria (not even sure what I would search under) to find what I need so I thought I would post a thread with the question. I have an access query that gives me the results in a spreadsheet layout, with 2 worksheets on it. I need to create a forumla that will look at the data on the 2nd sheet and compare the values in 2 particular columns with the value in one cell on the 1st sheet and populate a different cell on the 1st sheet with a count of entries from the 2nd sheet. If Sheet B, Colu...

Trigger Macro on Open Spreadsheet
How can we force a macro to execute whenever a spreadsheet is first opened? Hi do you mean the file? If yes either name your macro Auto_Open() or put your code in the workbook event Workbook_Open() -- Regards Frank Kabel Frankfurt, Germany Beckie Davis wrote: > How can we force a macro to execute whenever a spreadsheet > is first opened? ...

Help! #5
How can I filter single columns say three out of eight columns without setting the autofilter for all the columns? Any help much apreciated. Select your columns (including headers) and apply autofilter. Anyway, since autofilter filters out data by hiding rows, columns not included in autofilter wil also be hidden. (please excuse my hefty english) -- AP "Pat Watson" <pat.n.watson@baesystems.com> a �crit dans le message de news: 4496a1e0$1_1@glkas0286.greenlnk.net... > How can I filter single columns say three out of eight columns without > setting the autofilt...

Help with Combo-box
Hey everyone, hopefully someone's run across a problem similar to this and their is a (simple :) solution). Basically I have a simple combo-box that is displayed in a dialog. Now, the problem is that I have around 7200 entries that I want to appear in that box as valid selections...well thus far I haven't been able to get that many entries as a possibility (the combo-box simply refuses to allow me to enter that many fields of data). :( Okay, I tried googling this (thought maybe there was a readily defined maximum on combo-box data size) but I couldn't find anything that wou...

Switchboard hangs when running a macro
Access 2003: I've recently discovered the Switchboard - a grand concept; wish it wasn't so hidden... When running a macro from a form, no problem. But when running the same macro from the Switchboard, Access hangs. It seems to run all of the steps in the macro, and finish, but then Access hangs. Any help would be appreciated. Thanks, Mike in Sugar Land, TX On Mon, 30 Nov 2009 21:28:03 -0800, Switchboard newbie <Switchboard newbie@discussions.microsoft.com> wrote: My crystal ball is broken. Can you post some code, stripped to the bare essentials? -Tom. ...

Need help extracting text from EDLs
I really am trying to learn this stuff, but I haven't programmed anything since my TRS-80 Basic days and I'm a bit overwhelmed. I want to extract text from Edit Decision Lists so that I have a list of shots used in a film. Here is an example of one event of an EDL: 002 TAPE004 V C 04:45:22:06 04:45:24:14 02:00:08:00 02:00:10:08 FROM CLIP NAME: 7C-4_B_ DLEDL: PATH: /raids/luc_1/bun/reel_02/oscans/1222/bun_7c_4_b_02/2048x1556/ The text that follows "FROM CLIP NAME:" is the name of the clip that I want to extract so that I have a document on onl...

HELP SCREENS, can't get help..
HELP SCREENS, can't get help.. 100723 1900 no pun.. can't get help on how to turn off "TILE" windows if using the term correctly. had seen setting somewhere, to stop Excel from minimizing to a narrow window when help opens (how the heck supposed to compare help to work doing when reducing window on us: cannot read window). where is the setting to stop windows / excel help from resizing to a narrow screen, when you open help. Top left of the help window autotile icon (looks like double page). It toggles between the help andexcel windows being tiled and vo...

Durations keep changing... Help!
Ok... it's me again - brand new user... I need to list resources (sometimes multiple) for each task, but ONLY show it shows up on a report for each person. We do not assign or track real/actaul hours. So here's what I've done to try to simplify... Task #1: Tasks/Advanced Tab: "Must Start On" with a start date (this is the only constraint I've set in the project); Fixed Duration; Effort Driven unchecked All other tasks have the same settings except no constraint date and default to "As Soon as Possible." Each Resource: Booking type - P...

HELP! When I removed Office XP did I lose by inbox file?
I didn't archive my inbox and personal folders before removing Office XP. Now I have reinstalled Office XP and thought I could find my old data files. but all of the import and repair tools work with PST which I don't have. I remember that previously there were seperate inbox files and can not find them now. Thank you for your input. Ron Be sure to show hidden and system files on your computer and then do a search for *.pst. This should get you started... "Ron" <anonymous@discussions.microsoft.com> wrote in message news:093b01c3ba63$265a8120$a401280a@phx.gbl....

Ex5.5 to E2003 intra org migration ...help
Hi.. Berfore I get to issue I would first like to begin by asking the following question..>> There seems to be some difference between Exchange 2003 and Exchange 5.5 and how permissions are applied to public folders. Exchange 2003 uses Active Directory objects, such as users and security groups; and Exchange 5.5 uses Exchange objects, such as mailboxes and distribution lists. This means to apply permissions to public folders, Active Directory must have the equivalent to an Exchange distribution list which is Universal security groups ..As a result to keep the permmisions on Exchan...

Rebar help
Does anyone know a good example of a Rebar control like the ones in VC++ 6 enviornment? Also, how to implement a dropdown toolbar menu? Thanks in advance. Bruce, Here, you'll find lots of articles - I'd be surprised if you don't find one matching your needs: http://www.codeguru.com/Cpp/controls/toolbar/ http://www.codeproject.com/docking/ Johan Rosengren Abstrakt Mekanik AB "Bruce" <xg1@hotmail.com> a �crit dans le message de news:uUlAHiNHEHA.324@tk2msftngp13.phx.gbl... > Does anyone know a good example of a Rebar control like the ones in VC++ 6 > en...

newbie needs help in Ezcel programming
Hi everyone, please help me with this, newbie to Excel. I know what I want but, but dont know how to get it! I have a spreadsheet called Rawdata.xls and within that a sheet called "table a". The sheet contains data within B10:I100 where column b contains a list of non-unique codes, and column C contains a number of unique identifiers. Another spreadsheet called Tables.xls contains a sheet called "Numbers". Cell B10 contains a list of all the codes contained in a defined range (which is the same as all the codes in column b of "table a" in RawData.xls What I ...

Publisher help #3
I created a website and i am able to upload it and view it in firefox but not in any version of IE. Not exactly what i did wrong. But any help would be appreciated. Thanks Astro Repost this question in microsoft.public.publisher.webdesign newsgroup and add the URL to your problem website. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Astroda" <Astroda@discussions.microsoft.com> wrote in message news:2ADC0F87-CD62-421D-8088-61541AD6117B@microsoft.com... > I created a website and i am able to upload it and view it in firefox but > not > in a...

Help with a formmula change in a "flag" parameter!
The formula I have below in Column R works just fine. It creates a flag for me (in Column R) that lets me know one of two things: “Client Failed to Enroll on Time”, and “Return Referral Form to Referent”. Here is that formula: =IF(H4="","",IF(K4<>"","",IF(S4="Sent","Client Failed to Enroll on Time",IF(AND(K4="",TODAY()>WORKDAY(H4,10,AA4:AA12)),"Return Referral Form to Referent","")))) The one change (addition) that I’d like to make, is this: If Column N is toggled to say “Hudson ...

Help on Outlook automation
Hi all, I need to register my callback functions with outlook, to get notifications on events like new mail received and retrieve the mail subject line and sender. I want to do the same through managed code. Any idea, how to achieve it? Regards, Neo You will probably want to post this in one of the Outlook programming groups. The real experts with code hang out there and seldom slum in these general groups. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without re...

Macro or VBA
I am dumb, but would like to automatically trim down reports that we get from our IT dept. We have to delete redundant columns (A,B,C,D,E,G,H,J,K)from each report. In other words, the only data we want to view is in F,I,J,P. Can this done more effciently with a macro or VBA/ Could someone give me an example of either so that I could work from and learn? Thank-You start a new workbook open one of those report workbooks record a macro into that new workbook (not the report workbook) when you delete the columns you don't want. stop recording back to that new workbook save it as a nice...

Add value when recording a macro
Hello, I recorded a macro to create a bar chart - I checked the checkbox to add the values. Stopped recording and showed me the chart with the values. If I run the macro again, it does not display the value. What is the code I need to always display the value? -- LizW After you create the chart, turn on the macro recorder again Select the chart, and choose Chart>Chart Options On the Data Labels tab, check Values Click OK Turn off the macro recorder In the recorded code, you'll see a line similar to this: ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _ Ha...

Landed Costs Setup help
Hello, I would appreciate some advice on landed costs setup. Let=92s say I import goods from Germany. I use a shipping company called CargoShipers, Inc. CargoShippers takes care of handling the shipping costs, duty and inspection fees. So I go into inventory and setup 3 service type items for shipping, duty and inspection. But what do I set the inventory asset account to? These are really expenses right? Then once I create the service type inventory items, I need to create 3 landed cost ids =96 one for each type and assign those ids to CargoShippes as the vendor correct? I...

For more products pls visit: www.king-trade.cn
iscount Air max 95 shoes www.king-trade.cn wholesale Air Max 87 shoes discount Air MAX LTD shoes sell Air max 90 shoes discount Air Max 88 shoes www.king-trade.cn discount Air MAX 89 shoes discount Air max tn shoes discount Air Max tn8 shoes discount Air MAX tn9 shoes www.king-trade.cn discount MEN'S WOMEN'S Shox R5 R4 trainers discount Men's women's shocks OZ NZ TL trainers For more products pls visit: www.king-trade.cn ...

Macro #12
Why would a macro behave differently attached to a command button than when event driven through calculate worksheet? What does "behave differently" mean to you? It might help for you to post both a description of what you're concerned about, and relevant parts of your code in a reply message. A couple of things different: Macros in a regular code module may have different default behavior than macros stored in a worksheet code module - for instance Range("A1") in a regular code module is equivalent to ActiveSheet.Range("A1"). In a worksheet module, t...