You are here:

Excel/Trouble with my 'FIND' statement

Advertisement


Question
Hi Bob,

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
  MyRange.Find(ActiveCell).Offset(0, 2).PasteSpecial  

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?

Thank you.

Bob

Answer
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.
HTH
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.