Posts by lala_milo

lala_milo · Mar 9th 2017

Re: Search result not display correctly in ViewList

Please close this. Issue resolved

lala_milo · Mar 8th 2017

Hi, I am new to the vba. I have amend the userform for search and it not working correctly. I need it to show the information from A:H but the outcome just display Row number. Any idea how to fix it?

Code

[ATTACH]71668[/ATTACH]Private Sub CommandButton1_Click()
 'SEARCH
 
 Dim Cnt As Long
 Dim Col As Variant
 Dim FirstAddx As String
 Dim FoundMatch As Range
 Dim LastRow As Long
 Dim R As Long
 Dim StartRow As Long
 Dim Wks As Worksheet
 
     StartRow = 2
     Set Wks = Worksheets("Data")
     
       Col = ComboBox1.ListIndex + 1
         If Col = 0 Then
            MsgBox "Please choose a category."
            Exit Sub
         End If
        
       If TextBox1.Text = "" Then
          MsgBox "Please enter a search term."
          TextBox1.SetFocus
          Exit Sub
       End If
       
         LastRow = Wks.Cells(Rows.Count, Col).End(xlUp).Row
         LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
         
         Set Rng = Wks.Range(Wks.Cells(2, Col), Wks.Cells(LastRow, Col))
         
           Set FoundMatch = Rng.Find(What:=TextBox1.Text, _
                                     After:=Rng.Cells(1, 1), _
                                     LookAt:=xlWhole, _
                                     LookIn:=xlValues, _
                                     SearchOrder:=xlByRows, _
                                     SearchDirection:=xlNext, _
                                     MatchCase:=False)
          
          If Not FoundMatch Is Nothing Then
             FirstAddx = FoundMatch.Address
             ListView1.ListItems.Clear
             
             Do
               Cnt = Cnt + 1
               R = FoundMatch.Row
               ListView1.ListItems.Add Index:=Cnt, Text:=R
                 For Col = 1 To 8
                   Set C = Wks.Cells(R, Col)
                   ListView1.ListItems(Cnt).ListSubItems.Add Index:=Col, Text:=C.Text
                Next Col
               Set FoundMatch = Rng.FindNext(FoundMatch)
             Loop While FoundMatch.Address <> FirstAddx And Not FoundMatch Is Nothing
             SearchRecords = Cnt
          Else
             ListView1.ListItems.Clear
             SearchRecords = 0
             MsgBox "No match found for " & TextBox1.Text
          End If
          
End Sub




Private Sub UserForm_Activate()




  Dim C As Long
  Dim I As Long
  Dim R As Long
  Dim Wks As Worksheet
  
    With ListView1
      .Gridlines = True
      .View = lvwReport
      .HideSelection = False
      .FullRowSelect = True
      .HotTracking = True
      .HoverSelection = False
      .ColumnHeaders.Add Text:="Row", Width:=64
    End With
    
    Set Wks = Worksheets("Data")
    
      For C = 1 To 8
        ListView1.ColumnHeaders.Add Text:=Wks.Cells(1, C).Text
        ComboBox1.AddItem Wks.Cells(1, C).Text
      Next C
    
End Sub

Display More

lala_milo · Jan 24th 2017

I try to add new row according to the number key in without button but failed

Example, when user key in 2 in column A2, it will add 2 row in below. If user key in 4 in column A2 will add 4 row in below. If user change the number in column A2 from 4 to 1 then will delete 3 row

Currently i using simple code as below

Code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


    Application.ScreenUpdating = False
    Application.DisplayAlerts = False




    If Not Intersect(Target, Range("M6:M10000")) Is Nothing Then
     Cancel = True 'Prevent going into Edit Mode
        Target.Font.Name = "Marlett"
        If Target = vbNullString Then
           Target = "a"
            Target.Offset(1).EntireRow.Insert
          Else
           Target = vbNullString
        End If
    End If
    
    Application.ScreenUpdating = True
End Sub

Display More