| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro is very slow
I am trying to make a macro that will fill in this formula and then
remove the formula in these cells. It is a Vlookup formula that is taken apart so I can change the columns to lookup. the lookup values are coming from another sheet. Do go through each cell and select and put in the formula takes forever. can anyone help me make this faster? Thanks, Jay Sub LSStuff() Sheet1.Range("L4").Select Dim SelRow, LastRow As Long Dim String1, String2, String3 As String String1 = "=VLOOKUP(L" String2 = ",'G:\FI2_Share\Purchasing\Large sheet Lists\[LS BOOK.xls]Large Sheets'!$B$10:$M$1066," String3 = ",FALSE)" LastRow = Range("B65536").End(xlUp).Row Do Until Selection.Row = LastRow SelRow = Selection.Row If Selection.Value = "" Then Selection.Offset(1, 0).Select Else Selection.Offset(0, -2).Value = String1 & SelRow & String2 & 2 & String3 Selection.Offset(0, -2).Value = Selection.Offset(0, -2).Value Selection.Offset(0, -1).Value = String1 & SelRow & String2 & 3 & String3 Selection.Offset(0, -1).Value = Selection.Offset(0, -1).Value Selection.Offset(0, 1).Value = String1 & SelRow & String2 & 9 & String3 Selection.Offset(0, 1).Value = Selection.Offset(0, 1).Value Selection.Offset(0, 2).Value = String1 & SelRow & String2 & 10 & String3 Selection.Offset(0, 2).Value = Selection.Offset(0, 2).Value Selection.Offset(0, 3).Value = String1 & SelRow & String2 & 11 & String3 Selection.Offset(0, 3).Value = Selection.Offset(0, 3).Value Selection.Offset(0, 4).Value = String1 & SelRow & String2 & 12 & String3 Selection.Offset(0, 4).Value = Selection.Offset(0, 4).Value Selection.Offset(1, 0).Select End If Loop End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro is very slow
Hi
First you have to declare each variable seperatly. See my code Dim String1, String2, String3 As String The above line will declare String3 as String but String 1 and String2 as variant. Second don't select cells as it will slow down the macro just refer to the cells. Third using Application.Screenupdating=False will also speed things up. Just remember to turn it on again. Try this: Sub LSStuff() Application.ScreenUpdating = False Dim SelRow As Long Dim LastRow As Long Dim String1 As String, String2 As String, String3 As String Dim TargetCell As Range Sheet1.Activate Set TargetCell = Range("L4") String1 = "=VLOOKUP(L" String2 = ",'G:\FI2_Share\Purchasing\Large sheet Lists\[LSBOOK.xls]Large Sheets'!$B$10:$M$1066," String3 = ",FALSE)" LastRow = Range("B65536").End(xlUp).Row Do Until TargetCell.Row = LastRow SelRow = TargetCell.Row If TargetCell.Value = "" Then Set TargetCell = TargetCell.Offset(1, 0) Else TargetCell.Offset(0, -2).Value = String1 & SelRow & String2 & 2 & String3 TargetCell.Offset(0, -2).Value = TargetCell.Offset(0, -2).Value TargetCell.Offset(0, -1).Value = String1 & SelRow & String2 & 3 & String3 TargetCell.Offset(0, -1).Value = TargetCell.Offset(0, -1).Value TargetCell.Offset(0, 1).Value = String1 & SelRow & String2 & 9 & String3 TargetCell.Offset(0, 1).Value = TargetCell.Offset(0, 1).Value TargetCell.Offset(0, 2).Value = String1 & SelRow & String2 & 10 & String3 TargetCell.Offset(0, 2).Value = TargetCell.Offset(0, 2).Value TargetCell.Offset(0, 3).Value = String1 & SelRow & String2 & 11 & String3 TargetCell.Offset(0, 3).Value = TargetCell.Offset(0, 3).Value TargetCell.Offset(0, 4).Value = String1 & SelRow & String2 & 12 & String3 TargetCell.Offset(0, 4).Value = TargetCell.Offset(0, 4).Value Set TargetCell = TargetCell.Offset(1, 0) End If Loop Application.ScreenUpdating = True End Sub HTH Per "jlclyde" skrev i meddelelsen ... I am trying to make a macro that will fill in this formula and then remove the formula in these cells. It is a Vlookup formula that is taken apart so I can change the columns to lookup. the lookup values are coming from another sheet. Do go through each cell and select and put in the formula takes forever. can anyone help me make this faster? Thanks, Jay Sub LSStuff() Sheet1.Range("L4").Select Dim SelRow, LastRow As Long Dim String1, String2, String3 As String String1 = "=VLOOKUP(L" String2 = ",'G:\FI2_Share\Purchasing\Large sheet Lists\[LS BOOK.xls]Large Sheets'!$B$10:$M$1066," String3 = ",FALSE)" LastRow = Range("B65536").End(xlUp).Row Do Until Selection.Row = LastRow SelRow = Selection.Row If Selection.Value = "" Then Selection.Offset(1, 0).Select Else Selection.Offset(0, -2).Value = String1 & SelRow & String2 & 2 & String3 Selection.Offset(0, -2).Value = Selection.Offset(0, -2).Value Selection.Offset(0, -1).Value = String1 & SelRow & String2 & 3 & String3 Selection.Offset(0, -1).Value = Selection.Offset(0, -1).Value Selection.Offset(0, 1).Value = String1 & SelRow & String2 & 9 & String3 Selection.Offset(0, 1).Value = Selection.Offset(0, 1).Value Selection.Offset(0, 2).Value = String1 & SelRow & String2 & 10 & String3 Selection.Offset(0, 2).Value = Selection.Offset(0, 2).Value Selection.Offset(0, 3).Value = String1 & SelRow & String2 & 11 & String3 Selection.Offset(0, 3).Value = Selection.Offset(0, 3).Value Selection.Offset(0, 4).Value = String1 & SelRow & String2 & 12 & String3 Selection.Offset(0, 4).Value = Selection.Offset(0, 4).Value Selection.Offset(1, 0).Select End If Loop End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro is very slow
On Sep 24, 1:03*pm, "Per Jessen" wrote:
Hi First you have to declare each variable seperatly. See my code Dim String1, String2, String3 As String The above line will declare String3 as String but String 1 and String2 as variant. Second don't select cells as it will slow down the macro just refer to the cells. Third using Application.Screenupdating=False will also speed things up. Just remember to turn it on again. Try this: Sub LSStuff() * * Application.ScreenUpdating = False * * Dim SelRow As Long * * Dim LastRow As Long * * Dim String1 As String, String2 As String, String3 As String * * Dim TargetCell As Range * * Sheet1.Activate * * Set TargetCell = Range("L4") * * String1 = "=VLOOKUP(L" * * String2 = ",'G:\FI2_Share\Purchasing\Large sheet Lists\[LSBOOK.xls]Large Sheets'!$B$10:$M$1066," * * String3 = ",FALSE)" * * LastRow = Range("B65536").End(xlUp).Row * * Do Until TargetCell.Row = LastRow * * * * SelRow = TargetCell.Row * * * * If TargetCell.Value = "" Then * * * * * * Set TargetCell = TargetCell.Offset(1, 0) * * * * Else * * * * * * TargetCell.Offset(0, -2).Value = String1 & SelRow & String2 & 2 & String3 * * * * * * TargetCell.Offset(0, -2).Value = TargetCell.Offset(0, -2).Value * * * * * * TargetCell.Offset(0, -1).Value = String1 & SelRow & String2 & 3 & String3 * * * * * * TargetCell.Offset(0, -1).Value = TargetCell.Offset(0, -1).Value * * * * * * TargetCell.Offset(0, 1).Value = String1 & SelRow & String2 & 9 & String3 * * * * * * TargetCell.Offset(0, 1).Value = TargetCell.Offset(0, 1).Value * * * * * * TargetCell.Offset(0, 2).Value = String1 & SelRow & String2 & 10 & String3 * * * * * * TargetCell.Offset(0, 2).Value = TargetCell.Offset(0, 2).Value * * * * * * TargetCell.Offset(0, 3).Value = String1 & SelRow & String2 & 11 & String3 * * * * * * TargetCell.Offset(0, 3).Value = TargetCell.Offset(0, 3).Value * * * * * * TargetCell.Offset(0, 4).Value = String1 & SelRow & String2 & 12 & String3 * * * * * * TargetCell.Offset(0, 4).Value = TargetCell.Offset(0, 4).Value * * * * * * Set TargetCell = TargetCell.Offset(1, 0) * * * * End If * * Loop * * Application.ScreenUpdating = True End Sub HTH Per "jlclyde" skrev i ... I am trying to make a macro that will fill in this formula and then remove the formula in these cells. *It is a Vlookup formula that is taken apart so I can change the columns to lookup. *the lookup values are coming from another sheet. *Do go through each cell and select and put in the formula takes forever. *can anyone help me make this faster? Thanks, Jay Sub LSStuff() * *Sheet1.Range("L4").Select * *Dim SelRow, LastRow As Long * *Dim String1, String2, String3 As String * *String1 = "=VLOOKUP(L" * *String2 = ",'G:\FI2_Share\Purchasing\Large sheet Lists\[LS BOOK.xls]Large Sheets'!$B$10:$M$1066," * *String3 = ",FALSE)" * *LastRow = Range("B65536").End(xlUp).Row * *Do Until Selection.Row = LastRow * * * *SelRow = Selection.Row * * * *If Selection.Value = "" Then * * * * * *Selection.Offset(1, 0).Select * * * *Else * * * * * *Selection.Offset(0, -2).Value = String1 & SelRow & String2 & 2 & String3 * * * * * *Selection.Offset(0, -2).Value = Selection.Offset(0, -2).Value * * * * * *Selection.Offset(0, -1).Value = String1 & SelRow & String2 & 3 & String3 * * * * * *Selection.Offset(0, -1).Value = Selection.Offset(0, -1).Value * * * * * *Selection.Offset(0, 1).Value = String1 & SelRow & String2 & 9 & String3 * * * * * *Selection.Offset(0, 1).Value = Selection.Offset(0, 1).Value * * * * * *Selection.Offset(0, 2).Value = String1 & SelRow & String2 & 10 & String3 * * * * * *Selection.Offset(0, 2).Value = Selection.Offset(0, 2).Value * * * * * *Selection.Offset(0, 3).Value = String1 & SelRow & String2 & 11 & String3 * * * * * *Selection.Offset(0, 3).Value = Selection.Offset(0, 3).Value * * * * * *Selection.Offset(0, 4).Value = String1 & SelRow & String2 & 12 & String3 * * * * * *Selection.Offset(0, 4).Value = Selection.Offset(0, 4).Value * * * * * *Selection.Offset(1, 0).Select * * * *End If * *Loop End Sub- Hide quoted text - - Show quoted text - I never knew about targetcell. I am going to use this often. Thanks, Jay |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Running Painfully Slow! | Excel Discussion (Misc queries) | |||
| Macro is amazingly SLOW...Need help | Excel Discussion (Misc queries) | |||
| Excel 2007 VBA-XLL macro speed slow | Excel Discussion (Misc queries) | |||
| Why is this PageSetup Macro So Slow? | Excel Discussion (Misc queries) | |||
| Delay/Slow a Macro In Excel 2003 | Excel Worksheet Functions | |||
Linear Mode
