014. Excel VBA FIND function Example (Not found, 1 match and multiple matches)
Вставка
- Опубліковано 21 жов 2024
- In this tutorial, you will learn how to search for data in your excel file using macros. this Technique could be faster than going through each row and searching for the information (as we have done in past lessons).
Firstly, we will look at the parameters of the find functions, especially:
1) What you are looking for
2) Whether to look in comments, values, notes or formulas
3) Whether to search for an exact or partial match
4) If caps matter
Second, we will use the IF statement to avoid errors if matches are not found
Thirdly we will get look at the code to:
1) Get 1 match
2) Get multiple matches
Link to workbook: mega.nz/file/K...
if: • 016. Practical use of ...
For and While Loops: • 009. FOR Loop, WHILE L...
VBA if: • 009. FOR Loop, WHILE L...
Channel content: Excel, excel tutorial, Microsoft excel, learn excel, how to use excel, excel basics, basic excel, ms excel, office 365, excel for dummies, excel functions, learn excel basics, microsoft excel for beginners, excel course, Mr Excel, excel help, microsoft excel tutorial, excel tricks, excel tips, excel guide, how to in excel, tricks in excel, tips and tricks, pivots tables, excel formulas, advanced techniques for excel.
#Excel #MsExcel #ExcelMacro
Excellent, good explanation, point by point...
Thank you :))
Great explanation, thank you very much, helps me out a lot
you are welcome Steve! 😊
Fantastic, Thank You.
You r welcome, Alan!
sir you are genious
Thank you Rewati! That s the best comment ever ! 😊😊
Hello sir, can I get the basic VBA links if u have posted ? I am very new to vba
Sure. The link to the workbook is in the video description. If you r new, i suggest you start with the first vba lesson and advance 1 by 1. New videos are going to come every day on the topic.
Hello, yes in many of the videos about VBA (check the VBA playlist), u can go to the descriptions and u will find a link to download the workbook. If u need more of them (ie the workbook is not available to download), just leave a comment and i will respond with the link. :) also let me know if u r improving in VBA after completing those basic lessons
How this can be done using vba array as well as tables listobject thank you 🙏
@georgemathew860 if u r interested in vba, i have a course that explains arrays and all the concepts needed to code: www.udemy.com/course/vba-course-excel/?referralCode=D136051609B9FE0FD4E5
@georgemathew860 this is an example of a code:
Sub FindInArrayAndListObject()
Dim ws As Worksheet
Dim tbl As ListObject
Dim dataArray As Variant
Dim i As Long
Dim searchValue As String
Dim found As Boolean
' Set the worksheet and ListObject
Set ws = ThisWorkbook.Sheets("Sheet1")
Set tbl = ws.ListObjects("Table1")
' Define the value you are looking for
searchValue = "SearchValue" ' Replace this with the value you want to find
' Load the ListObject's data into an array
dataArray = tbl.DataBodyRange.Value
' Loop through the array to find the value in the first column
found = False
For i = LBound(dataArray, 1) To UBound(dataArray, 1)
If dataArray(i, 1) = searchValue Then
MsgBox "Value found in row " & i
found = True
Exit For
End If
Next i
If Not found Then
MsgBox "Value not found"
End If
End Sub
Hi sir can I have your email
So that I send a clearl explaination what I trying to do with my large excel files, extracting and copy a data. Thank you
Hello Jerome, unfortunately, I dont troubleshoot each issue. However, I suggest you try to explain ur problem here and maybe post some pics about ur issue. U might also want to record it on video and post the link here.
Based on this, I will try to guide you on how to solve it.
@@EXCELWIZARDINMINUTES
Hello sir
I was trying to copy a group of lines or cells/table in column A only. Because thrs hundred of groups in my single file, every cells contains wordS , Every groups has different number of cells may be 8 or 10 or 20.
My task is to copy a groups that stars with the cell/words ( for example ) ' TODAYS ACTIVITY' and end with the cell/words 'GOODBYE FOR NOW' but with in that group ther must be a words 'GREAT JOB'. I hope thrs a code for this. to lessen in doing controlF highlight and copy then paste again and again. Thank you in advance sir.
Hello Jerome, yes there is a way you can do this with VBA. U need to have a good understanding in VBA to be able to achieve the task. Here are the steps:
1) create a sub
2) use a loop to go through ur data. Every time the index has to increase by 1
3) create 3 variables as integer. V1, V2,V3. Each of the those variable will be used to identify if u get the great job, the today activity and goodbye
4) create a rowstart variable as long. This is to store ur start row for copyinf
5) with if statements, u have to populate ur variables. In case there is a match, u put 1 in the variable.
Also u need to store the start row when u find today s activity in rowstart
6) Then with another if u check if all the variables are 1, then u need to copy paste the data (u start from rowstart till current row)
If u dont have solid fundations in vba, have a look at the vba playlist. Study the if statement, the variable, the copy paste and the loops at least . Have a go and paste ur code here. I will be able to tell u if u are going in the right direction
@@EXCELWIZARDINMINUTES thank you so much sir. Thank you