About Us

Tom Ogilvy

do them all at once

--
Regards,
Tom Ogilvy

"VexedFist" wrote:

Tom,

Would it be quicker to Run each formula to the bottom of the page, then
Copy and paste values, or do them all at once Like I have?

Thank again.

Tom Ogilvy wrote:
Selection.AutoFill Destination:=Range("B2:M10000")
Range("B2:M1000").Select

would be
Dim lastrow as Long
lastrow = cells(rows.count,"A").End(xlup).row
Selection.AutoFill Destination:=Range("B2:M" & lastrow)
Range("B2:M" & lastrow).Select

--
Regards,
Tom Ogilvy

"VexedFist" wrote:

Help I have a Macro that opens a Text file downloaded from a Telephone
switch. The file is formatted and then I use various VLOOKUP commands
referencing other worksheets to complete the formatting. The Problem I
have is Two-Fold;

First: the File length is variable any where between 0 rows (empty,
never seen it happen, but) and over 30000 rows since the data is
dispersed;

Second: The VLOOKUP commands need to coorespond to the file length.

Here is what I have so far. I don't know if it would be simpler to
have the referenced workshhets within the same workbook, in the end
they all will be.

Any and all suggestions would be appreciated.

Sub DataSheet()
'
' DataSheet Macro

'
ChDir "C:\Temp Data Files\Reconfigured Data"
Workbooks.OpenText Filename:= _
"C:\Temp Data Files\Reconfigured Data\DataSheet.txt",
Origin:=437, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2),
Array(2, 2), Array( _
3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2),
Array(8, 2), Array(9, 2), Array(10 _
, 2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2),
Array(15, 2), Array(16, 2), _
Array(17, 2), Array(18, 2), Array(19, 2), Array(20, 2),
Array(21, 2), Array(22, 2), Array( _
23, 2), Array(24, 2), Array(25, 2), Array(26, 2), Array(27, 2),
Array(28, 2), Array(29, 2), _
Array(30, 2), Array(31, 2), Array(32, 2), Array(33, 2),
Array(34, 2), Array(35, 2), Array( _
36, 2), Array(37, 2), Array(38, 2), Array(39, 2), Array(40, 2),
Array(41, 2), Array(42, 2), _
Array(43, 2), Array(44, 2), Array(45, 2), Array(46, 2),
Array(47, 2), Array(48, 2), Array( _
49, 2), Array(50, 2), Array(51, 2), Array(52, 2), Array(53, 2),
Array(54, 2), Array(55, 2), _
Array(56, 2), Array(57, 2), Array(58, 2), Array(59, 2),
Array(60, 2), Array(61, 2)), _
TrailingMinusNumbers:=True
Columns("A:BZ").Select
Selection.ColumnWidth = 0.5
Columns("A:BZ").EntireColumn.AutoFit
Columns("A:BZ").Select
Selection.Sort Key1:=Range("E1"), Order1:=xlDescending,
Key2:=Range("D1") _
, Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal,
DataOption3 _
:=xlSortTextAsNumbers
Range("A1").Select
Selection.EntireRow.Insert
Windows("Data Phones.xls").Activate
ActiveWindow.WindowState = xlNormal
Range("A1").Select
Windows("DataSheet.txt").Activate
Range("B2").Select

Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert

Range("B2:M2").NumberFormat = "General"
Range("B2").FormulaR1C1 =
"=IF(RC[-1]"""",VLOOKUP(RC[-1],'[Capacity
Report.xls]Names'!C1:C3,2,FALSE),"""")"
Range("C2").FormulaR1C1 =
"=IF(RC[-2]"""",VLOOKUP(RC[-2],'[Capacity
Report.xls]Names'!C1:C3,3,FALSE),"""")"
Range("D2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,2,FALSE),"""")"
Range("E2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,5,FALSE),"""")"
Range("F2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,6,FALSE),"""")"
Range("G2").FormulaR1C1 = "=IF(RC1"""",VLOOKUP(RC1,'[Data
Phones.xls]Hp4k Optidat'!C1:C10,7,FALSE),"""")"
Range("H2").FormulaR1C1 = "='MACRO Data
Phones.xls'!ExtractElement(RC16,1,""-"")"
Range("I2").FormulaR1C1 = "='MACRO Data
Phones.xls'!ExtractElement(RC16,2,""-"")"
Range("J2").FormulaR1C1 = "='MACRO Data
Phones.xls'!ExtractElement(RC16,3,""-"")"
Range("K2").FormulaR1C1 = "."
Range("L2").FormulaR1C1 =
"=CONCATENATE(RC[-4],RC[-1],RC[-3],RC[-1],RC[-2])"
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[Hardware
Report.xls]Table'!C1:C12,3,FALSE)"
Range("B2:M2").Select
Selection.AutoFill Destination:=Range("B2:M10000")
Range("B2:M1000").Select
Columns("B:M").EntireColumn.AutoFit

Columns("B:M").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="..", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
end sub

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help with speeding up vlookup macro	[email protected]	Excel Programming	2	July 20th 06 05:59 PM
Need help speeding this up	KD[_5_]	Excel Programming	0	March 24th 06 05:17 PM
Speeding up a Data Validation macro	Ryan[_11_]	Excel Programming	2	June 28th 05 03:29 AM
help with speeding this up...	Simon	Excel Programming	16	April 26th 05 03:25 AM
speeding up a macro	Brenda[_5_]	Excel Programming	4	August 21st 03 12:56 AM

Menu

About Us