VBA Code optimieren
11.04.2006 08:37:36
MichaK
ich habe folgenden VBA Code in Anlehnung an die Hilfe von Franz S https://www.herber.de/forum/messages/753887.html (dort gleiches Problem - aber kürzere Abfrage)geschrieben. Leider bekomme ich die Fehlermeldung "Nicht genügend Speicher" Laufzeitfehler 7. Wie kann ich den nachstehenden Code optimieren?
Private Sub CommandButton4_Click()
Dim WKS As Worksheet
Ana = "[ChemischeAnalysen.xls]chem.Analysen!R2C2:R9980C49"
Set WKS = ThisWorkbook.Sheets("A5QT") 'Tabelle in der Formel eingefügt werden soll
WKS.Range("R42").FormulaR1C1 = _
"=IF(ISBLANK(R[-17]C[-17]),"""",R[-17]C[-17]&"" A="")&IF(ISERROR(VLOOKUP(R[-17]C[-17],Ana,42,FALSE)),"""", (VLOOKUP(R[-17]C[-17],Ana,42,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-17]C[-17]),"""","" B="")&IF(ISERROR(VLOOKUP(R[-17]C[-17],Ana,44,FALSE)),"""", (VLOOKUP(R[-17]C[-17],Ana,44,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-17]C[-17]),"""","" C="")&IF(ISERROR(VLOOKUP(R[-17]C[-17],Ana,46,FALSE)),"""", (VLOOKUP(R[-17]C[-17],Ana,46,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-17]C[-17]),"""","" D="")&IF(ISERROR(VLOOKUP(R[-17]C[-17],Ana,48,FALSE)),"""", (VLOOKUP(R[-17]C[-17],Ana,48,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-15]C[-17]),"""",R[-15]C[-17]&"" A="")&IF(ISERROR(VLOOKUP(R[-15]C[-17],Ana,42,FALSE)),"""", (VLOOKUP(R[-15]C[-17],Ana,42,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-15]C[-17]),"""","" B="")&IF(ISERROR(VLOOKUP(R[-15]C[-17],Ana,44,FALSE)),"""", (VLOOKUP(R[-15]C[-17],Ana,44,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-15]C[-17]),"""","" C="")&IF(ISERROR(VLOOKUP(R[-15]C[-17],Ana,46,FALSE)),"""", (VLOOKUP(R[-15]C[-17],Ana,46,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-15]C[-17]),"""","" D="")&IF(ISERROR(VLOOKUP(R[-15]C[-17],Ana,48,FALSE)),"""", (VLOOKUP(R[-15]C[-17],Ana,48,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-13]C[-17]),"""",R[-13]C[-17]&"" A="")&IF(ISERROR(VLOOKUP(R[-13]C[-17],Ana,42,FALSE)),"""", (VLOOKUP(R[-13]C[-17],Ana,42,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-13]C[-17]),"""","" B="")&IF(ISERROR(VLOOKUP(R[-13]C[-17],Ana,44,FALSE)),"""", (VLOOKUP(R[-13]C[-17],Ana,44,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-13]C[-17]),"""","" C="")&IF(ISERROR(VLOOKUP(R[-13]C[-17],Ana,46,FALSE)),"""", (VLOOKUP(R[-13]C[-17],Ana,46,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-13]C[-17]),"""","" D="")&IF(ISERROR(VLOOKUP(R[-13]C[-17],Ana,48,FALSE)),"""", (VLOOKUP(R[-13]C[-17],Ana,48,FALSE)))"
End Sub
Danke für die Hilfe
LG Micha