Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Macro Format Problem

Status
Not open for further replies.

rnordquest

New member
Jul 17, 2003
148
I have the following in a macro:

For i = 1 To endRow
PN = ActiveCell.Value
EC1 = ActiveCell.Offset(0, 1).Value
If EC1 = "" Then GoTo Next_PN

For j = 3 To Worksheets.Count
Sheets(j).Activate
Range("B4").Activate
ACV = ActiveCell.Value
If ACV = PN Then
ActiveCell.Offset(0, 16).Value = EC1

My If check never finds a match because PN=1234 and ACV="1234". Originally PN was formatted general and ACV as text. I reformatted ACV as general but I still don't get a match. How do I fix this?
 
Replies continue below

Recommended for you

Hello,

Instead of = try Like

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!
 
fyi...

it appears that the vba code sees PN as a number and ACV as text, regardless of formatted type. while the "like" comparason may suffice the needs, the cell function =value(B4) converts text to a numeric value. hence, the comparason can be made with like data (i.e. comparing numbers).

good luck!
-pmover
 
That would be best but each of 7 suppliers has 20+ part numbers each on their own worksheet. I have the files shared for multiple users and I don't fancy missing the season premier of Smallville tonight to do it when no one is around.

Apparently the guy who started these files formatted everything in the top x lines as text. Had he left them as general I think everything would have worked.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor