Excel/Trouble with my 'FIND' statement
I'm using Excel 2010 and have encountered a VBA problem which has me stymied. It has to do with a "Find" statement.
My VBA Procedure keeps track of my investment data in a file I call my master file. When this procedure is run and the update is made, the new detail record value replaces the existing value in the master record.
To verify that a detail record exists for each master, I first set column E (master value data) to color red. Then as I apply a detail record, I replace the red old master value with a new black value.
Master file names are 5-digit alphanumeric. Everything works fine until I read the detail record for "FORD ". Using a "Find" statement, the procedure apparently can't locate the FORD master, but rather finds a different master record "71008".
Just prior to the actual update. I verify that the color of the master value is red, that the Activecell = "FORD " and the
name of the master data range is = "MyRange", with MSGBOX statements.
I pick up the update value from the detail file with a "copy" statement and then run a MSGBOX statement for each of the parameters in the Find statement:
MsgBox MyRange.Find(ActiveCell).Offset(0, 2).Font.ColorIndex 'Should be 46, color red
MsgBox ActiveCell.Value 'Should be FORD
MsgBox MyRange.Find(ActiveCell).Offset(0, 2).Address 'Should be E17
The next statement pastes the value in MyRange
The Msgbox statements all produce the expected results. However the value is pasted to 71008, the wrong master.
What the heck is going on? Why wasn't the FORD value pasted to the FORD master? (I include a blank to pad the FORD name out to 5 positions.)
Bob, I hope I have adequately explained my problem. Can you help me?
What is missing for me is the Copy statement. Your msgbox statements don't at all indicate what was copied. Perhaps alongside the Copy statement you can put a debug.print (or another Msgbox) of the cell copied, because that's what you're pasting in the PasteSpecial.
At least this should give you a clue as to what's going on.