FIND TEXT / NUMBER VALUE COPY VALUE AND PASTE IN A SHEET

superstarr

Board Regular
Joined
Jul 10, 2003
Messages
54
Hi
was hopeing someone could help
been trying to make a macro that will
.. pop up the FIND (cntrl F) button,
allow me to enter a value (can be part of the text say "choc" instead of chocolate) even cool with *choc*
search a sheet called CAT and once the value is found ask me (with a button) if this is the item i was looking for,
(with a button) ask me if i want to keep searching, accept this result, or cancel
... keep seaching will find the next value for choc -- then prompt the same thing as above, accept the result means copying a cell whcih is 4 cells to the left, then if another button could prompt me to continue or finish, if you continue then copied cell 4 over would then go into a memory, and we would look up the same value further down the sheet, if that value is selected then it too goes into memory until we say finish, at which point all values collected are pasted into a sheet called PRO (pasted in the next available empty cell in column C (cell C3 beging the fist possible entry - so it may paste value 1 in C6 and the next in C7, and then we end the seach. cancel would end the search.

i know its a lot to ask, been trying to piece my own code togehter from disc i bought from this site, holy macro and excel knowlege base.

if its helps i can paste my code so far but is not that amazing
thanks to the brave soul

Jon
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Try this;

Code:
Sub Macro1()
Dim findthis As String
    Sheets("CAT").Select
    Range("A1").Select

findthis = InputBox("Enter value to find", "Find")

    Do
    
    Cells.Find(What:=findthis, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
        
    If MsgBox("Is this the value you are looking for?", vbYesNo) = vbYes Then
    ActiveCell.Offset(0, -4).Copy Sheets("PRO").Range("C65536").End(xlUp).Offset(1, 0)
    If MsgBox("Do you want to finish?", vbYesNo) = vbYes Then Exit Do
        
    End If
    Loop
End Sub
 

superstarr

Board Regular
Joined
Jul 10, 2003
Messages
54
Jimboy
absolutely awsome, your code is 1/3 as long as mine and works like a charm

if i could ask one thing more, trying to put the cancel on all VB buttons so we can cancel the operation at any time

thnaks again
you saved me from going nuts with all this script

thnaks

Jon
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Try this, you don't need a cancel button on the second msgbox because No will exit sub anyway;

Code:
Sub Macro1()
Dim findthis As String, myans
    Sheets("CAT").Select
    Range("E1").Select

findthis = InputBox("Enter value to find", "Find")

    Do
    Cells.Find(What:=findthis, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
        
    myans = MsgBox("Is this the value you are looking for?", vbYesNoCancel) '= vbYes Then
    If myans = vbYes Then
    ActiveCell.Offset(0, -4).Copy Sheets("PRO").Range("C65536").End(xlUp).Offset(1, 0)
    If MsgBox("Do you want to finish?", vbYesNo) = vbYes Then Exit Do
    ElseIf myans = vbCancel Then Exit Sub
    Else
    End If
    Loop
End Sub
 

superstarr

Board Regular
Joined
Jul 10, 2003
Messages
54
wow, very cool
thanks for everything
and the best part is i follow the logic just need to learn more of the syntax and possibility

really appreciate it jimboy
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,095
Members
425,452
Latest member
htay44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top