all thumbs up. Great code, that's what I need. Thanks a lot for teaching. Already subscribed. Enjoy your classes
@10minutesengineeringsolution
11 ай бұрын
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.
@dannyyeoh2340
5 жыл бұрын
Very simple and clear explanation tutorial, thanks
@Exceltrainingvideos
4 жыл бұрын
Thanks. Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
@frankie3005
5 жыл бұрын
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.
@Exceltrainingvideos
5 жыл бұрын
This link will help: www.exceltrainingvideos.com/how-to-avoid-duplicate-entries-in-excel-using-countif-in-vba/ Or search www.exceltrainingvideos.com
@bluchap43
7 жыл бұрын
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
@arunkumarchowdhury8721
4 жыл бұрын
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.
@Exceltrainingvideos
4 жыл бұрын
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
@surabhisawant5059
4 жыл бұрын
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
@Exceltrainingvideos
4 жыл бұрын
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
@patagonja
8 жыл бұрын
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.
@nalinchandra
5 жыл бұрын
Thanx a lot sir with this code.
@thadayoose
8 жыл бұрын
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.
@mukundpotdar9849
6 жыл бұрын
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
@MrMefilipino
4 жыл бұрын
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.
@Exceltrainingvideos
4 жыл бұрын
Can you share the code in this comments section?
@azlinshahira3112
7 жыл бұрын
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
@masnawiahzainuddin9516
6 жыл бұрын
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
@israelpeterraj
5 жыл бұрын
Thank you for video. I want to write the code for count of duplicate in another column (just number).
@Exceltrainingvideos
5 жыл бұрын
This link will help: www.exceltrainingvideos.com/counting-and-displaying-totals-automatically-using-excel-vba/ Or search www.exceltrainingvideos.com
@jeremyrosene157
7 жыл бұрын
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?
@Exceltrainingvideos
7 жыл бұрын
Can you show me your code? takyardinesh@gmail.com
@pmsocho
8 жыл бұрын
Great video! Thanks!
@sylwiasojka3517
7 жыл бұрын
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?
@Exceltrainingvideos
7 жыл бұрын
Loop through the sheets using a 'for next' loop.
@kisoy2x
8 жыл бұрын
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 :)
@shindre4752
7 жыл бұрын
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.
@Exceltrainingvideos
7 жыл бұрын
Think!
@riorodrigues6089
6 жыл бұрын
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..
@Exceltrainingvideos
6 жыл бұрын
Check your code carefully or search www.exceltrainingvideos.com
@RajKumar-kt3uu
8 жыл бұрын
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
@Exceltrainingvideos
8 жыл бұрын
Have a look at this link: www.exceltrainingvideos.com/tag/find-method-vba/ Alternatively, you can do a search at www.exceltrainingvideos.com
@joelhender94
6 жыл бұрын
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?
@Exceltrainingvideos
6 жыл бұрын
If you don't wish to avoid duplicates then don't use COUNTIF. Or search www.exceltrainingvideos.com
@the14Feb2011
8 жыл бұрын
I thank you Dr for you efforts,can we make a code to search and catch duplicated data in whole worksheet ?
@Exceltrainingvideos
8 жыл бұрын
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/
@the14Feb2011
8 жыл бұрын
+Dinesh Kumar Takyar many thanks agin it works well 👍
@11clark11
5 жыл бұрын
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
5 жыл бұрын
How is the data structured in the file where you're applying the formula? Give an example in the comments.
@11clark11
5 жыл бұрын
@@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.
@jollyonlan
8 жыл бұрын
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
This Excel VBA tutorial will help: www.exceltrainingvideos.com/archive-excel-data-using-vba/
@datadev1
8 жыл бұрын
Nice tip. Thank you.
@yogeshdesai3049
4 жыл бұрын
can we apply this for any Any text
@Exceltrainingvideos
4 жыл бұрын
Yes. Try it out!
@redhaakhund1271
3 жыл бұрын
👍👍👍👍⭐️, how to check for text plz?
@Exceltrainingvideos
3 жыл бұрын
You can use the code also for text.
@KingMK33
8 жыл бұрын
Pls. we need to download the excel file .
@abelhernandeztorres.6864
6 жыл бұрын
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.
@Soumenbera99
6 жыл бұрын
Dear Sir , How to avoid duplicate entries on two column ("E:F") using countifs in Excel VBA Thanks & Regards Soumen Bera (India)
@nassimgaming4948
6 жыл бұрын
I use this to highhlite if duplicate in range found .
@nassimgaming4948
6 жыл бұрын
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
@mukundpotdar9849
6 жыл бұрын
Hi, Can someone answer my below query?
@Exceltrainingvideos
6 жыл бұрын
You should share your code!
@mieczyslawm7093
2 жыл бұрын
2022 April... That doesn't work
@Exceltrainingvideos
2 жыл бұрын
Check your code!
@mieczyslawm7093
2 жыл бұрын
@@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....
@Exceltrainingvideos
2 жыл бұрын
@@mieczyslawm7093 Copy/Paste of code will not work! This Excel VBA tutorial will guide: www.exceltrainingvideos.com/tag/automatically-highlight-column-header/
Пікірлер: 64