Macro is very slow

J

jlclyde

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
 
P

Per Jessen

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

jlclyde

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" <[email protected]> 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
Click to expand...

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
Click to expand...
   Do Until Selection.Row = LastRow
       SelRow = Selection.Row
Click to expand...
       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 -
Click to expand...

- Show quoted text -
Click to expand...

I never knew about targetcell. I am going to use this often.
Thanks,
Jay
 

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

Can you speed up this code? 2
wrong font 4
Dynamic chart problem 1
Macro for Multiple Worksheets 6
User-Defined Data TYPE 3
cells.find & stopping the search 5
Excel keeps shutting down using code 2
Filter and Print Problem 1

Top