hier die Variante mit Kopieren, finde ich besser
VB.NET-Quellcode
- Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
- 'deine Liste
- Dim myList As New List(Of String) From {"Ziffer", "#Num_IT", "Produkt A", "Kunde 1", "col2", "col14"}
- Dim FileName As String = "E:\A9.xlsx"
- Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
- Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
- xlWb = xlApp.Workbooks.Open(FileName)
- 'kopiere von Tabelle2
- Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle2"), Worksheet)
- 'nach Tabelle4
- Dim WorkSheet2 As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle4"), Worksheet)
- 'suche in A1 bis P1
- Dim rngSearchValue As Range = xlSt.Range("A1:P1")
- With xlSt
- For i As Integer = 0 To myList.Count - 1
- Dim s As String = myList(i)
- Dim c As Excel.Range
- Dim firstAddress As String = String.Empty
- c = rngSearchValue.Find(s, LookIn:=XlFindLookIn.xlValues, _
- LookAt:=XlLookAt.xlWhole, _
- MatchCase:=True)
- Do
- If Not c Is Nothing Then
- If String.IsNullOrEmpty(firstAddress) Then firstAddress = c.Address
- c = rngSearchValue.FindNext(c)
- If s.Equals("Ziffer") Then
- c.Interior.Color = Color.Tomato
- c.EntireColumn.Copy(WorkSheet2.Range("F1")) '<-reihenfolge in Tabelle4 festlegen, Ziffer als letzte spalte
- ElseIf s.Equals("#Num_IT") Then
- c.Interior.Color = Color.Chartreuse
- c.EntireColumn.Copy(WorkSheet2.Range("B1"))
- ElseIf s.Equals("Produkt A") Then
- c.Interior.Color = Color.IndianRed
- c.EntireColumn.Copy(WorkSheet2.Range("C1"))
- ElseIf s.Equals("Kunde 1") Then
- c.Interior.Color = Color.HotPink
- c.EntireColumn.Copy(WorkSheet2.Range("D1"))
- ElseIf s.Equals("col2") Then
- c.Interior.Color = Color.GreenYellow
- c.EntireColumn.Copy(WorkSheet2.Range("E1"))
- ElseIf s.Equals("col14") Then
- c.Interior.Color = Color.BurlyWood
- c.EntireColumn.Copy(WorkSheet2.Range("A1"))
- End If
- Else
- Exit Do
- End If
- Loop While c.Address <> firstAddress
- Next
- End With
- xlWb.Save()
- xlApp.Quit()
- xlApp = Nothing
- End Sub