ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro is very slow (https://www.excelbanter.com/excel-discussion-misc-queries/203772-macro-very-slow.html)

jlclyde
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

Per Jessen
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


jlclyde
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


All times are GMT +1. The time now is 08:38 PM.

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