Modify content of cells in column "F" according to the content of cells in column "A"

H

harzer

Board Regular
Joined
Dec 15, 2021
Messages
122
Office Version
  1. 2016
Platform
  1. Windows
Hello everyone,
Given my level in vba, impossible for me to find a solution, hence my request.

The purpose of the code is to modify the content of the cells in column "F" according to the content of the cells in column "A".
Let me explain:
To begin with and according to what I have just explained above, the two columns that we are going to work on are column "A" and column "F".
Information about the contents of the cells in column "A"
1- All cells in column "A" end either with "M" for Male or with "F" for Female.
2- All the cells of column "A" contain a year, this year (with four digits) is in the 3rd position starting from the right.
3- We are going to process only the cells of column "A" which contain the current year, therefore those of the year "2023".
What should the requested code do:
To. Determine the cells of column "A" to process, therefore with year "2023".
b. Determine the sex of the subject who is in column "A"
Two scenarios present themselves to us: either the cell ends with "M" or with "F"
If the cell ends with "M", then we will check the content of the cell in column "F" which is on the same line as the cell in column "A", if the content is different from "5H", we correct it, otherwise we go to the next cell.
If the cell ends with "F", then we will check the content of the cell in column "F" which is on the same line as the cell in column "A", if the content is different from "4B", we correct it, otherwise we go to the next cell.
I allow myself to ask you for a quick code because the lines to be processed are numerous.
I remain at your disposal for any additional information.
Thank you in advance for your suggestions.

Unless I am mistaken, you will find below the desired result:

Classeur1
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2AE27-093/2012 MMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B168-06-20124T
3AE27-094/2012 MMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j2B168-06-20124T
4AE27-100/2012 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j2B1310-06-20124T
5AE27-059/2013 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 1m 11j3H1325-04-20134T
6AE27-060/2013 MPG14-029/2012 MPG14-050/2012 FGérard Claude10a 0m 31j5H195-05-20135T
7AE27-084R-10/2013 MYM856-117/2009 MYM856-083/2012 FGérard Claude10a 0m 18j3H118-05-20134T
8AE27-084/2013 MAE27-086/2012 MMN96-075/2012 FGérard Claude10a 0m 17j3H1619-05-20134T
9AE27-085R-12/2013 MYM856-101/2012 MMN96-075/2012 FGérard Claude9a 11m 26j3H1610-06-20134T
10AE27-085/2013 MAE27-032/2012 MPG15-131/2012 FGérard Claude10a 0m 18j5H2118-05-20134T
11AE27-086/2013 FAE27-032/2012 MPG15-131/2012 FGérard Claude9a 11m 9j2B2127-06-20134T
12AE27-087/2013 FYM856-028/2010 MPG14-041/2012 FGérard Claude10a 0m 18j5H2218-05-20134T
13AE27-011/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j4H1122-04-20195T
14AE27-012/2019 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-20195T
15AE27-013/2019 MPG14-048/2016 MAE27-008/2018 FGérard Claude4a 1m 14j4H122-04-20194T
16AE27-014/2019 FAE27-029/2017 MPG14-048/2018 FGérard Claude4a 1m 16j4H220-04-20194T
17AE27-015/2019 MAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j4H619-04-20194T
18AE27-017/2019 FAE27-032/2017 MAE27-007/2018 FGérard Claude4a 1m 17j3H619-04-20194T
19AE27-042/2022 MMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j5H618-06-20224Tx
20AE27-043/2022 FMN96-046/2019 MMN96-036/2021 FGérard Claude0a 11m 18j4H618-06-20224T
21AE27-045/2022 MAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j5H825-06-20224T
22AE27-046/2022 MAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j5H825-06-20224T
23AE27-047/2022 FAE27-024/2021 MAE27-016+/2020 FGérard Claude0a 11m 11j4H825-06-20224T
24AE27-048/2022 FAE27-006/2021 MAE27-052/2021 FGérard Claude0a 11m 8j4H328-06-20224T
25AE27-049/2022 FAE27-006/2021 MAE27-052/2021 FGérard Claude0a 11m 8j4H328-06-20224T
26HTY27-001/2023 MAE27-008/2022 MAE27-039/2022 FGérard Claude0a 1m 11j5H1525-04-20234T
27HTY27-002/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H111-05-20234T
28HTY27-003/2023 MAE27-032/2022 MAE27-026/2022 FGérard Claude0a 0m 31j5H125-05-20234T
29HTY27-004/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
30HTY27-005/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
31HTY27-006/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
32HTY27-007/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
33HTY27-008/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
34HTY27-009/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
35HTY27-010/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
36HTY27-011/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
37HTY27-012/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
38HTY27-013/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
39HTY27-014/2023 FAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j4B1323-05-20234T
40HTY27-015/2023 FAE27-33/2022 MAE27-024/2022 FGérard Claude0a 0m 11j4B725-05-20234T
41HTY27-016/2023 FAE27-008/2022 MAE27-039/2022 FGérard Claude0a 1m 11j4B1525-04-20234T
42HTY27-017/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H111-05-20234T
43HTY27-018/2023 MAE27-032/2022 MAE27-026/2022 FGérard Claude0a 0m 31j5H125-05-20234T
44HTY27-019/2023 FAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j4B521-05-20234T
45HTY27-020/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
46HTY27-021/2023 FAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j4B521-05-20234T
47HTY27-022/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
48HTY27-023/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
49HTY27-024/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-20234T
50HTY27-025/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
51HTY27-026/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-20234T
52HTY27-027/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
53HTY27-028/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
54HTY27-029/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
55HTY27-030/2023 MAE27-33/2022 MAE27-024/2022 FGérard Claude0a 0m 11j5H725-05-20234T
56HTY27-031/2023 MAE27-008/2022 MAE27-039/2022 FGérard Claude0a 1m 11j5H1525-04-20234T
57HTY27-032/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H111-05-20234T
58AE27-095/2012 FMA76-020/2011 MMM72-112/2011 FGérard Claude10a 11m 28j4B168-06-20124T
59AE27-096/2012 FCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j4B1310-06-20124T
60AE27-097/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j5H1310-06-20124T
61AE27-098/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j5H1310-06-20124T
62AE27-099/2012 MCF03-156/2011 MMM72-114/2011 FGérard Claude10a 11m 26j5H1310-06-20124T
63HTY27-038/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
64HTY27-039/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
65HTY27-040/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
66HTY27-041/2023 MAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j5H622-05-20234T
67HTY27-042/2023 FAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j4B1323-05-20234T
68HTY27-043/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
69HTY27-044/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
70HTY27-045/2023 FAE27-33/2022 MAE27-024/2022 FGérard Claude0a 0m 11j4B725-05-20234T
71HTY27-046/2023 FAE27-008/2022 MAE27-039/2022 FGérard Claude0a 1m 11j4B1525-04-20234T
72HTY27-047/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H111-05-20234T
73HTY27-048/2023 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j4B1122-04-20195T
74HTY27-049/2023 MPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-20195T
75HTY27-050/2023 MPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j5H1122-04-20195T
76HTY27-051/2023 FPG14-003/2018 MAE27-003/2018 FGérard Claude4a 1m 14j4B1122-04-20195T
77HTY27-052/2023 FPG14-048/2016 MAE27-008/2018 FGérard Claude4a 1m 14j4B122-04-20194T
78HTY27-053/2023 FAE27-029/2017 MPG14-048/2018 FGérard Claude4a 1m 16j4B220-04-20194T
79HTY27-054/2023 FAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j4B521-05-20234T
80HTY27-055/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
81HTY27-056/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
82HTY27-057/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
83HTY27-058/2023 MAE27-035/2022 MMN96-008/2022 FGérard Claude0a 1m 3j5H111-05-20234T
84HTY27-059/2023 FAE27-032/2022 MAE27-026/2022 FGérard Claude0a 0m 31j4B125-05-20234T
85HTY27-060/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
86HTY27-061/2023 FAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j4B521-05-20234T
87HTY27-062/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
88HTY27-063/2023 FAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j4B521-05-20234T
89HTY27-064/2023 MAE27-010/2021 MMN96-034/2021 FGérard Claude0a 0m 15j5H521-05-20234T
90HTY27-065/2023 FAE27-022/2022 MMN96-010/2020 FGérard Claude0a 0m 14j4B622-05-20234T
91HTY27-066/2023 FAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j4B1323-05-20234T
92HTY27-067/2023 FAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j4B1323-05-20234T
93HTY27-068/2023 MAE27-029/2022 M207-034/2022 FGérard Claude0a 0m 13j5H1323-05-20234T
94HTY27-069/2023 FAE27-33/2022 MAE27-024/2022 FGérard Claude0a 0m 11j4B725-05-20234T
Feuil1
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Does this do what you want. Please test on a backup copy of your data.

VBA Code: 
Sub ChangeYearSex()
    Dim arr, i  As Long
    arr = Worksheets("Sheet1").Range("A1:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To UBound(arr)
        If Right(arr(i, 1), 6) = "2023 M" Then arr(i, 6) = "5H"
        If Right(arr(i, 1), 6) = "2023 F" Then arr(i, 6) = "4B"
    Next
    Worksheets("Sheet1").Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub
 
Upvote 0
Hello igold,
Thank you for your reply.
The code works fine, thank you very much.
However, there is a small detail to point out to you, namely:
As we are in 2023, the code works very well.
So that I can use it for the next years, It would be desirable if the code could determine the current year itself, in this way I can use it for this year "2023" and also the next years.
How can you modify it so that it can work sustainably.
Good for you.
 
Upvote 0
If you always wanted to always use the current year then this should work:

VBA Code: 
Sub CureentYearSex()
    Dim arr, i  As Long, y As String
    arr = Worksheets("Sheet1").Range("A1:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    y = Format(Date, "YYYY")
    For i = 1 To UBound(arr)
        If Right(arr(i, 1), 6) = y & " M" Then arr(i, 6) = "5H"
        If Right(arr(i, 1), 6) = y & " F" Then arr(i, 6) = "4B"
    Next
    Worksheets("Sheet1").Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

If you wanted to be prompted to enter a year, you could use this:

VBA Code: 
Sub ChosenYearSex()
    Dim arr, i  As Long, y As String
    arr = Worksheets("Sheet1").Range("A1:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    y = Application.InputBox("Enter Year", , , , , , , 2)
    For i = 1 To UBound(arr)
        If Right(arr(i, 1), 6) = y & " M" Then arr(i, 6) = "5H"
        If Right(arr(i, 1), 6) = y & " F" Then arr(i, 6) = "4B"
    Next
    Worksheets("Sheet1").Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Or is you wanted to take the value from a cell in your workbook- provide the name of the sheet and cell, and code could be written to do that as well...
 
Upvote 0
Solution
Hello igold,
Thank you for your reply.
The modified code (of the two proposed versions) suits me and satisfies me perfectly.
I took the code from the first version of the two proposals, the one that takes into account the current year automatically.
Thank you very much for your availability.
 
Upvote 0
You're welcome, I was happy to help. Thanks for the feedback!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
142
Peter_SSs
Peter_SSs
H
  • Solved
Transpose data.
Replies
4
Views
110
harzer
H
H
  • Solved
Count nonblank lines (between blank lines)
2
Replies
15
Views
691
Alex Blakenburg
A
H
  • Question
Insert the space character in the penultimate place of all the cells of columns "A" - "B" & "C".
Replies
3
Views
233
Fluff
Fluff
C
  • Solved
Extract Dates from 2 columns
Replies
2
Views
319
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

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