View Full Version : [SOLVED] find "w" instance in column and count within range then change the next "Daily" val.
rrosa1
07-22-2015, 07:44 AM
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
p45cal
07-22-2015, 12:20 PM
try this instead of your Sub put_w():
Sub put_w2()
Dim ws As Worksheet
Dim i As Long
Dim rTFind As Range, rBFind, rFirst, mylookin As Range
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
'sname = Format(Day(Int(rTFind.Offset(-1))) + 1, "DD")
'ws.Rows(rTitle).Copy Sheets(sname).Cells(rTitle, "A")
With ws
'then count the occurence in that row range which have "w"
'' how i can incorporate this function to count in my code ?
Set mylookin = rTFind.CurrentRegion.Columns(12)
Set mylookin = mylookin.Offset(1).Resize(mylookin.Rows.count - 1)
Var = count("w", mylookin)
'if it is less then 6 time then change the value in column L to make
If Var < 6 Then
For i1 = mylookin.Rows.count To 1 Step -1
'mylookin.Cells(i1).Select 'pd
If Var < 6 And mylookin.Cells(i1).Value <> "w" Then
mylookin.Cells(i1).Select 'pd
myResponse = MsgBox("u want to change value of L" & Selection.Row & "?" & vbLf & "Cancel moves on to the next group", vbYesNoCancel, "Order Complete")
Select Case myResponse
Case vbCancel
Exit For
Case vbYes
mylookin.Cells(i1).Value = "w"
Var = Var + 1
End Select
End If
Next i1
End If
End With
Set rTFind = ws.Range("A:A").FindNext(rTFind)
If rTFind.Address = rFirst.Address Then Exit Do
Loop
' Stop
End If
Set rTFind = Nothing
Set rBFind = Nothing
Set rFirst = Nothing
End SubAlso this needs a tweak to avoid confusion:
Function count(find As String, lookin As Range) As Long
Dim cll As Range
For Each cll In lookin.Cells
If (cll.Value = find) Then count = count + 1 '//case sens
Next
End Function
rrosa1
07-23-2015, 06:41 AM
hi p45cal
thanks a lot's u save my day but when i try to run the code it give me error 1004 "select method or range class fail at this line
mylookin.Cells(i1).Select 'pd
thanks agin for your help
p45cal
07-23-2015, 07:15 AM
Early on in your code you have a reference to ThisWorkbook.Worksheets("pp"), this has to be the active sheet at the time the code runs. If you are experimenting on another sheet you have either:
To change ThisWorkbook.Worksheets("pp") to reflect the active sheet's name
or
change ThisWorkbook.Worksheets("pp") to ActiveSheet
rrosa1
07-23-2015, 07:54 AM
thanks p45cal
is there anyway code only change to"pp" sheet ? aslo one more change to remove the msg box and do change the value
since there will b lot's of "w" to b changed
rrosa1
07-23-2015, 08:28 AM
thanks p45cal
Set ws = ThisWorkbook.Worksheets("pp")
i change this to
Set ws = ThisWorkbook.Worksheets("pp")
ws.Activate
and also i comment the msg as this
If Var < 6 And mylookin.Cells(i1).Value <> "w" Then
mylookin.Cells(i1).Select 'pd
' myResponse = MsgBox("u want to change value of L" & Selection.Row & "?" & vbLf & "Cancel moves on to the next group", vbYesNoCancel, "Order Complete")
' Select Case myResponse
' Case vbCancel
' Exit For
' Case vbYes
mylookin.Cells(i1).Value = "w"
Var = Var + 1
' End Select
End If
and it's work like a charm thanks again for all yr help i still learning so sorry for dumb question. and thanks for bear up with me .
p45cal
07-23-2015, 10:29 AM
If you want it only to work on sheet pp and you don't need the msgbox question, then you don't need to select the cell (it was only to show the user which cell might be changed), then you also don't need to activate the sheet, so you can also lose the lines:
mylookin.Cells(i1).Select 'pd
and
ws.Activate
There are 4 other lines which are redundant and can be removed:
rTitle = ws.Cells.find("Balance", ws.[A1], xlValues, xlPart).Row
LR = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
With ws
End With
rrosa1
07-23-2015, 12:19 PM
thanks p45cal
i did remove those line and code still work as intended.
thanks
rrosa1
07-24-2015, 10:12 AM
hi p45cal
sorry to bother u again ,i was trying to change this line of code
If Var < 6 Then
For iw = mylookin.Rows.count To 1 Step -1
'mylookin.Cells(iw).Select
If Var < 6 And mylookin.Cells(iw).Value <> "Weekly" Then '''to make 6 count weekly
to this
If Var < 6 Then
'If Var Then
For iw = mylookin.Rows.count To 1 Step -1
'mylookin.Cells(iw).Select
If Var < 5 And mylookin.Cells(iw).Value <> "Weekly" Then '''to make 6 count weekly
but then code did not change the data sheet as needed when i run the macro again can u let me know what mi doing wrong
by the way my intention is to change the limit from 6 to 5 or 4 since there r some time data change so i need to change that value but it must be less then 6 how can i do that?
yr help is appreciated.
p45cal
07-24-2015, 10:30 AM
You'd better supply all the code as is.
rrosa1
07-24-2015, 10:42 AM
sorry hear my change from yr original code
Sub aa_put_w()
Dim ws1 As Worksheet
Dim iw As Long, LR, Var
Dim rTFind As Range, rBFind, rFirst, mylookin As Range
Dim a As String, nm
Dim cc As Double
Set ws1 = ThisWorkbook.Worksheets("RR")
ws1.Activate
' rTitle = ws.Cells.find("Balance", ws.[A1], xlValues, xlPart).Row
'Spot bottom row of data
' LR = ws1.Cells(ws1.Rows.count, "A").End(xlUp).Row
'# Start search
' # Search for the "period" in column A to spot the top of the data range
On Error Resume Next
nm = Application.InputBox("Name to be inserted in C ?", Title:="Name Input", Default:="P B", Type:=2) 'type 2 is text
If nm = "" Then GoTo ExitHandler
'On Error Resume Next
cc = Application.InputBox("Name to be inserted in C ?", Default:=41.29, Type:=1) 'type 1 is num
If cc = 0 Then GoTo ExitHandler
Set rTFind = ws1.Range("A:A").find(".", ws1.[A1], xlValues, xlWhole)
If Not rTFind Is Nothing Then
Set rFirst = rTFind
Do
'With ws1
'then count the occurence in that row range which have "w"
Set mylookin = rTFind.CurrentRegion.Columns(12)
Set mylookin = mylookin.Offset(1).Resize(mylookin.Rows.count - 1)
Var = count("Weekly", mylookin)
'if it is less then 6 time then change the value in column L to makeup for 6 time
mylookin.Cells(iw, 2).Value = Var
If Var < 6 Then
For iw = mylookin.Rows.count To 1 Step -1
'mylookin.Cells(iw).Select
If Var < 5 And mylookin.Cells(iw).Value <> "Weekly" Then '''to make 6 count weekly
mylookin.Cells(iw, 3).Value = Var 'Selection.Row 'mylookin.Cells(i1).Value '.Select 'pd
' myResponse = MsgBox("u want to change value of L" & Selection.Row & "?" & vbLf & "Cancel moves on to the next group", vbYesNoCancel, "Order Complete")
' Select Case myResponse
' Case vbCancel
' Exit For
' Case vbYes
mylookin.Cells(iw).Value = "Weekly"
mylookin.Cells(iw, -8).Value = nm
mylookin.Cells(iw, -6).Value = cc
mylookin.Cells(iw, -5).Value = cc
Var = Var + 1
' End Select
End If
Next iw
End If
'End With
Set rTFind = ws1.Range("A:A").FindNext(rTFind)
If rTFind.Address = rFirst.Address Then Exit Do
Loop
' Stop
End If
ExitHandler:
Set Var = Nothing
Set rTFind = Nothing
Set rBFind = Nothing
Set rFirst = Nothing
End Sub
'''' for weekly counts
Function count(find As String, lookin As Range) As Long
Dim cll As Range
For Each cll In lookin.Cells
If (cll.Value = find) Then count = count + 1 '//case sens
Next
End Function
when i try to run macro again for < 6 it count all weekly in column L i try to print on sh in "M3" i also added the new work book where code not work
p45cal
07-24-2015, 11:22 AM
by the way my intention is to change the limit from 6 to 5 or 4 since there r some time data change so i need to change that value but it must be less then 6 how can i do that?
Is the change to 4, 5 or 6 for the whole sheet or per group on the sheet? Do you want to ask the user what the limit is each time?
but then code did not change the data sheet as needed when i run the macro again can u let me know what mi doing wrong Well that's a difficult one because I have no idea what 'as needed' is.
I see you put the count in column M before you make changes; do you want this to be after you make changes instead?
Does M3 value represent the total count of 'Weekly' on the whole sheet? If so, before or after changes made?
It would be a good idea to change all instances of If Var < 6 so that they are all the same.
rrosa1
07-24-2015, 11:34 AM
hi p45cal
is it possible to do this ,when 1time run the code then
if within finding "." row "per group" if Weekly is 3 time then change to 4 go to next group of row if weekly is 4 time then make it to 5 time and if it is 5 then make it 6 time and if it is 6 then do nothing .hope i explained it right.
I see you put the count in column M before you make changes; do you want this to be after you make changes instead?
that i was looking for how many "Var" found in each group and how many change have be made .that data is not needed one it work with sample data
Is the change to 4, 5 or 6 for the whole sheet or per group on the sheet? Do you want to ask the user what the limit is each time?
it's per group and if it ask the limit each time is batter too so i don't need to change the code each time if limit chage
p45cal
07-24-2015, 11:59 AM
try:
Sub aa_put_w()
Dim ws1 As Worksheet
Dim iw As Long, LR, Var
Dim rTFind As Range, rBFind, rFirst, mylookin As Range
Dim a As String, nm
Dim cc As Double
Set ws1 = ThisWorkbook.Worksheets("RR")
ws1.Activate
'On Error Resume Next
nm = Application.InputBox("Name to be inserted in C ?", Title:="Name Input", Default:="P B", Type:=2) 'type 2 is text
If nm = "" Then GoTo ExitHandler
'On Error Resume Next
cc = Application.InputBox("Name to be inserted in C ?", Default:=41.29, Type:=1) 'type 1 is num
If cc = 0 Then GoTo ExitHandler
Set rTFind = ws1.Range("A:A").find(".", ws1.[A1], xlValues, xlWhole)
If Not rTFind Is Nothing Then
Set rFirst = rTFind
Do
Set mylookin = rTFind.CurrentRegion.Columns(12)
Set mylookin = mylookin.Offset(1).Resize(mylookin.Rows.count - 1)
Var = count("Weekly", mylookin)
mylookin.Cells(0, 2).Value = Var
If Var < 6 Then ' if 6 or more do nothing.
TargetCount = Var + 1 ' this line sets the target number of Weekly in the group to one more than that found (if there were less than 6).
For iw = mylookin.Rows.count To 1 Step -1
'mylookin.Cells(iw).Select
If Var < TargetCount And mylookin.Cells(iw).Value <> "Weekly" Then '''to make 6 count weekly
mylookin.Cells(iw, 3).Value = Var 'Selection.Row 'mylookin.Cells(i1).Value '.Select 'pd
mylookin.Cells(iw).Value = "Weekly"
mylookin.Cells(iw, -8).Value = nm
mylookin.Cells(iw, -6).Value = cc
mylookin.Cells(iw, -5).Value = cc
Var = Var + 1
End If
Next iw
End If
Set rTFind = ws1.Range("A:A").FindNext(rTFind)
If rTFind.Address = rFirst.Address Then Exit Do
Loop
' Stop
End If
ExitHandler:
Set Var = Nothing
Set rTFind = Nothing
Set rBFind = Nothing
Set rFirst = Nothing
End Sub
rrosa1
07-24-2015, 12:12 PM
sorry to b pain in back but code does not do change the value in L as well as other column it only print total Var found in "M5"
and i try to step in to the code by F8 and c where it goes it counting all the weekly in l in function .
p45cal
07-24-2015, 01:02 PM
temporarily add the line:
mylookin.Cells(iw).Interior.Color = vbRed
directly after the line:
mylookin.Cells(iw).Value = "Weekly"
to highlight the changed cells in column L.
It works here, but then I don't have your sheet RR.
rrosa1
07-24-2015, 01:14 PM
i did but the same result as nothing change,i try to step in to the code by "F8" macro do not inter in to
If Var < 6 Then ' if 6 or more do nothing.
TargetCount = Var + 1 ' this line sets the target number of Weekly in the group to one more than that found (if there were less than 6).
For iw = mylookin.Rows.count To 1 Step -1
'mylookin.Cells(iw).Select
If Var < TargetCount And mylookin.Cells(iw).Value <> "Weekly" Then '''to make 6 count weekly
mylookin.Cells(iw, 3).Value = Var 'Selection.Row 'mylookin.Cells(i1).Value '.Select 'pd
mylookin.Cells(iw).Value = "Weekly"
mylookin.Cells(iw).Interior.Color = vbRed
mylookin.Cells(iw, -8).Value = nm
mylookin.Cells(iw, -6).Value = cc
mylookin.Cells(iw, -5).Value = cc
Var = Var + 1
End If
Next iw
End If
so it did not change the color i think it do not getting the range value for group
from hear
Set mylookin = rTFind.CurrentRegion.Columns(12)
Set mylookin = mylookin.Offset(1).Resize(mylookin.Rows.count - 1)
Var = count("Weekly", mylookin)
so it just counting all weekly from column L since it is > 6 so it do not go in to IF statment
what v can change for mylookin to find range between two "." ?
btw there is 1 more rr(2) sh in wb it same data sh u can use that to
but u need to change the name
p45cal
07-24-2015, 01:54 PM
Send me the file
rrosa1
07-24-2015, 02:06 PM
Send me the file
p45cal
07-24-2015, 04:15 PM
Well, you've made some changes, you've added some data in the blank rows between groups, namely in cells C15, C32, C44, C54, C64, C74, C86, C96, C106, C117, C126, C134, C144, C155, C164, C180, C192 and C201.
The code depended on .CurrentRegion of each group and this worked because each group was separated from the next by a completely blank row. This no longer exists. The .currentRegion is now the whole data on the sheet. Delete those values to blank cells.
Next, you have the line:
If Var < TargetCount And mylookin.Cells(iw).Value <> "w" Then
which is looking for "w", not "Weekly".
There are one or two other changes.
See attached which has two buttons, one to recreate a sheet rr to work on; it copies rr(2) then deletes the 1s in column C. Then there is another button which runs the macro.
rrosa1
07-25-2015, 05:03 AM
p45cal thanks a lot
i was just running the macro two three time so code put the value in C column and i did not notice ,apologies for sloppiness on my part and asking solution that was working.
i appreciate your help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.