AW: VBA Spalteninhalt durchsuchen und ersetzen
06.08.2019 14:12:04
fcs
Hallo Tara,
Makro für Aktualiserung der Preisliste kann etwa wie folgt aussehen.
LG
Franz
Sub Tab1_aktualisieren()
Dim wks_1 As Worksheet, wks_2 As Worksheet
Dim Zei_1 As Long, Zei_2 As Long
Dim varPreis As Variant, varMatNo As Variant, rngMatNo As Range
Set wks_1 = ActiveWorkbook.Worksheets("Tabelle1") 'Zieltabelle - Name ggf. anpassen
Set wks_2 = ActiveWorkbook.Worksheets("Tabelle2") 'Quelltabelle - Name ggf. anpassen
If MsgBox("Preise in Blatt """ & wks_1.Name & """ aktualisieren?", _
vbQuestion + vbOKCancel, "Prisliste aktualisieren") = vbCancel Then Exit Sub
With wks_1
For Zei_1 = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row 'Startzeile der Schleife ggf. _
anpassen
varMatNo = .Cells(Zei_1, 1).Value
With wks_2
Set rngMatNo = .Columns(1).Find(what:=varMatNo, LookIn:=xlValues, _
lookat:=xlWhole) 'Nr. der zu durchsuchenden Spalte ggf anpassen
If rngMatNo Is Nothing Then
wks_1.Cells(Zei_1, 2) = "not found"
wks_1.Cells(Zei_1, 3) = Date
Else
Zei_2 = rngMatNo.Row
varPreis = .Cells(Zei_2, 2).Value 'Nr. der Spalte ggf. anpassen
If varPreis wks_1.Cells(Zei_1, 2).Value Then
wks_1.Cells(Zei_1, 2).Value = varPreis
wks_1.Cells(Zei_1, 3) = Date
End If
End If
End With
Next
End With
End Sub