|
leider bin ich neu hier und finde den Code-Button nicht :/
Hier ist nochmals Makro 1, was funktioniert
Sub TranslateCHN()
Dim mainWs As Worksheet
Dim dictionaryWs As Worksheet
Dim mainLastRow As Long
Dim dictionaryLastRow As Long
Dim dictionaryRng As Range
Dim x As Long
Set mainWs = ThisWorkbook.Worksheets("Homologation Scheme")
Set dictionaryWs = ThisWorkbook.Worksheets("Dictionary")
mainLastRow = mainWs.Range("A" & Rows.Count).End(xlUp).Row
dictionaryLastRow = dictionaryWs.Range("A" & Rows.Count).End(xlUp).Row
Set dictionaryRng = dictionaryWs.Range("A2:B" & dictionaryLastRow)
For x = 2 To mainLastRow
On Error Resume Next
mainWs.Range("A" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("A" & x).Value, dictionaryRng, 2, False)
mainWs.Range("B" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("B" & x).Value, dictionaryRng, 2, False)
mainWs.Range("C" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("C" & x).Value, dictionaryRng, 2, False)
mainWs.Range("D" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("D" & x).Value, dictionaryRng, 2, False)
mainWs.Range("E" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("E" & x).Value, dictionaryRng, 2, False)
mainWs.Range("F" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("F" & x).Value, dictionaryRng, 2, False)
Next x
End Sub
und Makro 2, was nicht funktioniert:
Sub TranslateEN()
Dim mainWs As Worksheet
Dim dictionaryWs As Worksheet
Dim mainLastRow As Long
Dim dictionaryLastRow As Long
Dim dictionaryRng As Range
Dim x As Long
Set mainWs = ThisWorkbook.Worksheets("Homologation Scheme")
Set dictionaryWs = ThisWorkbook.Worksheets("Dictionary")
dictionaryLastRow = dictionaryWs.Range("B" & Rows.Count).End(xlUp).Row
Set dictionaryRng = dictionaryWs.Range("A2:B" & dictionaryLastRow)
For x = 2 To mainLastRow
On Error Resume Next
mainWs.Range("A" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("A" & x).Value, dictionaryRng, 1, False)
mainWs.Range("B" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("B" & x).Value, dictionaryRng, 1, False)
mainWs.Range("C" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("C" & x).Value, dictionaryRng, 1, False)
mainWs.Range("D" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("D" & x).Value, dictionaryRng, 1, False)
mainWs.Range("E" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("E" & x).Value, dictionaryRng, 1, False)
mainWs.Range("F" & x).Value = Application.WorksheetFunction.VLookup( _
mainWs.Range("F" & x).Value, dictionaryRng, 1, False)
Next x
End Sub
|