Replacing Cell Contents

[Sineadd]

Hi

I have an excel sheet where on Tab A I will have a drop down menu on column C (Products). This drop down menu will list all the fields from TAB B( Company name)
When I select TEST 1 on TAB A in column C I want the Product description from TAB B to populate in the column C. Is this possible??

TAB A

Company	SKU	Products
Company 1		Test2

TAB B

Company Name	Product Description	Producy SKU
Test 1	Corner Sofa	ABCD-123-ABC
Test 2	2 Seater Sofa Velvet Blue	APR-ABB-BLU

 

[Gerald Higgins]

There are various ways of doing this, here's one way
=index(TabB!A:A,match(C2,TabB!B:B,0))

 

[etaf]

do you want to replace the contents of C - so instead of
TEST2 it is replaced by
which seems to be implied in

2 Seater Sofa Velvet Blue

 

[Sineadd]

do you want to replace the contents of C - so instead of
TEST2 it is replaced by
which seems to be implied in

2 Seater Sofa Velvet Blue

Yes I want to replace the contents of cell C

 

[etaf]

so VBA needed not my area - sorry

 

[Sineadd]

so VBA needed not my area - sorry

No problem - thanks

 

[Sineadd]

I have inputted the below VBA code but I then have to select the ranges manually via pop up boxes.

Sub VBA_Replace()

Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range

xTitleId = "VBA_Replace"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False

For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value, Lookat:=xlWhole

Next

End Sub

So Basically the Macro should look up the Original Range -This is in TAB A, Column C and return the value from Column B on TAB B

The Replace Range for the above code is Column A & Column B on TAB B


Hope that all makes sense