Vlookup in VBA runs very slow, any ideas?
<p>I have created the following macro to be able to have a sheet called "Macron" which looks inside different cells and sheets in my workbook, from there i want to create a macro that finds the value based on a name instead of a specific cell (since VBA code does not get updated if i add another cell etc, then i need to rewrite all the macro references which is extremely timeconsuming).</p>

<p>So i decided to work with the application.Vlookup function in my code, but now i see that this goes extremely slow compared to only looking inside the cells.</p>

<p>Is this the case all the time, or is it something wrong with my code that could be updated or cleaner to make it work faster.</p>

<p>Here is my code for the macro:</p>

<pre><code>Sub Motesbokning_saljare()
Dim OutApp As Object
Dim OutMail As Object
Dim a As String
Dim o As String
Dim a1 As String
Dim o1 As String
Dim strbody As String
Dim ws As Worksheet
Dim ws1 As Worksheet

' ä
a = Chr(228)
'å
a1 = Chr(229)
'ö
o = Chr(246)
'Ö
o1 = Chr(214)

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(1)
Set ws = Sheets("Macron")
Set ws1 = Sheets("Offert")

On Error Resume Next
With OutMail
    .To = Application.VLookup("kundEpost", ws.Range("A:C").Value, 3, False)
    .Subject = Application.VLookup("partnerNamn", ws.Range("A:C").Value, 3, False) &amp; ", " &amp; Application.VLookup("kundFulltNamn", ws.Range("A:C").Value, 3, False)
    .location = "" &amp; Application.VLookup("kundAdress", ws.Range("A:C").Value, 3, False) &amp; ", " &amp; Application.VLookup("kundPostnr", ws.Range("A:C").Value, 3, False) &amp; ", " &amp; Application.VLookup("kundPostort", ws.Range("A:C").Value, 3, False)
    .Body = "Projekttyp: " &amp; Application.WorksheetFunction.VLookup("moteProjekttyp", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; "Fastighetstyp: " &amp; Application.WorksheetFunction.VLookup("moteFastighetstyp", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; vbNewLine &amp; "Portkod: " &amp; _
    Application.VLookup("motePortkod", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; "Telefon: " &amp; Application.VLookup("kundTelefon", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; "V" &amp; a1 &amp; "ning: " &amp; Application.VLookup("moteVaning", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; vbNewLine _
    &amp; "Upphandlingsunderlag: " &amp; Application.VLookup("moteUpphandlingsunderlag", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; Application.VLookup("moteUpphandlingsunderlagTyp", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; vbNewLine &amp; "K" &amp; o &amp; "rtid: " &amp; Application.VLookup("moteKortid", ws.Range("A:C").Value, 3, False) &amp; " minuter" _
    &amp; vbNewLine &amp; "GPS URL: " &amp; Application.VLookup("moteGPSurl", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; vbNewLine &amp; "K" &amp; a &amp; "lla: " &amp; Application.VLookup("moteKalla", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; o1 &amp; "vrigt: " &amp; Application.VLookup("moteOvriginfo", ws.Range("A:C").Value, 3, False) &amp; vbNewLine &amp; vbNewLine &amp; "Referenskund i n" &amp; a &amp; _
    "romr" &amp; a1 &amp; "de: " &amp; vbNewLine &amp; ws1.Range("I35").Value &amp; ", " &amp; ws1.Range("K35").Value &amp; ", " &amp; ws1.Range("M35").Value &amp; vbNewLine &amp; ws1.Range("I36").Value &amp; ", " &amp; _
    ws1.Range("K36").Value &amp; ", " &amp; ws1.Range("M36").Value &amp; vbNewLine &amp; ws1.Range("I37").Value &amp; ", " &amp; ws1.Range("K37").Value &amp; ", " &amp; ws1.Range("M37").Value &amp; vbNewLine &amp; _
    ws1.Range("I38").Value &amp; ", " &amp; ws1.Range("K38").Value &amp; ", " &amp; ws1.Range("M38").Value &amp; vbNewLine &amp; ws1.Range("I39").Value &amp; ", " &amp; ws1.Range("K39").Value &amp; ", " _
    &amp; ws1.Range("M39").Value
    .Start = Application.VLookup("moteDatum", ws.Range("A:C").Value, 3, False) + Application.VLookup("moteKlockslag", ws.Range("A:C").Value, 3, False)
    .ReminderMinutesBeforeStart = Application.VLookup("moteReminder", ws.Range("A:C").Value, 3, False)
    .Duration = Application.VLookup("moteTidsatgang", ws.Range("A:C").Value, 3, False)
    .Recipients.Add Application.VLookup("moteLaggTillDeltagare", ws.Range("A:C").Value, 3, False)
    .Categories = Application.VLookup("moteKategori", ws.Range("A:C").Value, 3, False)
    .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub
</code></pre>

<p>Thanks for any help that can be offered.</p>

<p>Best regards
Agatonsaxx</p>