How to replace cell value based on other cell's value using macro

[ixara]

Dear all,

I have 2 excel files. The first file is the main file that hav
transactions. The other file contain static data which is a list of ful
code, description & short code. How do i create a macro to replace th
full code(ColumnC) in main file with description(ColumnB) found in th
static data file? But change the full code(ColumnC) in the main fil
only if the value in ColumnB is SOLID and the first 2 character in ful
code(ColumnC) is equal to short code(ColumnC) in the static dat
file.The following is the scenario for better understanding. Thanks i
advance for any help given.

Main file:
ColumnA ColumnB ColumnC
XY12083 SOLID HHIY
LK02491 TEMP HYTO
BGY1232 SOLID BSVV
UYTB981 SAMPEL JIEI

Static data file:
ColumnA ColumnB ColumnC
HHIY Household HH
HHXS Household HH
BSVV Bookstore BS
BSKY Bookstore BS
SPTB Sports SP
SPMI Sport SP

Expected output in the main file:
ColumnA ColumnB ColumnC
XY12083 SOLID Household
LK02491 TEMP HYTO
BGY1232 SOLID Bookstore
UYTB981 SAMPEL JIE

 

[Ron Rosenfeld]

Dear all,

I have 2 excel files. The first file is the main file that have
transactions. The other file contain static data which is a list of full
code, description & short code. How do i create a macro to replace the
full code(ColumnC) in main file with description(ColumnB) found in the
static data file? But change the full code(ColumnC) in the main file
only if the value in ColumnB is SOLID and the first 2 character in full
code(ColumnC) is equal to short code(ColumnC) in the static data
file.The following is the scenario for better understanding. Thanks in
advance for any help given.

Main file:
ColumnA ColumnB ColumnC
XY12083 SOLID HHIY
LK02491 TEMP HYTO
BGY1232 SOLID BSVV
UYTB981 SAMPEL JIEI

Static data file:
ColumnA ColumnB ColumnC
HHIY Household HH
HHXS Household HH
BSVV Bookstore BS
BSKY Bookstore BS
SPTB Sports SP
SPMI Sport SP

Expected output in the main file:
ColumnA ColumnB ColumnC
XY12083 SOLID Household
LK02491 TEMP HYTO
BGY1232 SOLID Bookstore
UYTB981 SAMPEL JIEI

Something like the following should get you started, but you can also do this with formulas EXCEPT for needing the results in Column C

============================
Option Explicit
Sub ProcessMain()
Dim rStatic As Range, rMainData As Range, c As Range, r As Range
Dim wbStatic As Workbook, wsStatic As Worksheet
Dim wbMainData As Workbook, wsMainData As Worksheet

Set wbStatic = Workbooks("Static.xlsx")
Set wsStatic = wbStatic.Worksheets("Sheet1")
With wsStatic
Set rStatic = .Range("A1", .Cells(.Rows.Count, "C").End(xlUp))
End With

Set wbMainData = Workbooks("Main.xlsm")
Set wsMainData = wbMainData.Worksheets("Sheet1")
With wsMainData
Set rMainData = .Range("A1", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each c In rMainData.Columns(2).Cells
If c.Value = "SOLID" Then
With rStatic.Columns(3)
Set r = .Find(what:=Left(c.Offset(columnoffset:=1), 2), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not r Is Nothing Then
c.Offset(columnoffset:=1).Value = r.Offset(columnoffset:=-1).Value
End If
End With
End If
Next c
End Sub
==================================

Using formulas:

MainData
D1: =IF(B1="SOLID",INDEX([Static.xlsx]Sheet1!$B$1:$B$6,MATCH(LEFT(C1,2),[Static.xlsx]Sheet1!$C$1:$C$6,0)),C1)
fill down as needed.

This will return #N/A if there is no match of the first two letters in column C of Main Data, with Column C of Static Data

 

[ixara]

dear Ron Rosenfeld..your codes works!...thank you very much.

 

[Ron Rosenfeld]

dear Ron Rosenfeld..your codes works!...thank you very much..

Glad to help. Thanks for the feedback.