ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Speeding Up Macro in VBA (https://www.excelbanter.com/excel-programming/374328-speeding-up-macro-vba.html)

VexedFist[_2_]
Speeding Up Macro in VBA
 
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

Tom Ogilvy
Speeding Up Macro in VBA
 
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

Bob Phillips
Speeding Up Macro in VBA
 
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" wrote in message
oups.com...
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

VexedFist[_2_]
Speeding Up Macro in VBA
 
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

Tom Ogilvy
Speeding Up Macro in VBA
 
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


All times are GMT +1. The time now is 05:41 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com