1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
|
Sub tr()
Dim i As Integer
Dim j As Integer
Dim a As String
Dim b As String
Dim c As Integer
Sheets.Add.Name = "Results"
a = Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B16")
j = 1
c = 1
For i = 15 To 1000
If Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i).Font.Bold Then
Application.ThisWorkbook.Worksheets("Results").Range("A" & j) = a
Application.ThisWorkbook.Worksheets("Results").Range("B" & j) = c
Application.ThisWorkbook.Worksheets("Results").Range("C" & j) = Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i)
c = c + 1
j = j + 1
ElseIf Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i) = "" Then
Exit For
End If
Next
For i = 15 To 1000
If Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i).Font.Bold Then
b = Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i)
ElseIf Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i) <> "" Then
Application.ThisWorkbook.Worksheets("Results").Range("A" & j) = a
Application.ThisWorkbook.Worksheets("Results").Range("B" & j) = b
Application.ThisWorkbook.Worksheets("Results").Range("C" & j) = Application.ThisWorkbook.Worksheets("VK_Preisliste").Range("B" & i)
j = j + 1
Else
Exit For
End If
Next
ActiveWorkbook.Worksheets("Results").Activate
ActiveWindow.Zoom = 30
Worksheets("Results").Range("A1:C500").Replace Chr(10), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(13), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(11), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(124), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(0), Chr(32)
Worksheets("Results").Range("A1:C500").Columns.AutoFit
Worksheets("Results").Range("A1:C500").Rows.AutoFit
Worksheets("Results").Range("A1:C500").Replace Chr(10), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(13), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(11), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(124), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(0), Chr(32)
Worksheets("Results").Range("A1:C500").Columns.AutoFit
Worksheets("Results").Range("A1:C500").Rows.AutoFit
Worksheets("Results").Range("A1:C500").Replace Chr(10), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(13), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(11), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(124), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(0), Chr(32)
Worksheets("Results").Range("A1:C500").Columns.AutoFit
Worksheets("Results").Range("A1:C500").Rows.AutoFit
Worksheets("Results").Range("A1:C500").Replace Chr(10), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(13), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(11), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(124), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(0), Chr(32)
Worksheets("Results").Range("A1:C500").Columns.AutoFit
Worksheets("Results").Range("A1:C500").Rows.AutoFit
Worksheets("Results").Range("A1:C500").Replace Chr(10), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(13), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(11), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(124), Chr(32)
Worksheets("Results").Range("A1:C500").Replace Chr(0), Chr(32)
Worksheets("Results").Range("A1:C500").Columns.AutoFit
Worksheets("Results").Range("A1:C500").Rows.AutoFit
End Sub |
Partager