Mit VBA suchen und Ausgabe

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Private Sub CommandButton2_Click() Dim ws As Worksheet Dim suche As String Dim found As String Dim rng As Range Dim lRow As Long Dim c On Error Resume Next suche = txtArtikelNr.Value Set ws = ThisWorkbook.Sheets("Adressen") lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row Set rng = ws.Range(Cells(1, 1), Cells(lRow, 1)) With rng Set c = .Find(suche) If Not c Is Nothing Then found = ws.Cells(c.Row, 2).Value End With txtArtikel.Value = found End Sub

Private Sub CommandButton2_Click() Dim ws As Worksheet Dim suche As Long Dim found As String Dim rng As Range Dim lRow As Long Dim c On Error Resume Next suche = txtArtikelNr.Value Set ws = ThisWorkbook.Sheets("Adressen") lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lRow, 1)) With rng Set c = .Find(suche, , xlValues) If Not c Is Nothing Then found = ws.Cells(c.Row, 2).Value End With txtArtikel.Value = found End Sub

Private Sub CommandButton2_Click() Dim ws As Worksheet Dim suche As String Dim rng As Range Dim lRow As Long Dim c On Error Resume Next suche = txtArtikelNr.Value Set ws = ThisWorkbook.Sheets("Adressen") lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lRow, 1)) With rng Set c = .Find(suche) If Not c Is Nothing Then txtArtikel.Value = ws.Cells(c.Row, 2).Value txtVorname.Value = ws.Cells(c.Row, 4).Value txtNachname.Value = ws.Cells(c.Row, 3).Value End If End With End Sub

Hallo,
hier ein Code mit zwei Varianten.
1ste: Sucht nur einmal danach
2te: Sucht bis alles gefunden.
Den Code musst du auf dich anpassen.

Option Explicit
Private ws As Worksheet
Private rng As Range
Private c
Private Sub find_once(ByVal suche As String, ByRef found As String)
With rng
Set c = .Find(suche)
found = c.Value
End With
End Sub
Private Sub find_often(ByVal suche As String, ByRef found As String)
Dim firstAddress
Dim arrFound() As Variant
Dim i As Integer
i = 0
With rng
Set c = .Find(suche)
If Not c Is Nothing Then
firstAddress = c.Address
Do
found = found & c.Value & "; "
Set c = .FindNext(c)
If c Is Nothing Then
Exit Do
End If
Loop While Not c Is Nothing And c.Address  firstAddress
End If
End With
End Sub
Private Function last_Row() As Long
last_Row = ws.Cells(Rows.Count, 1).End(xlUp).Row
End Function
Sub Main()
Dim suche As String
Dim found As String
suche = UserForm1.txtArtikelNr.Value
Set ws = ThisWorkbook.Sheets(1)
Set rng = ws.Range(Cells(1, 1), Cells(last_Row, 1))
Call find_once(suche, found)
Debug.Print found
Call find_often(suche, found)
Debug.Print found
End Sub

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

311 Forumthreads zu ähnlichen Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Mit VBA suchen und Ausgabe

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

311 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread