VBA Macro to help clean up report

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
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
 
Last edited:
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.
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0
pkaskaggs,


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


Excel Workbook
AB
142980WESTFIELD,TX/
242981WESTFIELD,TX/
342982HOUSTON,TX/
442983WESTFIELD,TX/
542984GEORGETOWN,KY/SCO
642985LONG BEACH,CA/
742986WESTFIELD,TX/
842988FORT WORTH,TX/
9
Dealer List





Excel Workbook
ABCDEFGHIJK
1
23TMLU4EN2BM0569697594HOK1/10/2001Est VPC Arr
33TMJU4GN1AM0959897188HO42980K5/13/2010Rev Comp dt
4JTDBT4K32A13581311441HO429805/12/2010Rev Comp dt
55TDKK4CC2AS3432005328HO42980K6/5/2010Rev Comp dt
6
Sheet1





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


Excel Workbook
ABCDEFGHIJK
1
23TMLU4EN2BM0569697594HO K1/10/2001Est VPC Arr
33TMJU4GN1AM0959897188HO42980WESTFIELD,TX/K5/13/2010Rev Comp dt
4JTDBT4K32A13581311441HO42980WESTFIELD,TX/5/12/2010Rev Comp dt
55TDKK4CC2AS3432005328HO42980WESTFIELD,TX/K6/5/2010Rev 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).


Code:
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.
 
Upvote 0
pkaskaggs,

The new code will check for the last row of data in worksheet Dealer List, and will adjust the VLOOKUP formulae accordingly.
 
Upvote 0
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!!
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top