Speeding Up Macro in VBA

V

VexedFist

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
 
T

Tom Ogilvy

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 said:
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
Click to expand...
 
B

Bob Phillips

I would suggest creating a dynamic named range for that variable data.
Something like

=OFFSET($A$1,,,COUNT($A:$A),1)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

VexedFist said:
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
Click to expand...
 
V

VexedFist

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

do them all at once

--
Regards,
Tom Ogilvy



VexedFist said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
changing range in recorded macro 1
Complie Syntax Error 6
Column is overwritten 4
VBA Pivot Table Error 1
Importing Files into Excel using VBA 1
Running Macro Across More Than One Sheet 3
Out Of Memory Issues 1

Top