![]() |
�������ʂ����X�g�{�b�N�X�ɕ\������:Excel VBA���� |
�X�|���T�[�h�����N | |
�X�V:2025/3/16;�쐬:2015/6/23
Sub test() �@�@UserForm1.show End Sub |
'���������s���܂��B������v�������s���Ă��܂��B 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 = "" Then End �@�@'��������f�[�^��z��@myData�@�Ɋi�[���Ă��܂��B �@�@With Worksheets("Sheet1") �@�@�@�@�@'---�@2018/9/27�C�����܂��� �@�@�@�@ lastRow = .Cells(Rows.Count, 1).End(xlUp).Row �@�@�@�@myData = .Range(.Cells(1, 1), .Cells(lastRow, 6)).Value �@�@End With �@�@'�z��@myData �̒��Ō����ň�v�����f�[�^��z��@myData2�@�Ɋi�[���Ă��܂��B �@�@ReDim myData2(1 To lastRow, 1 To 3) �@�@For i = LBound(myData) To UBound(myData) �@�@�@�@If myData(i, 2) Like "*" & TextBox1.Value & "*" And myData(i, 6) Like "*" & TextBox2.Value & "*" Then �@�@�@�@�@�@cn = cn + 1 �@�@�@�@�@�@myData2(cn, 1) = myData(i, 1) �@�@�@�@�@�@myData2(cn, 2) = myData(i, 2) �@�@�@�@�@�@myData2(cn, 3) = myData(i, 6) �@�@�@�@End If �@�@Next i �@�@'�����ň�v�����f�[�^�����X�g�{�b�N�X�ɕ\�����܂��B �@�@With ListBox1 �@�@�@�@.ColumnCount = 3 �@�@�@�@.ColumnWidths = "30;70;70" �@�@�@�@.List = myData2 �@�@End With End Sub '---------------------------------------------- '���X�g�{�b�N�X���̃f�[�^���_�u���N���b�N����ƃV�[�g�̃f�[�^��I�����܂��B Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) �@�@With Worksheets("Sheet1") �@�@�@�@.Range(.Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 1), .Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 13)).Select �@�@End With End Sub '---------------------------------------------- '���[�U�[�t�H�[���̏����ݒ�F���X�g�̑S�f�[�^��\�����Ă��܂��B Private Sub UserForm_Initialize() �@�@Dim lastRow As Long �@�@Dim myData, myData2() �@�@Dim i As Long, j As Long �@�@With Worksheets("Sheet1") �@�@�@�@�@'---�@2018/9/27�C�����܂��� �@�@�@�@ 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 3) �@�@For i = LBound(myData) To UBound(myData) �@�@�@�@myData2(i, 1) = myData(i, 1) �@�@�@�@myData2(i, 2) = myData(i, 2) �@�@�@�@myData2(i, 3) = myData(i, 6) �@�@Next i �@�@With ListBox1 �@�@�@�@.ColumnCount = 3 �@�@�@�@.ColumnWidths = "30;70;70" �@�@�@�@.List = myData2 �@�@End With End Sub |
'���������s���܂��B������v�������s���Ă��܂��B Private Sub CommandButton1_Click() �@�@Dim lastRow As Long �@�@Dim myData, myData2(), myno �@�@Dim i As Long, j As Long, cn As Long �@�@Dim myBook As Workbook �@�@Set myBook = Workbooks("kensaku.xlsm") �@�@'��������f�[�^��z��@myData�@�Ɋi�[���Ă��܂��B �@�@�@�@With myBook.Worksheets("meibo") �@�@�@�@�@'---�@2018/9/27�C�����܂��� �@�@�@�@ lastRow = .Cells(Rows.Count, 1).End(xlUp).Row �@�@�@�@myData = .Range(.Cells(1, 1), .Cells(lastRow, 6)).Value �@�@End With �@�@'�z��@myData �̒��Ō����ň�v�����f�[�^��z��@myData2�@�Ɋi�[���Ă��܂��B �@�@ReDim myData2(1 To lastRow, 1 To 3) �@�@For i = LBound(myData) To UBound(myData) �@�@�@�@If myData(i, 2) Like "*" & TextBox1.Value & "*" And myData(i, 6) Like "*" & TextBox2.Value & "*" Then �@�@�@�@�@�@cn = cn + 1 �@�@�@�@�@�@myData2(cn, 1) = myData(i, 1) �@�@�@�@�@�@myData2(cn, 2) = myData(i, 2) �@�@�@�@�@�@myData2(cn, 3) = myData(i, 6) �@�@�@�@End If �@�@Next i �@�@'�����ň�v�����f�[�^�����X�g�{�b�N�X�ɕ\�����܂��B �@�@With ListBox1 �@�@�@�@.ColumnCount = 3 �@�@�@�@.ColumnWidths = "30;70;70" �@�@�@�@.List = myData2 �@�@End With End Sub '---------------------------------------------- '���X�g�{�b�N�X���̃f�[�^���_�u���N���b�N����ƃV�[�g�̃f�[�^��I�����܂��B Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) �@�@ Dim myBook As Workbook �@�@Set myBook = Workbooks("kensaku.xlsm") �@�@With myBook.Worksheets("meibo") �@�@�@�@.Activate�@'�ʃt�@�C���̃V�[�g���A�N�e�B�u�ɂ��܂��B �@�@�@�@.Range(.Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 1), .Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 13)).Select �@�@End With End Sub '---------------------------------------------- '���[�U�[�t�H�[���̏����ݒ�F���X�g�̑S�f�[�^��\�����Ă��܂��B Private Sub UserForm_Initialize() �@�@Dim lastRow As Long �@�@Dim myData, myData2() �@�@Dim i As Long, j As Long �@�@Dim myBook As Workbook �@�@Set myBook = Workbooks.Open("c:\users\yone4\Documents\HP�p�f�[�^�t�@�C��\HP_2010\kensaku.xlsm") �f---���炩���߃t�@�C�����J���Ă���ꍇ�́����g���B�t�@�C�����J���ꍇ�͏�̍s�����g�� �@�@' Set myBook = Workbooks("kensaku.xlsm")�@ �@�@With myBook.Worksheets("meibo") �@�@�@�@�@�@'---�@2018/9/27�C�����܂��� �@�@�@�@ 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 3) �@�@For i = LBound(myData) To UBound(myData) �@�@�@�@myData2(i, 1) = myData(i, 1) �@�@�@�@myData2(i, 2) = myData(i, 2) �@�@�@�@myData2(i, 3) = myData(i, 6) �@�@Next i �@�@With ListBox1 �@�@�@�@.ColumnCount = 3 �@�@�@�@.ColumnWidths = "30;70;70" �@�@�@�@.List = myData2 �@�@End With End Sub |
Private Sub CommandButton1_Click() �@�@Dim lastRow As Long �@�@Dim myData, myData2(), myno �@�@Dim i As Long, j As Long, cn As Long �@�@Dim key1 As String, key2 As String, key3 As String, key4 As String �@�@If TextBox1.Value = "" Then key1 = "*" Else key1 = "*" & TextBox1.Value & "*" �@�@If TextBox2.Value = "" Then key2 = "*" Else key2 = "*" & TextBox2.Value & "*" �@�@If TextBox3.Value = "" Then key3 = "*" Else key3 = "*" & TextBox3.Value & "*" �@�@Dim ListNo As Long �@�@�@ListNo = ComboBox1.ListIndex �@�@�@�@�@If ListNo < 0 Then �@�@�@�@�@�@�@key4 = "*" �@�@�@�@�@Else �@�@�@�@�@�@�@key4 = ComboBox1.List(ListNo) �@�@�@�@�@End If �@�@With Worksheets("Sheet1") �@�@�@�@�@'---�@2018/9/27�C�����܂��� �@�@�@�@ 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 3) �@�@For i = LBound(myData) To UBound(myData) �@�@�@If myData(i, 2) Like key1 And myData(i, 6) Like key2 And myData(i, 4) Like key3 And myData(i, 5) Like key4 Then �@�@�@�@cn = cn + 1 �@�@�@�@myData2(cn, 1) = myData(i, 1) �@�@�@�@myData2(cn, 2) = myData(i, 2) �@�@�@�@myData2(cn, 3) = myData(i, 6) �@�@�@End If �@�@Next i �@�@With ListBox1 �@�@�@�@.ColumnCount = 3 �@�@�@�@.ColumnWidths = "30;70;70" �@�@�@�@.List = myData2 �@�@End With End Sub �f------ Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) �@�@With Worksheets("Sheet1") �@�@.Range(.Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 1), .Cells(ListBox1.List(ListBox1.ListIndex, 0) + 1, 13)).Select �@�@End With End Sub '-------- Private Sub UserForm_Initialize() �@�@Dim lastRow As Long �@�@Dim myData, myData2() �@�@Dim i As Long, j As Long �@�@With Worksheets("Sheet1") �@�@�@�@�@'---�@2018/9/27�C�����܂��� �@�@�@�@ 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 3) �@�@For i = LBound(myData) To UBound(myData) �@�@�@�@myData2(i, 1) = myData(i, 1) �@�@�@�@myData2(i, 2) = myData(i, 2) �@�@�@�@myData2(i, 3) = myData(i, 6) �@�@Next i �@�@With ListBox1 �@�@�@�@.ColumnCount = 3 �@�@�@�@.ColumnWidths = "30;70;70" �@�@�@�@.List = myData2 �@�@End With �@�@With ComboBox1 �@�@�@�@.RowSource = "Sheet1!J2:J48" �@�@End With End Sub '--------- Private Sub CommandButton2_Click() �@�@UserForm_Initialize End Sub |
�X�|���T�[�h�����N
PageViewCounter
�@
�@Since2006/2/27