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

[superstarr]

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

 

[jimboy]

Try this;

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]

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]

Try this, you don't need a cancel button on the second msgbox because No will exit sub anyway;

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]

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