AW: Sverweis mit ISTFEHLER als VBA
Franz
Hallo Micha,
der Formellindwurm läßt sich etwas übersichtlicher gestalten, wenn du für den Bereich in dem der SVERWEIS suchen soll einen Namen festlegst.
z.B. Name: Analysen
bezieht sich auf: = [ChemischeAnalysen.xls]chem.Analysen!$B$9:$AW$9980
Die Formel lautet dann, wobei das "=" nur eingefügt wird wenn in den Zellen Werte sind:
=WENN(ISTLEER(A25);"";A25&"=")&WENN(ISTFEHLER(SVERWEIS(A25;Analysen;34;FALSCH));""; (SVERWEIS(A25;Analysen;34;FALSCH)))&" "&WENN(ISTLEER(A27);"";A27&"=")& WENN(ISTFEHLER(SVERWEIS(A27;Analysen;34;FALSCH));"";(SVERWEIS(A27;Analysen;34;FALSCH)))& " "&WENN(ISTLEER(A29);"";A29&"=")&WENN(ISTFEHLER(SVERWEIS(A29;Analysen;34;FALSCH));""; (SVERWEIS(A29;Analysen;34;FALSCH)))
das Makro zum Erzeugen dieser Formel sieht wie folgt aus:
Sub FormelEinfuegen2()
'Formel in Zelle J42 einfügen
Dim WKS As Worksheet
Set WKS = ThisWorkbook.Sheets("Tabelle1") 'Tabelle in der Formel eingefügt werden soll
WKS.Range("J42").FormulaR1C1 = _
"=IF(ISBLANK(R[-17]C[-9]),"""",R[-17]C[-9]&""="")&IF(ISERROR(VLOOKUP(R[-17]C[-9], Analysen,34,FALSE)),"""", (VLOOKUP(R[-17]C[-9],Analysen,34,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-15]C[-9]),"""",R[-15]C[-9]&""="")&IF(ISERROR(VLOOKUP(R[-15]C[-9], Analysen,34,FALSE)),"""", (VLOOKUP(R[-15]C[-9],Analysen,34,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-13]C[-9]),"""",R[-13]C[-9]&""="")&IF(ISERROR(VLOOKUP(R[-13]C[-9], Analysen,34,FALSE)),"""", (VLOOKUP(R[-13]C[-9],Analysen,34,FALSE)))"
End Sub
Wird für den Bereich in dem SVERWEIS sucht kein Name festgelegt, dann wird der Makrotext entsprechend länger:
Sub FormelEinfuegen()
'Formel in Zelle J42 einfügen
Dim WKS As Worksheet
Set WKS = ThisWorkbook.Sheets("Tabelle1") 'Tabelle in der Formel eingefügt werden soll
WKS.Range("J42").FormulaR1C1 = _
"=IF(ISBLANK(R[-17]C[-9]),"""",R[-17]C[-9]&""="")&IF(ISERROR(VLOOKUP(R[-17]C[-9], [ChemischeAnalysen.xls]chem.Analysen!R9C2:R9980C49,34,FALSE)),"""", (VLOOKUP(R[-17]C[-9],[ChemischeAnalysen.xls]chem.Analysen!R9C2:R9980C49,34,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-15]C[-9]),"""",R[-15]C[-9]&""="")&IF(ISERROR(VLOOKUP(R[-15]C[-9], [ChemischeAnalysen.xls]chem.Analysen!R9C2:R9980C49,34,FALSE)),"""", (VLOOKUP(R[-15]C[-9],[ChemischeAnalysen.xls]chem.Analysen!R9C2:R9980C49,34,FALSE))) &"" ""&" & _
"IF(ISBLANK(R[-13]C[-9]),"""",R[-13]C[-9]&""="")&IF(ISERROR(VLOOKUP(R[-13]C[-9], [ChemischeAnalysen.xls]chem.Analysen!R9C2:R9980C49,34,FALSE)),"""", (VLOOKUP(R[-13]C[-9],[ChemischeAnalysen.xls]chem.Analysen!R9C2:R9980C49,34,FALSE)))"
End Sub
Gruß
Franz