Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Cannot Update Data from Idiot-Proof Excel - Part 7 tutorial

0

Hello, I have gone through Part 7 of the Idiot-Proof series multiple times and cannot get my existing data row to update with the changed information. Instead it creates a new line, thus creating duplicate entries with the same Date (my unique identifier for the row). My macro matches exactly to the sample provided from the tutorial. When I run the tutorial on my computer it works perfectly, but the same does not work with my document. Can you help me figure out what I have done wrong or excluded? 

Note: this is my first workbook that I've ever written macros with, so I may be a little out of my league here. 

Answer
Discuss

Discussion

I noticed that all three buttons on your form are name "PrintBtn" and that the one with the "Delete" caption has no macro attached to it.
Variatus (rep: 4889) Jan 6, '19 at 7:29 pm
Thank you for pointing this out... I had originally set up a Print Button and just copied and pasted without renaming, then eventually deleting the Print Button. I have not gotten to the Delete button yet, that is my next step. Thank you! 
Elaine (rep: 4) Jan 7, '19 at 10:11 pm
Add to Discussion

Answers

0
Selected Answer

This issue isn't, frankly, to be solved by the tqarget group of Don's tutorial, as Don's own answer demonstrates. The point is that Excel records a date as a number, an integer for a day and a fraction for the time of day. Jan 8 2018 = 43473, and 43473.5 would be 12 noon on Jan 8, 2018. Depending upon your cell format that number would be displayed as Jan 08, 2018 12:00:00 or 1/8/18 or simply Tue, to show just a few variation.

Now, when you look for a date which value should you look for? The number or the display? By asking you to look at the Formula, Don was hoping to let you catch the number. It didn't work out. The look-up value must be of the same format as the looked-for value.

Correct me if I'm wrong, but I think that you neither set the date format for E6 (in your Form), nor in the Data list. If so, both were set to Excel's default (which is controlled by Window's Regional Settings). The odd one out is the InputBox, however, which returns a string. It isn't of the same format as the others. Therefore I suggest you try this Find command in your Module3

    Set Rng = dataIDCol.Find(What:=CDate(searchValue), _
        LookIn:=xlFormulas, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        MatchCase:=False)

CDate(searchValue) will convert the date you entered in the InputBox to the default date format, and that should be the same as happens to be in the look-up column.

The solution worked on my PC and I hope it will work for you too. If it doesn't we have to look further among the different formats of your dates.

Discuss

Discussion

You are very right about the Dates being the issue! Never figured that would be used as a unique identifier - I'm just so used to using a UUID for things like this that I forget that regular people don't use that stuff every day, or at all lol.
don (rep: 1989) Jan 8, '19 at 11:12 am
Variatus, it worked perfectly!! Thank you! You also taught me a thing about the dates held in Excel... I never knew Excel dates as both time and date when inputting the data, so it does make sense that it didn't catch what I was trying to do.

On a side note, I believe I did change the formats in on both the Form sheet "E6" and in the Data sheet. I did this by using "Format cells", and choosing the option for "Date" in the format of m/dd/yy. Should I have done something different? 

Thank you both so much for your help!!! Now off to Part 8...hopefully it will work as smoothly as Parts 1-6 did. Thank you for the tutorials, they have really helped me. 
Elaine (rep: 4) Jan 8, '19 at 10:12 pm
I'm glad you got it working, Elaine. Don't thank me for the tutorials though. They are Don's brain child and work. On the date formats, you did the right thing, precisely. Just bear in mind that, when you look for a date in a column in the future, the Formula might be 43313 or 43313.6333 (if it's a date or time/date) or =TODAY() or =NOW() or =A17 (if it's a formula) or even "August 1, 2018" (if it's a text string). The Value property would be different depending upon the cell format except when it is Text. You can adjust the searchValue to match the format in the cell or v.v. and choose to look at either the Formula or the Value. It's really hard to make this bit idiot-proof :-)
Variatus (rep: 4889) Jan 8, '19 at 10:37 pm
Add to Discussion
0

Hi Elaine,

You need to change these lines:

searchValue = sourceSheet.Range("E6").Value

to

searchValue = sourceSheet.Range("E6").Formula

Change is highlighted in Bold.

And

LookIn:=xlValues

to

LookIn:=xlFormulas

Both changes are made in the Store_Update_Data macro.

Let me know if this works!

Discuss

Discussion

Hi Don, I made these changes and it still did not work. I feel that there should be something else in this section:

Else
'Input new record
'Get the next empty row from the Data sheet. 
nextRow=dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

It seems there should be something in this (bold) line that says to update if record exists, but offset to next line if it doesn't exist... but, in the tutorial, it works perfectly. I even went as far as copying the data exactly from the tutorial, just changing the cell references, but it still didn't work in my workbook. 

I'm going to watch the video again to see if I'm missing something.  

If you have any more thoughts, I'd greatly appreciate it. 

Thanks! 
Elaine (rep: 4) Jan 7, '19 at 10:35 pm
Elaine, it has to do with you using a Date for the unique column. Since I didn't anticipate that happening I didn't plan for it in the tutorial. I will re-download your workbook today, make the 2 changes I made in this Answer (make sure that it works), then re-upload it so you can see that and we can be on the same page. As there is a lot of code in the workbook, it could be easy to get mixed-up, so no worries.
don (rep: 1989) Jan 8, '19 at 11:09 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login