Thanks for your excellent explanation.. Sir., I need your help... I want to entry the data date wise... For example. 12-09-2023 i need to specific date select for data entry and put value the specific date. If i missing previous some date data also. Just want to put data individual date wise specific cells.
Hi, I have gone through your video, Its was very useful information and nice tip to prevent duplicate entries in excel. But, I have question I have tried your example and I also tried to copy the value and paste in the same column. I received an error message like Run-time error '13' Type Mismatch Kind Regards, Mukund
Dear Sir, your videos are good to follow and very helpful. I was wandering if in excel i can use VBA to color a range of cells based on input done in another group of cells. I want to input data from cells A1 and A6 and then that same data will be graphically sorted in let say range C10: F14, with a color fill and border around the whole range. Can this be done. ? Thanks in advance for your comments.
Hello Sir, I did exactly the way you wrote code and it worked. but if I delete someting in the sheet like row or column or paste it gives me error 13 type missmatch.
Thank you for the tutorial. I am working on a project and I would like to use this VBA but I would like to apply it to 176 sheets excluding the Table of Contents which is the first sheet. In a specified column, which is the same for all the worksheets. If someone entered a value in a column on a sheet. P.S. All of my data is being entered in a table format. I would like to warn them that that value already exist in that column on another sheet. Please help. Thank you
This is a great video, thanks for doing this. I have a unique problem though and I'm hoping that you can help. I am working to schedule staff to work at different locations and I am looking for a way to ensure that I am not scheduling them in two locations at the same time. I use Excel and have different sheets for each location with times in columns and I would like an alert or some notification if I have scheduled the same person in two different locations during the same time. Can this be accomplished using Excel?
Hi Sir, I need to delete the entire row if a duplicate entry is found in one of the columns of a table. However, I get Run-time error 13: Type Mismatch. Below mentioned is the code. Could you please give your valuable insights on what's going wrong here. Private Sub Worksheet_Change(ByVal Target As Range) If Application.CountIf(Range("E:E"), Target) > 1 Then MsgBox "Duplicate Data!", vbCritical, "Remove data" ActiveCell.EntireRow.Delete 'Target.Value = "" End If End Sub
You are using the code in the wrong context. This VBA tutorial will help: www.exceltrainingvideos.com/tag/automate-deleting-duplicate-date-entries/ You can also search www.exceltrainingvideos.com
Hello Sir. I tried to apply these codes of yours and my problem is: When I delete or insert a row it will give me a "run time error '13". Please advice what shall I do? Hoping for your reply. Thanks a lot.
Hi sir, how if i wanted to do with several columns? example< column a, b, c, and D. How is the coding would be? Appreciate if you could help. :) thank you
In this example, after messaging duplicate entry, the cursor jumps down to the next cell shifting the onus on to the operator to change the value or not. Ideally, I think, unless and until the data is fully tested and validated, the cursor should not leave the current cell, forcing the operator to modify the entry compulsorily. Please, tell me how this feature can be included in this VBA code.
These tow sample VBA videos will guide: www.exceltrainingvideos.com/tag/check-that-all-fields-in-a-userform-are-completed-before-submitting-data-excel-vba/ www.exceltrainingvideos.com/how-to-allow-only-numerical-values-in-excel-user-form-text-box-using-vba/ For more idease you can search this channel or our website www.exceltrainingvideos.com
I want to update some datas from Sheet1 to Sheet2 when we click update button from sheet1. when I press the update button, i want to transfer datas from RED color to RED color and the same as other colors. (Sheet 1, A15 to A5,) Plz check your mail for clear picture of my doubt.
hi this is very helpful.. thank you i have a question. how do you make a function in vba removing a specific value in a cell.. example: remove "#, ","#".. examples: 1, 2, #, 3 #, 1, 2, 3 1, 2, 3, # results: 1, 2, 3 1, 2, 3 1, 2, 3 i really dont want use the simple find and replace format.. since my speadsheat is very big.. please help.. :( thank you in advance :)
sifu please teach me....after i validate my data, it prevent me from entering any num or value in my cell....even the new number that did'nt have duplicate value in my cell
hello again Kumar, This method works however if i delete a row, get and error (Run-time error "13) on the line If Application.CountIf(Range("B:B"), Target) > 1 Then. What can i do to solved this? thanks again great work.
Hi Sir, Thanks for making this informative video. However i came across a scenario. When pasting multiple data in specific column, vba gives an error Run-time error '1004'. This error also pops up when deleting or adding rows. please help..
I am creating a Bar Graph of completed work. Information gathered from multiple sheets. Files marked complete, pending or blank. I have some duplicate files which I must keep, both marked as complete. However I want to avoid counting them in my graph. the formula currently looks like this=('sheet1'!A2:A1000,"*complete*"). How could I avoid counting the duplicates?
@@Exceltrainingvideos Tricky to explain. The data on sheet 1 is showing history of documents updated (Keeping previous revisions). EG. I have the same document number repeated 4 times in column 1. Column 2 I have status ie. Pending 1, Pending 2, Complete 1, Complete 2. I would like to create a formula to count only ONE Complete note for this document as the fact that is was pending and complete 1 is irrelevant now. At the moment my formula is counting all four status's and therefore my bar graph shows more documents than there actually are.
how would you do this when adding a persons name to a list using a button that you have written in VBA, but if someone types the same name and clicks the button it then shows the error 'name already in table' and deletes entry?
Hi Dinesh, Thank you very much for this video, it's really useful and you explain things clearly :) I'm trying to add your VBA code into my macro but it's not working because probably i'm using it in the wrong way. I saw your gmail in the previous response , would it be OK to send you an email with my code so that you can help me?? I would really appreciate your assistance as it's the only thing that i need to complete my macro. Thank you very much.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim x, dict Dim i As Long, lr As Long, r As Long If Target.Column = 1 Or Target.Column = 3 And Target.Row > 1 Then r = Target.Row lr = Cells(Rows.Count, "C").End(xlUp).Row If lr < 2 Then Exit Sub x = Range("A2:C" & lr).Value Set dict = CreateObject("Scripting.Dictionary") For i = 1 To UBound(x, 1) If i r - 1 Then dict.Item(x(i, 1) & x(i, 3)) = "" End If Next i If dict.exists(Cells(r, 1) & Cells(r, 3)) Then Range("A" & r & ":C" & r).Interior.Color = vbRed Else Range("A" & r & ":C" & r).Interior.ColorIndex = xlNone End If End If End Sub
@@Exceltrainingvideos hmm at first after I'm typing it than it's working...but when I pasted it is then not... Anyway, I'm struggling to combine the range of a few columns..... Each one of them apart I can do easily through the validation data. No need to type code. My target is to select any columns all together while they are not right next to each other. I'm trying many different ways to achieve that and I'm failing....
@@mieczyslawm7093 Copy/Paste of code will not work! This Excel VBA tutorial will guide: www.exceltrainingvideos.com/tag/automatically-highlight-column-header/
Thanks for your excellent explanation..
Sir.,
I need your help... I want to entry the data date wise... For example.
12-09-2023 i need to specific date select for data entry and put value the specific date. If i missing previous some date data also. Just want to put data individual date wise specific cells.
all thumbs up. Great code, that's what I need. Thanks a lot for teaching. Already subscribed. Enjoy your classes
Hi,
I have gone through your video, Its was very useful information and nice tip to prevent duplicate entries in excel. But, I have question I have tried your example and I also tried to copy the value and paste in the same column. I received an error message like Run-time error '13' Type Mismatch
Kind Regards,
Mukund
Dear Sir, your videos are good to follow and very helpful. I was wandering if in excel i can use VBA to color a range of cells based on input done in another group of cells. I want to input data from cells A1 and A6 and then that same data will be graphically sorted in let say range C10: F14, with a color fill and border around the whole range. Can this be done. ? Thanks in advance for your comments.
Very simple and clear explanation tutorial, thanks
Thanks. Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Hello Sir, I did exactly the way you wrote code and it worked. but if I delete someting in the sheet like row or column or paste it gives me error 13 type missmatch.
This link will help: www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-using-countif-in-vba/
Or search www.exceltrainingvideos.com
Thank you for the tutorial. I am working on a project and I would like to use this VBA but I would like to apply it to 176 sheets excluding the Table of Contents which is the first sheet. In a specified column, which is the same for all the worksheets. If someone entered a value in a column on a sheet. P.S. All of my data is being entered in a table format. I would like to warn them that that value already exist in that column on another sheet. Please help. Thank you
This is a great video, thanks for doing this. I have a unique problem though and I'm hoping that you can help. I am working to schedule staff to work at different locations and I am looking for a way to ensure that I am not scheduling them in two locations at the same time. I use Excel and have different sheets for each location with times in columns and I would like an alert or some notification if I have scheduled the same person in two different locations during the same time. Can this be accomplished using Excel?
Can you show me your code? takyardinesh@gmail.com
Hi Sir, I need to delete the entire row if a duplicate entry is found in one of the columns of a table. However, I get Run-time error 13: Type Mismatch. Below mentioned is the code. Could you please give your valuable insights on what's going wrong here. Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountIf(Range("E:E"), Target) > 1 Then
MsgBox "Duplicate Data!", vbCritical, "Remove data"
ActiveCell.EntireRow.Delete
'Target.Value = ""
End If
End Sub
You are using the code in the wrong context. This VBA tutorial will help: www.exceltrainingvideos.com/tag/automate-deleting-duplicate-date-entries/
You can also search www.exceltrainingvideos.com
This was a great video and very helpful. Thank you! What would be the vba code that allows me to check column A across more than 1 excel worksheet?
Loop through the sheets using a 'for next' loop.
Hello Sir. I tried to apply these codes of yours and my problem is: When I delete or insert a row it will give me a "run time error '13". Please advice what shall I do? Hoping for your reply. Thanks a lot.
Can you share the code in this comments section?
Hi sir, how if i wanted to do with several columns? example< column a, b, c, and D. How is the coding would be?
Appreciate if you could help. :) thank you
In this example, after messaging duplicate entry, the cursor jumps down to the next cell shifting the onus on to the operator to change the value or not. Ideally, I think, unless and until the data is fully tested and validated, the cursor should not leave the current cell, forcing the operator to modify the entry compulsorily. Please, tell me how this feature can be included in this VBA code.
These tow sample VBA videos will guide:
www.exceltrainingvideos.com/tag/check-that-all-fields-in-a-userform-are-completed-before-submitting-data-excel-vba/
www.exceltrainingvideos.com/how-to-allow-only-numerical-values-in-excel-user-form-text-box-using-vba/
For more idease you can search this channel or our website www.exceltrainingvideos.com
I want to update some datas from Sheet1 to Sheet2 when we click update button from sheet1.
when I press the update button, i want to transfer datas from RED color to RED color and the same as other colors. (Sheet 1, A15 to A5,)
Plz check your mail for clear picture of my doubt.
Good morning sir thanks for your excel tips one small clarification is there is any way to find debit and credit value in a range for example: A:A
Have a look at this link: www.exceltrainingvideos.com/tag/find-method-vba/
Alternatively, you can do a search at www.exceltrainingvideos.com
hi this is very helpful.. thank you
i have a question. how do you make a function in vba removing a specific value in a cell.. example: remove "#, ","#"..
examples:
1, 2, #, 3
#, 1, 2, 3
1, 2, 3, #
results:
1, 2, 3
1, 2, 3
1, 2, 3
i really dont want use the simple find and replace format.. since my speadsheat is very big.. please help.. :( thank you in advance :)
sifu please teach me....after i validate my data, it prevent me from entering any num or value in my cell....even the new number that did'nt have duplicate value in my cell
hello again Kumar, This method works however if i delete a row, get and error (Run-time error "13) on the line If Application.CountIf(Range("B:B"), Target) > 1 Then. What can i do to solved this? thanks again great work.
Think!
Hi Sir, Thanks for making this informative video. However i came across a scenario. When pasting multiple data in specific column, vba gives an error Run-time error '1004'. This error also pops up when deleting or adding rows. please help..
Check your code carefully or search www.exceltrainingvideos.com
I thank you Dr for you efforts,can we make a code to search and catch duplicated data in whole worksheet ?
These links might help:
www.exceltrainingvideos.com/find-duplicates-in-two-different-worksheets-in-ms-excel/
www.exceltrainingvideos.com/nested-loops-excel-vba/
www.exceltrainingvideos.com/how-to-use-advanced-filter-in-excel-to-extract-unique-records-and-remove-duplicates/
+Dinesh Kumar Takyar many thanks agin it works well 👍
I am creating a Bar Graph of completed work. Information gathered from multiple sheets. Files marked complete, pending or blank. I have some duplicate files which I must keep, both marked as complete. However I want to avoid counting them in my graph. the formula currently looks like this=('sheet1'!A2:A1000,"*complete*"). How could I avoid counting the duplicates?
How is the data structured in the file where you're applying the formula? Give an example in the comments.
@@Exceltrainingvideos Tricky to explain. The data on sheet 1 is showing history of documents updated (Keeping previous revisions). EG. I have the same document number repeated 4 times in column 1. Column 2 I have status ie. Pending 1, Pending 2, Complete 1, Complete 2. I would like to create a formula to count only ONE Complete note for this document as the fact that is was pending and complete 1 is irrelevant now. At the moment my formula is counting all four status's and therefore my bar graph shows more documents than there actually are.
Hi, when I use this code an error accurs when I try to clear a row or cut it to another sheet. Mismatched types... Why is that? Thank u
www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-using-countif-in-vba/
how would you do this when adding a persons name to a list using a button that you have written in VBA, but if someone types the same name and clicks the button it then shows the error 'name already in table' and deletes entry?
If you don't wish to avoid duplicates then don't use COUNTIF. Or search www.exceltrainingvideos.com
can we apply this for any Any text
Yes. Try it out!
Thanx a lot sir with this code.
Great video! Thanks!
Thank you for video. I want to write the code for count of duplicate in another column (just number).
This link will help: www.exceltrainingvideos.com/counting-and-displaying-totals-automatically-using-excel-vba/
Or search www.exceltrainingvideos.com
how about delete the entire row?
This Excel VBA tutorial will help: www.exceltrainingvideos.com/archive-excel-data-using-vba/
Pls. we need to download the excel file .
Hi Dinesh, Thank you very much for this video, it's really useful and you explain things clearly :) I'm trying to add your VBA code into my macro but it's not working because probably i'm using it in the wrong way. I saw your gmail in the previous response , would it be OK to send you an email with my code so that you can help me??
I would really appreciate your assistance as it's the only thing that i need to complete my macro. Thank you very much.
👍👍👍👍⭐️, how to check for text plz?
You can use the code also for text.
Nice tip. Thank you.
Dear Sir ,
How to avoid duplicate entries on two column ("E:F") using countifs in Excel VBA
Thanks & Regards
Soumen Bera
(India)
I use this to highhlite if duplicate in range found .
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim x, dict Dim i As Long, lr As Long, r As Long If Target.Column = 1 Or Target.Column = 3 And Target.Row > 1 Then r = Target.Row lr = Cells(Rows.Count, "C").End(xlUp).Row If lr < 2 Then Exit Sub x = Range("A2:C" & lr).Value Set dict = CreateObject("Scripting.Dictionary") For i = 1 To UBound(x, 1) If i r - 1 Then dict.Item(x(i, 1) & x(i, 3)) = "" End If Next i If dict.exists(Cells(r, 1) & Cells(r, 3)) Then Range("A" & r & ":C" & r).Interior.Color = vbRed Else Range("A" & r & ":C" & r).Interior.ColorIndex = xlNone End If End If End Sub
Hi,
Can someone answer my below query?
You should share your code!
Thanku sir
2022 April... That doesn't work
Check your code!
@@Exceltrainingvideos hmm at first after I'm typing it than it's working...but when I pasted it is then not...
Anyway, I'm struggling to combine the range of a few columns.....
Each one of them apart I can do easily through the validation data. No need to type code.
My target is to select any columns all together while they are not right next to each other.
I'm trying many different ways to achieve that and I'm failing....
@@mieczyslawm7093 Copy/Paste of code will not work! This Excel VBA tutorial will guide: www.exceltrainingvideos.com/tag/automatically-highlight-column-header/