Macro Help
 

I have 2 workbooks that will be needed for this macro.

One is a simple list: column A = product code, column B = product
name

The second workbook is a forumula for our products, which uses only
product code so no one can get our formulas and take them.

Before we print this for internal use, we would like to convert the
product code to the product name by running a macro.

For example, I would like to search the formula workbook for the
product code and replace it with the product name. There are over 200
products, so doing a macro where I recorded replacing these
individually would take forever. Also, some of the product code are
very similar.. ie. VRM2004 VRM2004A VRM2004B. Obviously if you
search for VRM2004 it would change all of those, so it needs to be an
exact match with the product code in the first sheet.

Please help.


--
richinlaf31
------------------------------------------------------------------------
richinlaf31's Profile: http://www.excelforum.com/member.php...o&userid=25846
View this thread: http://www.excelforum.com/showthread...hreadid=392302

Macro Help
 

can anyone help me

--
richinlaf3
-----------------------------------------------------------------------
richinlaf31's Profile: http://www.excelforum.com/member.php...fo&userid=2584
View this thread: http://www.excelforum.com/showthread.php?threadid=39230

Macro Help
 

Have a look at the VLookup Function it should do everything you need

Macro Help
 

Thanks, but VLookup is a forumla. Can it be used in a macro also? I
don't want the product names to show up until a manager runs the macro
from their computer.

Any help would be greatly appreciated.


--
richinlaf31
------------------------------------------------------------------------
richinlaf31's Profile: http://www.excelforum.com/member.php...o&userid=25846
View this thread: http://www.excelforum.com/showthread...hreadid=392302

Macro Help
 

You could actually do it as soon as the manager types in the product code.

I put the table on sheet2 in A:B and check for product codes in A3:A65536.

Rightclick on the worksheet tab that should have this behavior and select view
code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myLookupRng As Range
Dim res As Variant

If Target.Cells.Count 1 Then Exit Sub 'one cell at a time

'headers in rows 1 and 2
If Intersect(Target, Me.Range("a3:a65536")) Is Nothing Then
Exit Sub
End If

Set myLookupRng = Worksheets("Sheet2").Range("a:b")

res = Application.VLookup(Target.Value, myLookupRng, 2, False)

Application.EnableEvents = False
If IsError(res) Then
Target.Value = "Invalid Code"
Else
Target.Value = res
End If
Application.EnableEvents = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

richinlaf31 wrote:

Thanks, but VLookup is a forumla. Can it be used in a macro also? I
don't want the product names to show up until a manager runs the macro
from their computer.

Any help would be greatly appreciated.

--
richinlaf31
------------------------------------------------------------------------
richinlaf31's Profile: http://www.excelforum.com/member.php...o&userid=25846
View this thread: http://www.excelforum.com/showthread...hreadid=392302

--

Dave Peterson