VBA Macro to help clean up report

[pkaskaggs]

I am trying to rearrange a report so it can be used in a differnt department. They only need certain information, and one column of info contains a five digit code that needs to be translated into an address.

I would like to create a macro that will delete all unwanted columns, Then insert a column next to the five digit address codes, and insert the address, the code stands for. It would also be ok for the address to replace the five digit code. Finally I would like to delete an entire row if one of the columns contains certain information that I will specify.

Summary
Delete columns:2,3,5,10,11,12,13,14,15,16,17,18,19,20

(Add column next to what will be 6 after the others are deleted {6 Contains the 5 Digit codes} Output Corrisponding address) or (Replace Five digit Code with specified address)

Delete Rows that contain specified data in Column 25

I hope this is easy enough to understand

Thanks for any help
Bryan

 

[hiker95]

pkaskaggs,

Run-time error '9':

What line of code causes the error?

What version of Excel are you using?

I am trying to automate it so it can be run easily multiple times a day. The report is exported from mainframe to excel. I was planning on having a blank template that coworkers could paste the information into everytime they exported then run the macro. Will this work?

You could create a template and place the macro in the template.

Open the template and download the expored data into it, and then run the macro.

 

[pkaskaggs]

You could create a template and place the macro in the template.

Open the template and download the expored data into it, and then run the macro.

That is is exactly what I was thinking, however that is when I get the error message. On the other hand, If I paste the code after I open the saved report it works perfectly.

...I tried again this morning, by opening the blank template I made, and it seemed to work. I amtrying to see under what conditions I get the error.

pkaskaggs,



What line of code causes the error?

What version of Excel are you using?

When I got the error it was line 9. It highlighted the line:

With Worksheets("PIPELINE")


Lastly is there a code I can use in VB that will essentially replace a Vlookup table, so I can populate that G column?

 

[pkaskaggs]

When I ran the successfull test I was using my PERSONAL.XLS!RecordData. When I ran it with the code that was in the workbook it gave me the error. I am saving the code to the actual sheet now and I think that may have solved my problem.

 

[pkaskaggs]

Thanks so much for your help hiker95, last question is can you show me how to insert this VlookUp:
=VLOOKUP(G2,'Dealer List'!$A$1:$B$1493,2,FALSE)
into your code. Im hoping we can change the 1493, on the Dealer List, so if we add to the list the macro will pick it up automatically.

My Data, is on a second page called Dealer List, I want that to populate the G column. The Lookup is functional, however I made a template and when I paste the data in it, my formulas get over written.

If I could insert formula into the code, so that it looks all the way to the end of Column G, as it may be 12-1500 rows depending on the day, and run within the macro, that would solve the rest of my problems.

Thanks in advance, and for all the help you have already given.

 

[hiker95]

pkaskaggs,

Can we have a screenshot of your two worksheets?

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[pkaskaggs]

This is template, and how it looks after running the code.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 128px"><COL style="WIDTH: 67px"><COL style="WIDTH: 97px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 78px"><COL style="WIDTH: 126px"><COL style="WIDTH: 150px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">3TMLU4EN2BM056969</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">7594</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2001/01/10</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Est VPC Arr</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">3TMJU4GN1AM095989</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">7188</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/05/13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTDBT4K32A1358131</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">1441</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/05/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">5TDKK4CC2AS343200</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">5328</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 15px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">4T1BF3EK5AU089590</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2532</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">GT</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">F</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">LA001</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">04025</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">M</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2009/11/30</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2009/12/24</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Est VPC Arr</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTLZE4FE7A1113611</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">6209</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/04/20</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTLZE4FE7A1114080</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">6208</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"></TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/06/30</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

All I have to do now is insert a VLookup that pulls infomatin off of sheet2, which is named Dealer list
Dealer List

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 129px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">959</TD><TD style="TEXT-ALIGN: right">42980</TD><TD>WESTFIELD,TX/</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">960</TD><TD style="TEXT-ALIGN: right">42981</TD><TD>WESTFIELD,TX/</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">961</TD><TD style="TEXT-ALIGN: right">42982</TD><TD>HOUSTON,TX/</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">962</TD><TD style="TEXT-ALIGN: right">42983</TD><TD>WESTFIELD,TX/</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">963</TD><TD style="TEXT-ALIGN: right">42984</TD><TD>GEORGETOWN,KY/SCO</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">964</TD><TD style="TEXT-ALIGN: right">42985</TD><TD>LONG BEACH,CA/</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">965</TD><TD style="TEXT-ALIGN: right">42986</TD><TD>WESTFIELD,TX/</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">966</TD><TD style="TEXT-ALIGN: right">42988</TD><TD>FORT WORTH,TX/</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

(Its about 1400 entries) and populates column G.If possible I would like the ability to add more dealer codes and cities if needed without having to change code.

This is what it should look like after code is run. Listing City State for every valid code.
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 128px"><COL style="WIDTH: 67px"><COL style="WIDTH: 97px"><COL style="WIDTH: 59px"><COL style="WIDTH: 98px"><COL style="WIDTH: 78px"><COL style="WIDTH: 101px"><COL style="WIDTH: 126px"><COL style="WIDTH: 150px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">3TMLU4EN2BM056969</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">7594</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2001/01/10</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Est VPC Arr</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">3TMJU4GN1AM095989</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">7188</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD>WESTFIELD,TX/</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/05/13</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">JTDBT4K32A1358131</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">1441</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD>WESTFIELD,TX/</TD><TD> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/05/12</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">5TDKK4CC2AS343200</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">5328</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">HO</TD><TD> </TD><TD> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">42980</TD><TD>WESTFIELD,TX/</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">K</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI"> </TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI; TEXT-ALIGN: right">2010/06/05</TD><TD style="FONT-SIZE: 9pt; FONT-FAMILY: CALIBRI">Rev Comp dt</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[hiker95]

pkaskaggs,


Sample worksheets before the new code to add the VLOOKUP formulae to Sheet1:

Excel Workbook
	A	B
1	42980	WESTFIELD,TX/
2	42981	WESTFIELD,TX/
3	42982	HOUSTON,TX/
4	42983	WESTFIELD,TX/
5	42984	GEORGETOWN,KY/SCO
6	42985	LONG BEACH,CA/
7	42986	WESTFIELD,TX/
8	42988	FORT WORTH,TX/
9
Dealer List

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1
2	3TMLU4EN2BM056969	7594	HO					K		1/10/2001	Est VPC Arr
3	3TMJU4GN1AM095989	7188	HO			42980		K		5/13/2010	Rev Comp dt
4	JTDBT4K32A1358131	1441	HO			42980				5/12/2010	Rev Comp dt
5	5TDKK4CC2AS343200	5328	HO			42980		K		6/5/2010	Rev Comp dt
6
Sheet1

After the new code to add the VLOOKUP formulae to Sheet1:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1
2	3TMLU4EN2BM056969	7594	HO					K		1/10/2001	Est VPC Arr
3	3TMJU4GN1AM095989	7188	HO			42980	WESTFIELD,TX/	K		5/13/2010	Rev Comp dt
4	JTDBT4K32A1358131	1441	HO			42980	WESTFIELD,TX/			5/12/2010	Rev Comp dt
5	5TDKK4CC2AS343200	5328	HO			42980	WESTFIELD,TX/	K		6/5/2010	Rev Comp dt
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Test()
' hiker95, 07/15/2010, http://www.mrexcel.com/forum/showthread.php?t=480930

'********** Add the code between the lines with the **********

Dim LR As Long, LR2 As Long

LR = Worksheets("Dealer List").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Sheet1").Range("G2:G" & LR2)
  .FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE))"
End With

'**********

End Sub

You can add the code from the "Dim" statement, to before the "End Sub" to your macro after the code to delete the columns.

 

[hiker95]

pkaskaggs,

The new code will check for the last row of data in worksheet Dealer List, and will adjust the VLOOKUP formulae accordingly.

 

[pkaskaggs]

pkaskaggs,
Option Explicit
Sub Test()
' hiker95, 07/15/2010, http://www.mrexcel.com/forum/showthread.php?t=480930

'********** Add the code between the lines with the **********

Dim LR As Long, LR2 As Long

LR = Worksheets("Dealer List").Cells(Rows.Count, 1).End(xlUp).Row
LR2 = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
With Worksheets("Sheet1").Range("G2:G" & LR2)
.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE)),"""",VLOOKUP(RC6,'Dealer List'!R1C1:R" & LR & "C2,2,FALSE))"
End With

'**********

End Sub

[/code]


You can add the code from the "Dim" statement, to before the "End Sub" to your macro after the code to delete the columns.

Its almost there, I made a template to paste the report in. When the macro is run it inserts all the correct fomulas but the codes, Which are already in the report, dont get the city and state. If I type one in it pops up, but the report is too long to retype all the dealer codes.

Again, thanks so much for the help, once we get this last part my Ill name my next kid hiker95!!

 

[pkaskaggs]

The code is saved as text, Im going to play with chanigng it to number in the macro. Thanks so much this site is great.