Dec 05 2021 12:19 AM
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
Dec 05 2021 12:38 AM
SolutionFindNext 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)
Dec 06 2021 01:58 PM
Dec 06 2021 02:24 PM
Dec 06 2021 02:27 PM
Fixed!
Dec 05 2021 12:38 AM
SolutionFindNext 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)