A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old June 26th 12, 11:14 AM
ixara ixara is offline
Junior Member
 
First recorded activity by ExcelBanter: Feb 2012
Posts: 10
Exclamation How to replace cell value based on other cell's value using macro

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
Ads
  #2  
Old June 26th 12, 06:58 PM posted to microsoft.public.excel.programming
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 1,021
Default How to replace cell value based on other cell's value using macro

On Tue, 26 Jun 2012 10:14:27 +0000, ixara > wrote:

>
>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
  #3  
Old June 27th 12, 04:00 PM
ixara ixara is offline
Junior Member
 
First recorded activity by ExcelBanter: Feb 2012
Posts: 10
Smile

dear Ron Rosenfeld..your codes works!...thank you very much..
  #4  
Old June 28th 12, 02:52 AM posted to microsoft.public.excel.programming
Ron Rosenfeld[_2_]
external usenet poster
 
Posts: 1,021
Default How to replace cell value based on other cell's value using macro

On Wed, 27 Jun 2012 15:00:07 +0000, ixara > wrote:

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


Glad to help. Thanks for the feedback.
  #5  
Old November 17th 12, 10:36 AM
cpatz cpatz is offline
Junior Member
 
First recorded activity by ExcelBanter: Nov 2012
Posts: 1
Default

[quote='Ron Rosenfeld[_2_];1603206']On Wed, 27 Jun 2012 15:00:07 +0000, ixara > wrote:

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


Glad to help. Thanks for the feedback.[/QUOTE



*** IGNORE THIS - I FOUND A MUCH SIMPLER METHOD OF ACHIEVING WHAT I NEED THROUGH A SIMPLE VLOOKUP FORMULA - THANKS ANYWAY! ***




Hi

I have a requirement to do the same thing but with a slight twist! I tried to edit your code to make it work but am very new to this and am not having much success:

I have one workbook called "code" and the worksheets within it:

Output
Code
Data

In the Data sheet I have Column A as a part number "ARCP" in Column B I have the long description of what ARCP relates to "Anderson Premier" then in Column C I have the next part of the code (a colour option) "B" then Column D is the long colour description "B" and so on. The data sheet looks like this:

RANGE RESULT COLOUR RESULT SIZE RESULT
ARCP Anderson Pro B Black S Small

I have 6 different parts of the code in the Data sheet and then in the column immediately to the right of each code section is the long description


Then:

In the Code worksheet I have set up 6 columns for each of the 6 parts of the code (columns A to F) and a further 6 columns to the right of those for the long description (I to N)

What I would like to be able to do is type in a short code in columns A to F and the long description to automatically populate using the data worksheet as a reference point.

Finally: I would like to concatenate the 6 columns (A to F) into column A on the "output" worksheet and similarly rows I to N into Row B on the "output" worksheet in order to provide a customer friendly parts list with part code and description.

The last bit I guess is relatively straight forward but I am really struggling with how to get the code to reference what I have entered in the codes sheet with that of the data sheet and insert the result into the relevant columns of the codes sheet.

Any assistance you could provide would ba massively appreciated

Thanks in advance!

Last edited by cpatz : November 17th 12 at 12:56 PM. Reason: Problem Solved!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find and replace a character in a cell with another cell's content JABAgdl Excel Programming 1 September 23rd 09 02:34 AM
increment cell value based on another cell's value Rosa Hildur Excel Programming 1 July 1st 08 09:31 PM
Select a cell based on an other cell's value wally_91 Excel Worksheet Functions 4 March 13th 08 12:24 AM
insert rows based on a cell's value (macro) Shoney Excel Programming 2 February 28th 08 02:14 AM
Varying a macro based on a cell's value Neal Zimm Excel Programming 4 December 27th 04 06:37 AM


All times are GMT +1. The time now is 05:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.