SOLVED

Transferring selected data to a new sheet and checking it is not already transferred

Copper Contributor

Hi wonderful people!

I am selecting lines from a worksheet - these are lines involving donations.  I use:

With Worksheets("Sheet1").Range("CPData")
      Set MyEdit = .Find("k", , xlValues, xlWhole, xlByColumns, , True)
End With

When an entry is found I read in the date, charity and amount from a line with the function code of donation which is k. Then I activate the charity sheet.

First I do a search to make sure the entry is not already there. I use:

With Worksheets("Charity").Range("Donations")
     Set DateCheck = .Find(Format(EntryDate, "d-mm-yyyy"), , xlValues, xlWhole, xlByColumns, , True)
End With

I check each line (if more than one) with the same date. If no line matches, I enter the line otherwise require a human confirmation (Yes enter it, no don't)

Then I go back to sheet1 and change the colour of the code (k) to show it has been copied.

This works on a single transfer BUT I want to be able to run the code to transfer ALL k entries.

When I go back to sheet1 MyEdit is still k. The cursor is still on the k. The active cell is unchanged.

I use the code:

With Worksheets("Sheet1").Range("CPData")
    Set MyEdit = .FindNext(MyEdit)
End With

The MyEdit and the (MyEdit) in that code still show as k and the range MyEdit is the activecell (ie all correct) BUT when I go on eg press F8 the MyEdit changes to the value of DateCheck  and the range of the first value of that date on sheet1 !!!! *#!

I have changed names to see if the problem was due to scope. On the Charity date search I first used MyEdit, then changed to MyEdit2, then to DateCheck but it made no difference at all the outcome was still as above.

I'm stuck. Is there someone who can assist me with this situation? With thanks, Sue

4 Replies
best response confirmed by Sue (Copper Contributor)
Solution

@Sue 

FindNext gets confused if you execute another Find in between the original Find and FindNext.

Change

Set MyEdit = .FindNext(MyEdit)
to

Set MyEdit = .Find("k", MyEdit, xlValues, xlWhole, xlByColumns, , True)

Thank you so much! When I read your reply I knew instantly the key word I had missed in my attempts - the second MyEdit in "look after"!!
I had also tried using the full .find again. But I did not realise the default was to search the activecell (which was myEdit) first. I discovered this when the search was "complete" because in this second search the search cell had the "firstaddress". I put in code to move it to the next cell but with the search value always being the same (ie k) and MyEdit being k and the cell below was also k and the active cell both before and after the move being k I became "lost" in a sea of k's! ie thinking of the value of MyEdit instead of the range. When I saw your answer I realized I had moved the cursor (good old days of Lotus123) but NOT the name "MyEdit". I looked up .find and read about .look after. You have not only solved my problem but I have learnt a lot from this event. The "All Donations" works perfectly now although I still need to optimize the code.Thank you again. Sue
Hi Hans, I marked YOUR reply as the best response to my problem but it looks like it has come up associated with the reply I made to you - how can this be fixed? Sue
1 best response

Accepted Solutions
best response confirmed by Sue (Copper Contributor)
Solution

@Sue 

FindNext gets confused if you execute another Find in between the original Find and FindNext.

Change

Set MyEdit = .FindNext(MyEdit)
to

Set MyEdit = .Find("k", MyEdit, xlValues, xlWhole, xlByColumns, , True)

View solution in original post