Sverweis ändert sich nach einfügen einer Zeile

02/04/2010 - 17:20 von Daniel Frei | Report spam
Hallo zusammen

Ich habe eine Datei, bei welcher ich diversen VBA Code einsetze (alles ganz
unten). Im Sheet Erfassen habe ich in Spalte D einen Sverweis stehen
[=WENN(B2="";"";SVERWEIS(B2;Tabelle1!$A$2:$C$45;2;0)&"
"&SVERWEIS(B2;Tabelle1!$A$2:$C$45;3;0)))] im Code schiebe ich in der Matrix
(Tabelle1) eine Zeile aus Tabelle4 ein. Nun verànder sich aber mein Sverweis
wie folgt: [=WENN(B2="";"";SVERWEIS(B2;Tabelle1!$A$3:$C$45;2;0)&"
"&SVERWEIS(B2;Tabelle1!$A$3:$C$45;3;0))]. Wie Ihr seht, macht er aus Zelle
A2 die A3. Wie kann ich dies verhindern? Das einschieben der Zeile aus
Tabelle4 ist Notwendig, da es die Überschriften sind, welche im
ErfassenSheet benötigt werden. Oder kann ich diese Zeile im Code auch
deklarieren und dann ohne Probleme in die Matrix einschieben?

Ich hoffe Ihr könnt mein Problem(chen) lösen. Danke schon mal jetzt dafür

Gruss Daniel

*********************************
Nur wer fragt kommt im Leben weiter.
Ein kluger Mann macht nicht alle Fehler selbst. Er gibt auch anderen eine
Chance. --
"Winston Churchill"
_______________________
Win 7
Office 2007 Enterprise


Hier der komplette Code: [ist noch nicht alles fertig, aber ich arbeite noch
dran :-) ]

Private Sub Workbook_Open()

Application.ScreenUpdating = False 'hier wird der VBA Code in den
Hintergrund gestellt!
' hier wird die Datei geladen
'********************************
FileCopy "E:\1 yx\Kunden\Diverses\Mitgliederdateien2.csv", "E:\1
xy\Kunden\Diverses\Mitgliederdateien2copie.csv" ' PFAD ANPASSEN!!

'********************************
Dim sDatei As String
'********************************
sDatei = "E:\1 xy\Kunden\Diverses\Mitgliederdateien2.txt" 'PFAD
ANPASSEN!!
'********************************
If Dir(sDatei) <> "" Then ' wenn die Datei bereits besteht wird sie hier
gelöscht
Kill sDatei
End If

'********************************
Name "E:\1 xy\Kunden\Diverses\Mitgliederdateien2copie.csv" As "E:\1
xy\Kunden\Diverses\Mitgliederdateien2.txt" 'PFAD ANPASSEN!!
'Ende des laden der Datei
'********************************

' Laden der Textdatei und Kopieren in Tabelle 1
'**************************33

'PFAD ANPASSEN!!
Workbooks.OpenText Filename:="E:\1
xy\Kunden\Diverses\Mitgliederdateien2.txt", _
DataType:=xlDelimited, Origin:=xlWindows, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False

'*****************************************
Range("A1:W32").Select ' ACHTUNG HIER DEN RANGE ANPASSEN !!!!
Selection.Copy '*****************************************
Windows("Kunden zzz def0.xls").Activate ' ACHTUNG DATEINAME ANPASSEN!!
Sheets("Tabelle1").Select
'*******************************
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Sheets("Tabelle4").Activate 'hier wird die Überschriftspalte eingefügt
Rows("1:1").Select
Selection.Copy
Sheets("Tabelle1").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

'Ende

Workbooks("Mitgliederdateien2.txt").Close SaveChanges:=False ' Hier wird
die Textdatei wieder geschlossen
Kill sDatei


' Kontrollieren ob das Datum den 27. erreicht hat
Sheets("Tabelle4").Activate
Dim Name_A1 As String ' Variable definieren
[K2] = Format([I2], "dd")
Name_A1 = Range("K2").Value 'Variable einlesen
If [K2] = 27 Then ' wenn ja dann wird das Sheet "Erfassen" mit dem Monat
versehen und ans ende gestellt
MsgBox "Datum ist 27" ' MsgBox wenns làuft ausklammern
End If

Sheets("Erfassen").Activate
Cells(2, 1).Select

'# Hier gehts weiter wenn das Sheet "Erfassen" kopiert und ans ende
gestellt werden soll!
' Sheets("Erfassen").Select
' ActiveSheet.Buttons.Add(540, 25.5, 69.75, 38.25).Select
' ActiveSheet.Buttons.Add(540, 98.25, 81.75, 39.75).Select
' Sheets("Erfassen").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
' Dim Name_A1 As String ' Variable definieren
' [J1] = Format([I1], "mmmm")
' Name_A1 = Range("J1").Value 'Variable einlesen
' ActiveSheet.Name = "" & Name_A1 & "" 'Name auf Tabelle übernehmen

'Hier werden alle Buttons gelöscht
' Dim shp As Shape
' For Each shp In ActiveSheet.Shapes
' shp.Delete
' Next

Application.ScreenUpdating = True ' Hier wird der VBA Code wieder in den
Vordergrund gerückt!


End Sub
 

Lesen sie die antworten

#1 Opa
02/04/2010 - 19:58 | Warnen spam
Ein tipp, in welche Richtung ich es versuchen würde: der zelle $a$2 einen
Namen (z.B. platz) geben und diesen in die Formel einfügen. Wenn der VBA Code
die zeile oberhalb platz einschiebt, wird die Adressse von platz tatsàchlich
geàndert, kann aber vermutlich im VBA Code wieder korrigiert werden.


"Daniel Frei" wrote:

Hallo zusammen

Ich habe eine Datei, bei welcher ich diversen VBA Code einsetze (alles ganz
unten). Im Sheet Erfassen habe ich in Spalte D einen Sverweis stehen
[=WENN(B2="";"";SVERWEIS(B2;Tabelle1!$A$2:$C$45;2;0)&"
"&SVERWEIS(B2;Tabelle1!$A$2:$C$45;3;0)))] im Code schiebe ich in der Matrix
(Tabelle1) eine Zeile aus Tabelle4 ein. Nun verànder sich aber mein Sverweis
wie folgt: [=WENN(B2="";"";SVERWEIS(B2;Tabelle1!$A$3:$C$45;2;0)&"
"&SVERWEIS(B2;Tabelle1!$A$3:$C$45;3;0))]. Wie Ihr seht, macht er aus Zelle
A2 die A3. Wie kann ich dies verhindern? Das einschieben der Zeile aus
Tabelle4 ist Notwendig, da es die Überschriften sind, welche im
ErfassenSheet benötigt werden. Oder kann ich diese Zeile im Code auch
deklarieren und dann ohne Probleme in die Matrix einschieben?

Ich hoffe Ihr könnt mein Problem(chen) lösen. Danke schon mal jetzt dafür

Gruss Daniel

*********************************
Nur wer fragt kommt im Leben weiter.
Ein kluger Mann macht nicht alle Fehler selbst. Er gibt auch anderen eine
Chance. --
"Winston Churchill"
_______________________
Win 7
Office 2007 Enterprise


Hier der komplette Code: [ist noch nicht alles fertig, aber ich arbeite noch
dran :-) ]

Private Sub Workbook_Open()

Application.ScreenUpdating = False 'hier wird der VBA Code in den
Hintergrund gestellt!
' hier wird die Datei geladen
'********************************
FileCopy "E:\1 yx\Kunden\Diverses\Mitgliederdateien2.csv", "E:\1
xy\Kunden\Diverses\Mitgliederdateien2copie.csv" ' PFAD ANPASSEN!!

'********************************
Dim sDatei As String
'********************************
sDatei = "E:\1 xy\Kunden\Diverses\Mitgliederdateien2.txt" 'PFAD
ANPASSEN!!
'********************************
If Dir(sDatei) <> "" Then ' wenn die Datei bereits besteht wird sie hier
gelöscht
Kill sDatei
End If

'********************************
Name "E:\1 xy\Kunden\Diverses\Mitgliederdateien2copie.csv" As "E:\1
xy\Kunden\Diverses\Mitgliederdateien2.txt" 'PFAD ANPASSEN!!
'Ende des laden der Datei
'********************************

' Laden der Textdatei und Kopieren in Tabelle 1
'**************************33

'PFAD ANPASSEN!!
Workbooks.OpenText Filename:="E:\1
xy\Kunden\Diverses\Mitgliederdateien2.txt", _
DataType:=xlDelimited, Origin:=xlWindows, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False

'*****************************************
Range("A1:W32").Select ' ACHTUNG HIER DEN RANGE ANPASSEN !!!!
Selection.Copy '*****************************************
Windows("Kunden zzz def0.xls").Activate ' ACHTUNG DATEINAME ANPASSEN!!
Sheets("Tabelle1").Select
'*******************************
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False


Sheets("Tabelle4").Activate 'hier wird die Überschriftspalte eingefügt
Rows("1:1").Select
Selection.Copy
Sheets("Tabelle1").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

'Ende

Workbooks("Mitgliederdateien2.txt").Close SaveChanges:=False ' Hier wird
die Textdatei wieder geschlossen
Kill sDatei


' Kontrollieren ob das Datum den 27. erreicht hat
Sheets("Tabelle4").Activate
Dim Name_A1 As String ' Variable definieren
[K2] = Format([I2], "dd")
Name_A1 = Range("K2").Value 'Variable einlesen
If [K2] = 27 Then ' wenn ja dann wird das Sheet "Erfassen" mit dem Monat
versehen und ans ende gestellt
MsgBox "Datum ist 27" ' MsgBox wenns làuft ausklammern
End If

Sheets("Erfassen").Activate
Cells(2, 1).Select

'# Hier gehts weiter wenn das Sheet "Erfassen" kopiert und ans ende
gestellt werden soll!
' Sheets("Erfassen").Select
' ActiveSheet.Buttons.Add(540, 25.5, 69.75, 38.25).Select
' ActiveSheet.Buttons.Add(540, 98.25, 81.75, 39.75).Select
' Sheets("Erfassen").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
' Dim Name_A1 As String ' Variable definieren
' [J1] = Format([I1], "mmmm")
' Name_A1 = Range("J1").Value 'Variable einlesen
' ActiveSheet.Name = "" & Name_A1 & "" 'Name auf Tabelle übernehmen

'Hier werden alle Buttons gelöscht
' Dim shp As Shape
' For Each shp In ActiveSheet.Shapes
' shp.Delete
' Next

Application.ScreenUpdating = True ' Hier wird der VBA Code wieder in den
Vordergrund gerückt!


End Sub


.

Ähnliche fragen