VB Code or Macro - help me pls

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi, pls check the below table and provide macro/vb code

we have a data in excel and want to check that how many times a certain task will repeat, you can understand it by checking the below excel

Excel 2007 32 bit
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
S.No.​
Hours​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
1​
2​
We need to check the numbers 2,5,6 (or row number 1,2,3) from serial no. 4 in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
2​
5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
3​
6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
4​
8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
5​
9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
6​
8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
7​
4​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
8​
1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
9​
4​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
10​
5​
We have find the number five here, and its come after 7 rows, Now again we need to check the numbers 5,4,1 (S.No. 8,9,10) in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​
11​
2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​
12​
8​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​
13​
7​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​
14​
1​
We have find the number one here, and its come after 4 rows, Now again we need to check the numbers 1,7,8 (S.No. 12,13,14) in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​
15​
6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​
16​
5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​
17​
6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​
18​
7​
We have find the number Seven here, and its come after 4 rows, Now again we need to check the numbers 7,6,5 (S.No. 16,17,18) in next coming rows
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​
19​
9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​
20​
9​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​
21​
6​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​
22​
5​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​
23​
2​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​
24​
5​
We have find the number Five here, and its come after 6 rows,

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

Result
1. we have found that after 7 row we find the value one time
2. we have found that after 6 row we find the value one time
3. we have found that after 4 row we find the value two time

so the result is

After 7 = 1
After 6 = 1
After 4 = 2

pls help and provide the code/macro​

 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Answer starts "E1", it is not the same as Yours !!!
NB:- Why when your looking for "5,6,7" , do you find "5" instead of "6" which comes after "6", and give it "6" rows when it seems to be "3" ????
Code:
[COLOR="Navy"]Sub[/COLOR] MG28May04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("B5", Range("B" & Rows.Count).End(xlUp))
Num = Join(Application.Transpose(Range("B2:B4")), ",")

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    [COLOR="Navy"]If[/COLOR] InStr(Num, Dn.Value) > 0 [COLOR="Navy"]Then[/COLOR]
        Num = Join(Application.Transpose(Dn.Offset(-2).Resize(3)), ",")
        .Item(c) = .Item(c) + 1
        c = 0
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
c = 0

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, "E") = "After " & K & " = " & .Item(K)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Many Many Thanks MickG for the code.......

checking the code and asked you if we need any editing in this code
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi MickG,

checked, can it will show the result like this

i have following sheet

Col ACol B
S.No.Student Work
12
23
36
48
54
62
73
86

<tbody>
</tbody>


In the above sheet i have approx 140 row and in coloumn B, we have approx 70 values

and i want the three different result from this sheet

1 - We will check the value 2, 3 & 6 (S.No. 1, 2 & 3) from the S.No. 4 and when we find the any value we will update it as the given sheet,
After it will check the rest 2 value and we will update it as the given sheet
After getting 2 value, we will start the check last value and update it when we find it according to the given sheet

Col ACol BFirstSecondThird
S.No.Student WorkResultAfter how manyResultAfter how manyResultAfter how many times
12
23
36
48
54
62Yes3
73Yes4
86Yes5

<tbody>
</tbody>


2 - We will check the value 2, 3 & 6 (S.No. 1, 2 & 3) from the S.No. 4 and when we find the any value we will update it as the given sheet,
After that we will again take the value 2,4,8 (S.No. 4, 5 & 6) from the S.No. 7 and when we find the any value we will update it as the given sheet,
After that we will again take the value 8,6,3 (S.No. 7, 8 & 9) from the S.No. 10 and when we find the any value we will update it as the given sheet,

Col ACol B
S.No.Student WorkResultAfter How many times
12
23
36
48
54
62Yes3
73
86
98Yes3
104
115
120
130
147
153Yes6

<tbody>
</tbody>


Hope will get a solution....
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code for Data 1 results :-
Code:
[COLOR="Navy"]Sub[/COLOR] MG09Aug04
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("B2:B4"): .Item(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            col = col + 2
            Dn.Offset(, col - 1) = "Yes"
            Dn.Offset(, col) = Dn.Row - 4
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Code for Data 2 results:-

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

[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
408
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
love you MickG, great

again, you all guys are master in excel. really
 

Watch MrExcel Video

Forum statistics

Threads
1,114,255
Messages
5,546,801
Members
410,759
Latest member
Bufnercash
Top