VBA Macro to help clean up report

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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

New Member
Joined
Jul 13, 2010
Messages
23
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

New Member
Joined
Jul 13, 2010
Messages
23
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:

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
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:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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

New Member
Joined
Jul 13, 2010
Messages
23
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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
23TMLU4EN2BM0569697594HOK1/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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
pkaskaggs,

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

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
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:

pkaskaggs

New Member
Joined
Jul 13, 2010
Messages
23
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.
 

Forum statistics

Threads
1,085,714
Messages
5,385,409
Members
401,943
Latest member
xvpnkr

Some videos you may like

This Week's Hot Topics

Top