Need Help Creating a Macro

March 4, 2009 at 18:40:41
Specs: Windows XP
Hello I am looking for help, I am trying to figure out a way in Microsoft Excel that I can create a macro that would look at all the text in column A (Code). Starting in cell A2 if it equals X then it would populate column B, cell B2 with Y. In addition to this the text in Column A will have a date included that needs to be ignored during the search and replace. Also I would like to use some sort of look-up table that would be located in a different file. This way I don't have to update the code I can just change the table.

Step 1:
A B
Code New Code
ABC DEF 010101
BCD EFG 010101
CDE FGH 010101


Step 2:
A B A
Code New Code Look-up Table
ABC DEF 010101 MTABCDEFTM
BCD EFG 010101 MTBCDEFGTM
CDE FGH 010101 MTCDEFGHTM


Step 3:
A B A
Code New Code Look-up Table
ABC DEF 010101 MTABCDEFTM MTABCDEFTM
BCD EFG 010101 MTBCDEFGTM MTBCDEFGTM
CDE FGH 010101 MTCDEFGHTM MTCDEFGHTM

Thank you and please let me know if you have any ideas.

Joe


See More: Need Help Creating a Macro

Report •


#1
March 4, 2009 at 19:32:56
I need some clarification:

First - The columns in your example don't line up so it's hard to tell what is what. Try using the pre and /pre codes to format your post.

Second - What is meant by A B A? Are they supposed to be Column labels? Wouldn't that be A B C?

Third, and beyond...

Starting in cell A2 if it equals X then it would populate column B, cell B2 with Y

Where are you determining what X is (the search string) and where are you getting Y from?

Column A will have a date included that needs to be ignored during the search and replace.

Ignoring the date might be easy. However, you asked about searching Column A and populating Column B. Where does replace fit into this question?

I would like to use some sort of look-up table that would be located in a different file

And the purpose of this table would be what?


Report •

#2
March 5, 2009 at 08:21:17
DerbyDad03 thanks for your responce...Sorry the example didn't workout it kept shifting everthing to the left when I pasted it into the post.
I am not really sure how this will work or what I need to make it work, I just have an idea of what I want the macro to do.

In spreadsheet 1 there is going to be two or more columns. In column A there will be the origanal data that looks like this...
CODE
ABC DEF 010101
BCD EFG 010101
CDE FGH 010101
The macro will then look at just the ABC DEF part of the data, ignoring the date portion(010101). It will then compare that data to spreadsheet 2 which will contain the data that will be used for the comparison(I am not really sure how this will work though) it will work something like this...
CODE ....... NEW CODE
ABC DEF = MTABCDEFTM
BCD EFG = MTBCDEFGTM
CDE FGH = MTCDEFGHTM
Then after the comparison it will output the new code into column B of spreadsheet 1.

I hope this makes more sence then before.

Joe


Report •

#3
March 5, 2009 at 08:45:49
Depending on what your data looks like, you may not need a macro.

Assuming your dates are all 6 digits, then the "code" you want to search for is:

=LEFT(A1, LEN(A1)-7)

7 is used to get rid of the space before the date.

I would set up my lookup table to look like this and use VLOOKUP

(My table is in A12:B14)

       A          B
12   ABC DEF   MTABCDEFTM
13   BCD EFG   MTBCDEFGTM
14   CDE FGH   MTCDEFGHTM

The formula in Column B would be:

=VLOOKUP(LEFT(A1,LEN(A1)-7),A12:B14,2,0)


Report •

Related Solutions

#4
March 5, 2009 at 17:28:33
That worked perfect...just two question.

First, is there a way that I can store the table in A12:B14 in a separate excel document stored on my hard drive.

Second, is there a way to create a macro that would automatically import the formula into the entire column so it will convert the entire spreadsheet with a few clicks of the mouse.

Joe


Report •

#5
March 5, 2009 at 17:59:10
I stored a table (A1:B3) in a different workbook and used this formula...it seemed to work, even with Book1 closed.

I suggest you save the workbook with the table before building the formula.

=VLOOKUP(LEFT(A1,LEN(A1)-7),'C:\Documents and Settings\User\My Documents\[Book1.xls]Sheet1'!$A$1:$B$3,2,0)

I think it would take more typing to create to macro than to enter the formula manually.

Highlight the first cell in the column, scroll and Shift-click the last cell in which you need the formula. Type the formula in the Formula Bar and use Ctrl-Enter. That should fill the highlighted range with the formula.

An alternative is to just drag the formula down to AutoFill the range.


Report •


Ask Question