Results 1 to 20 of 21

Thread: find "w" instance in column and count within range then change the next "Daily" val.

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    find "w" instance in column and count within range then change the next "Daily" val.

    how can i do with this code i know hear good Samaritan help me in past thanks a lot's to look into it
    Sub put_w()
        Dim ws As Worksheet
        Dim i As Long
        Dim rTFind As Range, rBFind, rFirst
    
        Set ws = ThisWorkbook.Worksheets("pp")
        rTitle = ws.Cells.find("Balance", ws.[A1], xlValues, xlPart).Row
        'Spot bottom row of data
        LR = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
        '# Start search
        ' # Search for the "period" in column A to spot the top of the data range
    
        Set rTFind = ws.Range("A:A").find(".", ws.[A1], xlValues, xlWhole)
        If Not rTFind Is Nothing Then
            Set rFirst = rTFind
            Do
                Set rBFind = ws.Range("A:A").FindNext(rTFind)
                'sname = Format(Day(Int(rTFind.Offset(-1))) + 1, "DD")
    
                'ws.Rows(rTitle).Copy Sheets(sname).Cells(rTitle, "A")
                If rBFind.Address <> rFirst.Address Then
                    With ws
                        'then count the occurence in that row range which have  "w"
                         '' how i can incorporate this function to count in my code ?
                        Var = count("w", Range(rTFind.Address, rBFind.Address))
                      'if it is less then 6 time then change the value in column L to make
                        If Var <= 6 Then
                            For i1 = .Cells(.Rows.count, 1).End(xlUp).Row To 5 Step -1
                                If .Cells(i1, 12).Value = "w" Then
                                   If MsgBox("u want to change value of L?" & Var, vbYesNo, "Order Complete") = vbNo Then
                                        Exit Sub
    
                            'by asking though input box like this one below
                           'Application.InputBox("change column B name data range", Type:=8)
                           'the total count 6 and also change the value in column C,D,E value
    
    
                                    End If
                                End If
                            Next i1
                        End If
                    End With
                Else
                    'do same on last records
    
                End If
    
                If rBFind.Address = rFirst.Address Then Exit Do
                Set rTFind = rBFind
            Loop
            ' Stop
        End If
        Set rTFind = Nothing
        Set rBFind = Nothing
        Set rFirst = Nothing
    
    End Sub
    
    'Var = count("w", Range("A1:A100"))
    
    Function count(find As String, lookin As Range) As Long
        Dim cell As Range
        For Each cell In lookin
            If (cell.Value = find) Then count = count + 1    '//case sens
        Next
    End Function
    hear is my sample data wb
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •