Macro to Find a info in more than one column
Solved/Closed
AJ
-
May 7, 2010 at 04:34 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 10, 2010 at 08:52 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 10, 2010 at 08:52 AM
Related:
- Macro to Find a info in more than one column
- Spell number in excel without macro - Guide
- Display two columns in data validation list but return only one - Guide
- Netflix update payment info - Guide
- Macros in excel download - Download - Spreadsheets
- Radio info code - Guide
7 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 05:57 AM
May 7, 2010 at 05:57 AM
Your data look very confusing. From what I understood you said that you want to see that column C is master ? It does not look to me
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Dear Rizvisa1,
Please refer to the following link
[ https://authentification.site/files/22314281/Daily_Running_Total_Dashboard.xls ]
password shown over there is [ neribajikova ].
Kindly let me know if this helps.
In this excle file, the master information is on Column # [Z]
The source Columns are on [D17 to X40].
I would like the matching result on Column [AF].
Best Regards - AJ
Please refer to the following link
[ https://authentification.site/files/22314281/Daily_Running_Total_Dashboard.xls ]
password shown over there is [ neribajikova ].
Kindly let me know if this helps.
In this excle file, the master information is on Column # [Z]
The source Columns are on [D17 to X40].
I would like the matching result on Column [AF].
Best Regards - AJ
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 07:08 AM
May 7, 2010 at 07:08 AM
AJ now can you explain your issue. Use the sample sheet to explain what you seek
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 08:27 AM
May 7, 2010 at 08:27 AM
AJ
I looked at your sheet "May" and none of the value in Z exist in D17:X40. Are you sure you have the right file ? or am i missing some thing here?
I looked at your sheet "May" and none of the value in Z exist in D17:X40. Are you sure you have the right file ? or am i missing some thing here?
Didn't find the answer you are looking for?
Ask a question
Ok,
From Column [D17] thru [X40], I have a list of batches completed daily , listed over here (across month).
The Column [Z] contains list of batches expected for the month.
Now the help I am seeking from you is to update a status of a Batch in Column [AF] as "Complete" based on their availability/appearance on Column [D17] thru [X40].
Note:
1. I have noticed that none of the books listed on Column [D], [E] and [F] are listed in Column [Z], hope you wont'n mind to add any batch from [Z] to column [D] so that you can test the results. - Apologize for not sending the correct data.
2. The merge cells could should create a problem, if this affects a macro or a query, then I can remove the merge and duplicate the information in the actual data. - Please advise.
Kindly let me know if more details are required.
Thank you and Best Regards - AJ
From Column [D17] thru [X40], I have a list of batches completed daily , listed over here (across month).
The Column [Z] contains list of batches expected for the month.
Now the help I am seeking from you is to update a status of a Batch in Column [AF] as "Complete" based on their availability/appearance on Column [D17] thru [X40].
Note:
1. I have noticed that none of the books listed on Column [D], [E] and [F] are listed in Column [Z], hope you wont'n mind to add any batch from [Z] to column [D] so that you can test the results. - Apologize for not sending the correct data.
2. The merge cells could should create a problem, if this affects a macro or a query, then I can remove the merge and duplicate the information in the actual data. - Please advise.
Kindly let me know if more details are required.
Thank you and Best Regards - AJ
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 09:11 AM
May 7, 2010 at 09:11 AM
Add this function
then instead of match, use this function
as
=IF(isPresent(Z2, D17:X40)>0, "Complete", "")
Function isPresent(lookfor As String, searchRange As Range) As Long Dim lThisRow As Long On Error Resume Next lThisRow = searchRange.Find(lookfor, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row On Error GoTo 0 isPresent = lThisRow End Function
then instead of match, use this function
as
=IF(isPresent(Z2, D17:X40)>0, "Complete", "")
Dear Rizvisa1,
Thank for the function and a query to execute it, but it seems to be not updating the status.
Follwoing are the steps I followed:
a. [ Alt + F11 ] to open [ Microsoft Visual Basic Editor ].
b. Insert a Module.
c. Pasted the copied function.
d. Quit from [ Microsoft Visual Basic Editor ] by pressing [ Alt + Q ]
e. In Worksheet copied the above [IF] query(Function).
But it is not showing any result. The result column is blank.
The sample file is uploaded on [ https://authentification.site/files/22317534/Daily_Running_Total_DashboardV2.xls ] password is [ bocuzudibeju ]
Kindly find some time to review to the sample file I have created and advise.
Thank you & Best Regards - AJ
Thank for the function and a query to execute it, but it seems to be not updating the status.
Follwoing are the steps I followed:
a. [ Alt + F11 ] to open [ Microsoft Visual Basic Editor ].
b. Insert a Module.
c. Pasted the copied function.
d. Quit from [ Microsoft Visual Basic Editor ] by pressing [ Alt + Q ]
e. In Worksheet copied the above [IF] query(Function).
But it is not showing any result. The result column is blank.
The sample file is uploaded on [ https://authentification.site/files/22317534/Daily_Running_Total_DashboardV2.xls ] password is [ bocuzudibeju ]
Kindly find some time to review to the sample file I have created and advise.
Thank you & Best Regards - AJ
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 10:51 AM
May 7, 2010 at 10:51 AM
When I opened the book it showed result already
http://www.speedyshare.com/files/22317999/snap2.jpg
http://www.speedyshare.com/files/22317999/snap2.jpg
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 02:42 PM
May 7, 2010 at 02:42 PM
Yeah seems like it. It works with office Xp but not with office 2007.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 7, 2010 at 03:41 PM
May 7, 2010 at 03:41 PM
Ok try this. It works in 97 so it must work in 2000 too.
You would call this function as
=IF(isMatchPresent(Z2, D$17:X$40)>0, "Complete", "")
You would call this function as
=IF(isMatchPresent(Z2, D$17:X$40)>0, "Complete", "")
Function isMatchPresent(lookfor As String, searchRange As Range) As Variant Dim sStartRange As String Dim sEndRange As String Dim lStartRow As Long Dim iStartCol As Integer Dim lEndRow As Long Dim iEndCol As Integer Dim lMatchRow As Variant Dim iCol As Integer lMatchRow = 0 isMatchPresent = lMatchRow If (InStr(1, searchRange.Address, ":") > 0) Then sStartRange = Left(searchRange.Address, InStr(1, searchRange.Address, ":") - 1) sEndRange = Mid(searchRange.Address, InStr(1, searchRange.Address, ":") + 1) Else sStartRange = searchRange.Address sEndRange = searchRange.Address End If lStartRow = Range(sStartRange).Row iStartCol = Range(sStartRange).Column lEndRow = Range(sEndRange).Row iEndCol = Range(sEndRange).Column For iCol = iStartCol To iEndCol lMatchRow = 0 On Error Resume Next lMatchRow = WorksheetFunction.Match(lookfor, Range(Cells(lStartRow, iCol), Cells(lEndRow, iCol)), 0) On Error GoTo 0 If lMatchRow <> 0 Then isMatchPresent = lMatchRow + lStartRow - 1 Exit Function End If Next iCol On Error Resume Next End Function
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 10, 2010 at 08:52 AM
May 10, 2010 at 08:52 AM
Yeah I saw that too that this solution is a bit slot. You just have to upgrade to at least XP, then use original function. That one is faster. Other way would be to to do away with function and have a macro that do it.