Excel 2003- VBA

Question

Hallo,
vielleicht kann mir jemand in diesem Forum beim Ersetzen einer Formel durch Ihre Werte helfen.

Folgende Problemstellung:

Die Zellen S10 bis Y10 sind fortlaufend nummeriert (also in S10 steht 1,in T10 steht 2,...)
In einer weiteren Zelle (A1) trage ich händisch die Zahlen ein.

Nun soll folgendes per Makro/ VBA passieren:
Wenn die Zelle A1 der Zelle S1 entspricht dann sollen die Formeln der Zellen unter S10 durch ihren Wert ersetzt werden (wobei diese Werte sowohl Zahlen als auch Texte (z.B. "x") sein können)....sollte die Zelle A1 z.B. T10 entsprechen dann sollen die Werte der Spalte T ersetzt werden .....

Ich hoffe, dassmir irgendwer bei meinem Problem behilflich sein kann!

Answer 1

Hallo Ferd,

dazu brauchst du eigentlich kein VBA. Da du das sowieso manuell durch Änderung in A1 auslöst, kannst du stattdessen auch die Spalte markieren --> Kopieren --> Inhalte Einfügen --> Werte machen.

Wenn du durch die Änderung in A1 noch mehr auslöst hier mein Vorschlag:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.Calculate
If Target.Address = [A1].Address Then
lz = ActiveCell.SpecialCells(xlLastCell).Row
Range(Cells(11, [A1] + 18).Address & ":" & Cells(lz, [A1] + 18).Address).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
[A1].Select
End If


End Sub

Answer 2

Im VBA Fenster (Alt + F11) findest du links deine Tabellenblätter. Klicke doppelt auf das gewünschte Blatt und füge den Code dort ein.

Gruß Mr. K.

Answer 3

hi :-)

ihr habt noch einen zirkelbezug im code der mit einer ereignisausschaltung zu beheben waere.mit Application .EnableEvents = False

Ausschaltung !
Application .EnableEvents = False

Einschaltung !
Application .EnableEvents = True


am ende des code wieder auf true setzen
oder bei bedarf die ereignisstellung auslesen und am ende wieder zuruecksetzen

gruss nighty

Answer 4

Hallo Nighty,

vielen Dank für den Tipp.
Mein Excel 2010 hat aber keine Probleme mit dem Code.
Liegts an Application.Calculate? Darauf kann man vielleicht auch verzichten. Ich hatte nur keine Lust nachzuforschen was von beiden zuerst ausgeführt wird. Die Formelberechnung oder das Ereignis.

Gruß Mr. K.

Answer 5

Hi,

auf Select kann man verzichten:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim varWert As Variant
Dim lngLetzte As Long
Dim intSpalte As Integer
If Target.Address = "$A$1" Then
If Not IsError(Application.Match(Target.Value, Range("S10:Y10"), 0)) Then
intSpalte = Application.Match(Target.Value, Range("S10:Y10"), 0) + 18
lngLetzte = IIf(IsEmpty(Cells(Rows.Count, intSpalte)), Cells(Rows.Count, intSpalte).End(xlUp).Row, Rows.Count)
Range(Cells(11, intSpalte), Cells(lngLetzte, intSpalte)).Copy
Cells(11, intSpalte).PasteSpecial Paste:=xlValues
End If
Application.CutCopyMode = False
End If
End Sub

Bis später,
Karin