anonymous No title
No License VBA
2020年05月29日
Copy Clone
Option Explicit

Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim myData, myData2(), myno
Dim i As Long, j As Long, cn As Long

' If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Then End

With Worksheets("Sheet1")

lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
myData = .Range(.Cells(1, 1), .Cells(lastRow, 6)).Value
End With

ReDim myData2(1 To lastRow, 1 To 6)
ReDim myData2(1 To 6)
For i = LBound(myData) To UBound(myData)

If myData(i, 4) Like "?" & TextBox1.Value & "*" And myData(i, 6) Like "*" & TextBox2.Value & "*" And myData(i, 2) Like "*" & TextBox3.Value & "*" Then
cn = cn + 1

ListBox1.AddItem  '★追加
ListBox1.List(cn - 1, 1) = myData(i, 1) '★追加
ListBox1.List(cn - 1, 2) = myData(i, 2) '★追加
ListBox1.List(cn - 1, 3) = myData(i, 3) '★追加
ListBox1.List(cn - 1, 4) = myData(i, 4) '★追加
End If

Next

With ListBox1
.ColumnCount = 5
.ColumnWidths = "40;80;60;50;170"
'.List = myData2 '★不要
End With

End Sub

Option Explicit

Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim myData, myData2(), myno
Dim i As Long, j As Long, cn As Long

' If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Then End

With Worksheets("Sheet1")

lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
myData = .Range(.Cells(1, 1), .Cells(lastRow, 6)).Value
End With

ReDim myData2(1 To lastRow, 1 To 6)
ReDim myData2(1 To 6)
For i = LBound(myData) To UBound(myData)

If myData(i, 4) Like "?" & TextBox1.Value & "*" And myData(i, 6) Like "*" & TextBox2.Value & "*" And myData(i, 2) Like "*" & TextBox3.Value & "*" Then
cn = cn + 1

ListBox1.AddItem  '★追加
ListBox1.List(cn - 1, 1) = myData(i, 1) '★追加
ListBox1.List(cn - 1, 2) = myData(i, 2) '★追加
ListBox1.List(cn - 1, 3) = myData(i, 3) '★追加
ListBox1.List(cn - 1, 4) = myData(i, 4) '★追加
End If

Next

With ListBox1
.ColumnCount = 5
.ColumnWidths = "40;80;60;50;170"
'.List = myData2 '★不要
End With

End Sub

anonymous
Anonymous
2021年08月29日
変数の設定で、j As longはどこに使用していますか?