Record Changing Value for Multiple Cells

Thank you for the help, it works. The only problem encountered is that now it does not recognize if it is a reference value, it makes the change only if it's entered manually.

Ok, how is it entered if not manually?

You specifically stated "When I change the value in AG5", so I assumed that's what you was doing??

Yes, you are right. What I meant to say was the value is changed by a formula.

Oh, ok.. I will try and help you more tomorrow, or maybe later tonight (that's unlikely, but maybe..).. I got a lot going on right now, but I will get back to this if nobody else does.

Thank you for your time.

Is your formula in a specific cell that we can use in the code, or could it be anywhere?

It could be anywhere (at times the values are entered from a website)

What is the max number of rows you will use in column AG? Just an estimate is fine

Maybe 30 to 45

asac,

Ok, since the Change event does not run when a formula changes the cell, you will have to get rid of the Change event code and use the Calculate event. There is 1 drawback but hopefully it will not interfere. You will have to pick 1 column in your worksheet that will never be used. Doesn't matter which one.. could be column 1 or the last column in the entire sheet (I don't know what that is.. lol) but that column will be used to store the last value you entered so the code will know whether to add a new value to the row or not. You can hide the column if you want or whatever. You will never need to see it, but it will have to be there. There is no way to make the Change event run when the cell is changed by a formula.
Here is the code. Currently it uses the cell 1 to the left of AG but you can change that to any column you want like I said. There is a comment on that line. I hope you are able to use this. It will have to be changed to fit your needs, but it works perfectly for the exact question you asked.

Please Login or Register  to view this content.

Oh, I missed telling you something. There are 2 lines you need to change to the column you want. Sorry about that.. Here is the code again with comments on the 2 lines:

Please Login or Register  to view this content.

Hope this works for you!

Jeez... I'm not batting a hundred today...lol not a big deal but you can get rid of this line:

Set r = r.Offset(1, 0)

Thank you very much!!! I have been playing with it, but it's been limiting the number of records (only 2) and it's storing the values one cell below all the time. I have been playing with the offset values, but it does not work yet. I will keep on trying. But I do thank you for helping me.

Your'e welcome!, it's hard to tell what's going on with me not knowing how your sheet is set up, but it sounds like you got the offsets wrong. In my code the first number will need to be 0 (that's the row) and the second number will need to be how far to the right or left (which column). Here is the sample worksheet I'm using. There is a simple formula in column AG down to row 20. If you put a value in column Z, the formula will update to match and the code will run and do what you asked. I'm sure your workbook is more complex and there might be something else going on that I don't know about, but it works good in this workbook. Maybe you can look at it and figure out why yours is not working.

It's perfect!!!! Thank you again for your time. Best Regards!!!

You're welcome! I'm very glad it worked! If you get a moment, it would be really awesome if you could click on the 'Add reputation' link at the bottom of one of my answers. I just started trying to help here and trying to get my reputation built up. Either way.. I'm happy it worked. That makes me feel good!

Just a brief question:
At [ If r.Offset(0, -1).Value <> r.Value Then ' Change this offset to match the one below] It gives the ‘Run-time error ‘13’: Type mismatch’ is it my offset value that causes the error? Thanks again.

Im not in front of my computer today but the offset value is not the problem i doubt think. You could try deleting the value in that column that keeps track of the last value entered.. that would be column AF if you're using my exact offsets... and see if you still get the error. I can't do much today but probably tomorrow morning? What value are you entering when this happens?

Sorry I've been so long. I wrote up a long post and couldn't post it a few days ago. It kept giving me an error saying there was illegal SQL code or something like that. I even made a post about not being able to post to you. But I think with some help from the moderators, I figured it out. So here goes again..
This is a very difficult situation when you start involving API's that change values of a cell that changes the result of a formula, then have code that will run only when the formula cell changes. I don't think I, or anybody will be able to figure this out without actually having a sample workbook that works on their computer with whatever API's you are using. I don't know how your workbook is set up so I don't really know what to do to make this work. Every time I come up with a solution you add something I didn't know about.. which I'm not upset about that, I used to do it too many years ago when I was the one asking questions because I didn't think the details mattered and I'd figure it out.. lol.. but sometimes they do matter.

Just in case this works, you can try adding this line of code to the worksheet change event:
Target.Select

so you end up with this in that worksheet's module:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Select

End Sub

Whatever cell is the last cell to change will be selected though when any code runs that causes cells to change, whether you want it to or not..

Sorry I couldn't help more, but I don't know what else to do without having a sample workbook that I can work with. I'm not getting my hopes up, but let me know if that little fix works!