Vba code editing

V

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi,

Thanks in advance

I am using the following vba code,
Code:
Sub MG09Aug48
Dim Rng As Range, Dn As Range, Rw As Long, col As Long, nRng As Range, R As Range
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
Set nRng = Range("B2:B4")
For Rw = 0 To 2
Set Rng = Range(nRng(nRng.Count + 1), Range("B" & Rows.Count).End(xlUp))
For Each R In nRng: .Item(R.Value) = Empty: Next
For Each Dn In Rng
If .exists(Dn.Value) Then
Dn.Offset(, 1) = "Yes"
Dn.Offset(, 2) = Dn.Row - nRng(nRng.Count).Row
Exit For
End If
Next Dn
.RemoveAll
Set nRng = nRng.Offset(3)
Next Rw

End With

End Sub


In the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
10089​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
3363​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
13452​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
3009​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
214​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
12744​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
56000​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
3363​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
66080​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
10620​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
10620​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
214​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
12744​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]
17700​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]
10620​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]
5310​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]
7080​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]
7080​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]
5310​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
10620​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
5060​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
13452​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
13452​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]



and i want the following result, its also working for me but its cant stop and restart for previous values, please check the sheet


Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
Amount Given​
[/td][td]
Work​
[/td][td]
Amount Recd.​
[/td][td]
How Many Days​
[/td][td]
​
[/td][td]
Stop & Start​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
10089​
[/td][td]check these values on upcoming cells and if the values are duplicate in rows then add previous another value[/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
3363​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
13452​
[/td][td][/td][td]
​
[/td][td]
1​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
3009​
[/td][td]
​
[/td][td]
​
[/td][td]
2​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
214​
[/td][td]
​
[/td][td]
​
[/td][td]
3​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
12744​
[/td][td]check these values on upcoming cells and if the values are duplicate in rows then add previous another value[/td][td]
​
[/td][td]
4​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
56000​
[/td][td][/td][td]
​
[/td][td]
5​
[/td][td]Recd. After 5 days[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#FFFF00]
3363​
[/td][td][/td][td]
Recd.​
[/td][td]
1​
[/td][td][/td][td]Stop the searching value of row 1,2,3[/td][td]Start the searching of Row value 7,8,9[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
66080​
[/td][td]
​
[/td][td]
​
[/td][td]
2​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
10620​
[/td][td]check these values on upcoming cells and if the values are duplicate in rows then add previous another value[/td][td]
​
[/td][td]
3​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
10620​
[/td][td][/td][td]
​
[/td][td]
4​
[/td][td]Recd. After 4 days[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#FFFF00]
214​
[/td][td][/td][td]
Recd.​
[/td][td]
1​
[/td][td][/td][td]Stop the searching value of row 7,8,9[/td][td]Start the searching of Row value 10,11,12,13 because the value of row 11 & row 12 are same[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
12744​
[/td][td]
​
[/td][td]
​
[/td][td]
2​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]
17700​
[/td][td]
​
[/td][td]
​
[/td][td]
3​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]
10620​
[/td][td]
​
[/td][td]
​
[/td][td]
4​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]
5310​
[/td][td]
​
[/td][td]
​
[/td][td]
5​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]
7080​
[/td][td]
​
[/td][td]
​
[/td][td]
6​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]
7080​
[/td][td]
​
[/td][td]
​
[/td][td]
7​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]
5310​
[/td][td]check these values on upcoming cells and if the values are duplicate in rows then add previous another value[/td][td]
​
[/td][td]
8​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
10620​
[/td][td][/td][td]
​
[/td][td]
9​
[/td][td]Recd. After 9 days[/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td=bgcolor:#FFFF00]
5060​
[/td][td][/td][td]
Recd.​
[/td][td]
​
[/td][td][/td][td]Stop the searching value of row 10,11,12,13[/td][td]Start the searching of Row value 20,21,22[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
13452​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
13452​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td][/td][td]
​
[/td][td]
​
[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Help Pls
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
One more request

If any clarification required my end, feel free to ask
 
Upvote 0
If any extra information required in this post, pls tell me

or if question are not clear, please tell me
 
Upvote 0
O.K. Make it short, in the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
10089​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
3363​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
13452​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
12744​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
420​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
3363​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
500​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
400​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
350​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
1410​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
12744​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Need the following result

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
10089​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
3363​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
13452​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
12744​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
420​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#FFFF00]
3363​
[/td][td]
Recd.​
[/td][td]
After 4 Days​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
500​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
400​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
350​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
1410​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#FFFF00]
12744​
[/td][td]
Recd.​
[/td][td]
After 5 Days​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Do the following task

1. check the value of Row 2,3 & 4
2. From the Row 5
3. when we get the any value of Row 2,3 & 4
4. Highlight the row
5. And show after how many days
6. Now start the search from row 7, 6 & 5
7. From Row 8
8. when we get the any value of Row 7,6 & 5
4. Highlight the row
5. And show after how many days
6. Now start the search from row 12,11 & 10

i have data in column A from row 2 to 1800
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
958
Alex Blakenburg
A
J
  • Question Question
VBA Search box and button code
Replies
0
Views
272
jlcodes
J
M
  • Question Question
how change code from range to TABLE and from sheet to multiple
Replies
8
Views
593
Maklil
M
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
98
Denny57
D
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
322
Patriot2879
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top