list1 zamen nazew na: Data
list2 zamen nazew na: Zmeny
kod:
Sub Zamena()
Dim Spolecnost As String
Dim Zeme As String
Dim Adres As String
Dim TypZmeny As String
Dim Kapital As Long
Dim Data As Worksheet
Dim Zmeny As Worksheet
Dim i As Long
Dim j As Long
Set Data = ThisWorkbook.Sheets("Data")
Set Zmeny = ThisWorkbook.Sheets("Zmeny")
For i = 2 To LastUsedRow(Data, "A")
Spolecnost = Data.Cells(i, "A")
Zeme = Data.Cells(i, "B")
Adres = Data.Cells(i, "C")
Kapital = Data.Cells(i, "D")
For j = 2 To LastUsedRow(Zmeny, "A")
If Zmeny.Cells(j, "C") = Spolecnost Then
'kdyz kod znajde stejne data v slupcu E podmeni data na te ze slupca F
If Zmeny.Cells(j, "E") = Zeme Then Zeme = Zmeny.Cells(j, "F")
If Zmeny.Cells(j, "E") = Adres Then Adres = Zmeny.Cells(j, "F")
If Zmeny.Cells(j, "E") = Kapital Then Kapital = Zmeny.Cells(j, "F")
TypZmeny = Zmeny.Cells(j, "D")
'pokud kod nasel Spolecnost ne musi pokracovat
Exit For
Else:
TypZmeny = "bez zmen"
'nebo
'TypZmeny = ""
End If
Next j
'zapisujemy zmeny
Data.Cells(i, "B") = Zeme
Data.Cells(i, "C") = Adres
Data.Cells(i, "D") = Kapital
Data.Cells(i, "D") = Kapital
Data.Cells(i, "F") = TypZmeny
Next i
Set Data = Nothing
Set Zmeny = Nothing
End Sub
Function LastUsedRow(ws As Worksheet, column As String) As Long
LastUsedRow = ws.Cells(ws.Rows.Count, column).End(xlUp).Row
End Function
citovat