1 Attachment(s)
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
Code:
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