Find Value of a cell in column "B"

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
159
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
As I am a novice and I can't find a solution to my problem, I submit this vba coding request to you.
The purpose of the code is to look for a word in a column and select its relevant cell.
Some explanations and information to provide you so that you can develop the code:
We are working on the "Issus" sheet.
The word I'm looking for is still in cell "M2".
The search is done in column "B", the searched word is repeated several times (therefore in duplicate) in column "B".
There are blank rows in the "Issues" sheet and should remain.

The purpose of the search is to find the number of the last line of the cell in column "B" which contains the searched word of cell "M2".
When the last line of the cell containing the searched word is found, its cell is selected to be able to display it on the screen.

I allow myself to ask you for a fairly fast code, such as: Using tables (Faster) because my data is quite large.

I remain at your disposal for other additional information.
Cheers.

Copie de FormListeSansDoublonsDict.xls
ABCDEFGHIJKLM
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevageNombreChoix Du Sujet
2AE27-093/2012 MMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B1608-06-124TFemelle AE27-076/2012 M
3AE27-094/2012 MMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B1608-06-124TFemelle
4AE27-095/2012 FMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B1608-06-124TFemelle
5AE27-096/2012 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j2B1310-06-124TFemelle
6AE27-097/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j3H1310-06-124TMâle
7AE27-098/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j3H1310-06-124TMâle
8AE27-099/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j3H1310-06-124TMâle
9AE27-100/2012 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j2B1310-06-124TFemelle
10
11AE27-053/2013 MAE27-098/2012 MAE27-038/2012 FGérard Claude9a 11m 3j4H301-07-134TFemelle
12AE27-054/2013 MPG17-002/2012 MPG15-118/2012 FGérard Claude10a 1m 0j5H1004-05-134TFemelle
13AE27-055/2013 FPG17-002/2012 MPG15-118/2012 FGérard Claude10a 1m 0j5H1004-05-134TMâle
14AE27-056/2013 FMM72-076/2012 MPG14-041/2012 FGérard Claude9a 11m 11j5H2225-06-134TMâle
15AE27-057/2013 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 1m 0j2B1304-05-134TFemelle
16AE27-058/2013 MPG14-029/2012 MPG14-031/2012 FGérard Claude10a 1m 11j5H1825-04-134TMâle
17AE27-059/2013 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 1m 11j3H1325-04-134TMâle
18AE27-060/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 31j5H1905-05-135TMâle
19AE27-062/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 17j5H1919-05-134TMâle
20
21AE27-063/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 30j5H1906-05-135TMâle
22AE27-064/2013 FPG14-029/2012 MPG14-050/2012 FGérard Claude10a 1m 16j5H1920-04-134TMâle
23AE27-065/2013 MAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j4H1506-05-135TMâle
24AE27-066/2013 MAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j4H1506-05-135TMâle
25AE27-067/2013 MAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j4H1506-05-135TMâle
26AE27-068/2013 FAE27-076/2012 MAE27-087/2012 FGérard Claude10a 0m 30j1B1506-05-135TFemelle
27AE27-069/2013 FPG14-029/2012 MPG14-031/2012 FGérard Claude10a 1m 0j2B1804-05-134TFemelle
28AE27-070/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude10a 1m 0j3H204-05-134TMâle
29AE27-071/2013 FYM856-028/2010 MAE27-044/2012 FGérard Claude10a 1m 0j3B204-05-134TFemelle
30AE27-072/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude9a 11m 24j3H212-06-135TMâle
31AE27-073/2013 FYM856-028/2010 MAE27-044/2012 FGérard Claude10a 0m 23j3H213-05-134TMâle
32AE27-074/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude10a 0m 23j3H213-05-134TMâle
33AE27-075/2013 MYM856-028/2010 MAE27-044/2012 FGérard Claude10a 0m 22j5H214-05-134TMâle
34
35AE27-077/2013 FAE27-078/2012 MYM856-083/2012 FGérard Claude10a 0m 23j3H113-05-134TMâle
36AE27-078R-12/2013 MCF03-156/2011 MAE27-044/2012 FGérard Claude10a 0m 23j5H213-05-134TMâle
37AE27-078/2013 MAE27-078/2012 MYM856-083/2012 FGérard Claude10a 0m 23j3H113-05-134TMâle
38AE27-079/2013 FAE27-078/2012 MYM856-083/2012 FGérard Claude10a 0m 17j3H119-05-134TMâle
39AE27-080/2013 FAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j5B1619-05-134TFemelle
40AE27-081/2013 MAE27-086/2012 MMN96-075/2012 FGérard Claude9a 11m 11j3H1625-06-134TMâle
41AE27-082/2013 FAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j3H1619-05-134TMâle
42AE27-083R-12/2013 FYM856-117/2009 MAE27-056/2012 FGérard Claude10a 0m 18j2B1218-05-134TFemelle
43AE27-083/2013 FAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 18j2B1618-05-134TFemelle
44AE27-084R-10/2013 MYM856-117/2009 MYM856-083/2012 FGérard Claude10a 0m 18j3H118-05-134TMâle
45AE27-084/2013 MAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j3H1619-05-134TMâle
46AE27-085R-12/2013 MYM856-101/2012 MMN96-075/2012 FGérard Claude9a 11m 26j3H1610-06-134TMâle
47AE27-085/2013 MAE27-032/2012 MPG15-131/2012 FGérard Claude10a 0m 18j5H2118-05-134TMâle
48AE27-086/2013 FAE27-032/2012 MPG15-131/2012 FGérard Claude9a 11m 9j2B2127-06-134TFemelle
49AE27-087/2013 FYM856-028/2010 MPG14-041/2012 FGérard Claude10a 0m 18j5H2218-05-134TMâle
50AE27-088/2013 MYM856-028/2010 MPG14-041/2012 FGérard Claude9a 11m 3j2H2201-07-134TMâle
51AE27-089/2013 FYM856-028/2010 MPG14-041/2012 FGérard Claude10a 0m 18j5H2218-05-135TMâle
52AE27-090/2013 MCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 18j5H1418-05-135TMâle
53AE27-091/2013 MCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j4H1404-06-135TMâle
54AE27-092/2013 FCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j5H1404-06-135TMâle
55AE27-093/2013 FCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j5H1404-06-135TMâle
56
57AE27-095/2013 FCF03-156/2011 MYM856-131/2011 FGérard Claude10a 0m 0j5H1404-06-134TMâle
58AE27-096/2013 MPG14-011/2012 MPG14-020/2012 FGérard Claude10a 0m 0j5H1704-06-134TMâle
59AE27-097/2013 FPG14-011/2012 MPG14-020/2012 FGérard Claude10a 0m 0j5H1704-06-134TMâle
60AE27-098/2013 FPG14-011/2012 MPG14-020/2012 FGérard Claude9a 11m 31j2B1705-06-134TFemelle
61AE27-099/2013 MCF03-156/2011 MAE27-044/2012 FGérard Claude10a 0m 0j3H204-06-134TMâle
62AE27-100/2013 FPG15-121/2012 MPG14-050/2012 FGérard Claude9a 11m 24j5H1912-06-135TFemelle
63AE27-168-11/2013 FYM856-028/2010 MMM72-112/2011 FGérard Claude9a 0m 2j2B802-06-145TFemelle
64AE27-169-11/2013 MYM856-028/2010 MMM72-112/2011 FGérard Claude9a 11m 3j5H801-07-134TMâle
65AE27-170-11/2013 MYM856-028/2010 MMM72-112/2011 FGérard Claude9a 11m 9j3H827-06-134TMâle
66AE27-171-11/2013 FYM856-028/2010 MMM72-112/2011 FGérard Claude9a 11m 9j2B827-06-134TFemelle
67AE27-173-11/2013 FAE27-027/2011 MYM856-053/2011 FGérard Claude10a 0m 12j2B2024-05-134TFemelle
68AE27-174R-11/2013 MYM856-117/2009 MYM856-083/2012 FGérard Claude10a 0m 12j5H124-05-134TMâle
69AE27-175-11/2013 FAE27-027/2011 MYM856-053/2011 FGérard Claude10a 0m 12j2B2024-05-134TFemelle
70AE27-001/2014 FPG14-017/2013 MPG14-050/2012 FGérard Claude9a 1m 25j5H2211-04-145TFemelle
71AE27-002/2014 MPG14-017/2013 MPG14-050/2012 FGérard Claude9a 1m 25j5H2211-04-145TFemelle
72AE27-020/2014 MWH1183-037/2012 MPG14-020/2012 FGérard Claude9a 1m 21j5H2015-04-145TFemelle
73AE27-021/2014 MWH1183-037/2012 MPG14-020/2012 FGérard Claude9a 1m 21j5H2015-04-145TFemelle
74AE27-022/2014 MWH1183-037/2012 MPG14-020/2012 FGérard Claude9a 1m 21j5H2015-04-145TFemelle
75AE27-023/2014 FAE27-064/2013 MPG15-102/2013 FGérard Claude9a 1m 21j5H1815-04-145TMâle
76AE27-001/2019 MPG14-001/2016 MAE27-010/2018 FGérard Claude4a 1m 17j5H1219-04-195TFemelle
77AE27-002/2019 mPG14-001/2016 MAE27-010/2018 FGérard Claude4a 1m 17j5H1219-04-195TFemelle
78
79AE27-006/2019 FPG14-003/2018 MAE27-075/2017 FGérard Claude4a 1m 17j5H1019-04-195TFemelle Tt jaune
80AE27-007/2019 MPG14-003/2018 MAE27-075/2017 FGérard Claude4a 1m 17j5H1019-04-195TFemelle Tt jaune
81AE27-008/2019 MPG14-003/2018 MAE27-075/2017 FGérard Claude4a 1m 17j5H1019-04-195TFemelle Tâche au dos
82AE27-009/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-195TFemelle Tte jaune
83AE27-010/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-195TFemelle Tt jaune
84AE27-011/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j4H1122-04-195TFemelle Tte jaune
85AE27-012/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-195TFemelle Tâche derrière la tête
86AE27-013/2019 MPG14-048/2016 MAE27-008/2018 FGérard Claude4a 1m 14j4H122-04-194TMâle Tt jaune
87AE27-014/2019 FAE27-029/2017 MPG14-048/2018 FGérard Claude4a 1m 16j4H220-04-194TFemelle Tte jaune
88AE27-015/2019 MAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j4H619-04-194TMâle
89AE27-017/2019 FAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j3H619-04-194TFemelle Tt jaune - Ptes Flûtes - cris d'appel!
90AE27-018/2019 MAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j4H619-04-194TMâle
91AE27-019/2019 MAE27-034/2017 MAE27-034/2018 FGérard Claude4a 1m 16j4H820-04-194TMâle Tâche tête
92AE27-020/2019 MAE27-034/2017 MAE27-034/2018 FGérard Claude4a 1m 16j4H820-04-194TMâle Tâche derrière œil et tête - Klingelrol
93AE27-035/2022 MMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j5H413-06-224TMâle Tt jaunex
94AE27-036/2022 MMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j5H413-06-224TMâle Tt Jaune
95AE27-037/2022 FMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j4H413-06-224TFemelle Tte jaunex
96AE27-038/2022 FMN96-005/2021 MAE27-068/2021 FGérard Claude0a 11m 23j4H413-06-224TFemelle avec tâche derrière la têtex
97AE27-039/2022 FAE27-049/2021 MAE27-003/2021 FGérard Claude0a 11m 23j5H113-06-224TFemelle tâche ronde derrière la têtex
98AE27-040/2022 FAE27-049/2021 MAE27-003/2021 FGérard Claude0a 11m 23j4H113-06-224TFemelle Tte jaune
99AE27-041/2022 FMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j4H618-06-224TFemelle Tte jaune
100AE27-042/2022 MMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j5H618-06-224TMâle Tt jaunex
101AE27-043/2022 FMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j4H618-06-224TFemelle Tte jaune
102
103AE27-045/2022 MAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j5H825-06-224TMâle Tâche aile droite (n'aime pas être pris en main)
104AE27-046/2022 MAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j5H825-06-224TMâle Tte jaune
105AE27-047/2022 FAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j4H825-06-224TFemelle Tte jaune
106AE27-048/2022 FAE27-006/2021 MAE27-052/2021 FGérard Claude0a 11m 8j4H328-06-224TFemelle Tâche aux 2 ailes
107AE27-049/2022 FAE27-006/2021 MAE27-052/2021 FGérard Claude0a 11m 8j4H328-06-224TFemelle Tâche ronde der, Tête + tâche aile droite
108AE27-001/2023 MAE27-008/2022 MAE27-039/2022 FGérard Claude0a 1m 11j5H1525-04-234TMâle Tache au dos - Mange seul à 26 jours
109AE27-002/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H1101-05-234TMâle T Jaune (Pas Sauvage)
110AE27-003/2023 FAE27-032/2022 MAE27-026/2022 FGérard Claude0a 0m 31j5H1205-05-234TFemelle tâche tête
111AE27-004/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle Tâche tête
112AE27-005/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
113AE27-006/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
114AE27-007/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
115AE27-008/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-234TMâle
116AE27-009/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-234TMâle
117AE27-010/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-234TMâle
118AE27-011/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-234TMâle
119AE27-012/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-234TMâle
120AE27-013/2023 FAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-234TMâle
121AE27-014/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-234TMâle
122AE27-018/2023 MAE27-33/2022 MAE27-024/2022 FGérard Claude0a 0m 11j5H725-05-234TMâle
123
124
125
Issus
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
harzer said:
The purpose of the search is to find the number of the last line of the cell in column "B" which contains the searched word of cell "M2".
Click to expand...
Try this:
Rich (BB code): 
Sub harzer()
Dim c As Range
With Sheets("Issus")
    Set c = .Range("B:B").Find(What:=.Range("M2"), LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        MsgBox "Found at row " & c.Row  'this is the row
    Else
        MsgBox "Can't find " & .Range("M2")
    End If
End With
End Sub
I set "SearchDirection" argument to "xlPrevious", so the search will start from the bottom. In this case the searched word was found at row 26.
 
Upvote 0
Solution
Hello Akini,
Thank you for your reply, your code is working fine and giving me the desired result.
Big thanks.
Cheers.
 
Upvote 0
harzer,

When marking a post as the solution, please mark the actual post that contains the solution, not your own post acknowleding that some other post is the solution.
I have updated it in this case for you.
 
Upvote 0
You're welcome, glad to help & thanks for the feedback.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
83
MrsFraser07
M
H
  • Solved
Modify content of cells in column "F" according to the content of cells in column "A"
Replies
5
Views
520
igold
igold
H
  • Question Question
copy some columns based on the value in column "A".
Replies
9
Views
365
Cubist
Cubist
H
  • Solved
Count nonblank lines (between blank lines)
2
Replies
15
Views
1K
Alex Blakenburg
A
H
  • Solved
Copy rows if cells in column "K" end with either "X" or "x".
Replies
3
Views
235
rollis13
rollis13

Forum statistics

Threads
1,224,823
Messages
6,181,175
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