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