[Closed] Microsoft excel guru, help?

Unusual request - Rob likes as fiddly challenge

Stick this in the code module behind the input sheet (only works once - e.g. enter number, close workbook, open workbook to re-set):

Option Explicit
Public i As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Old_B1_Value, New_B1_Value As Integer

If i = 1 Then Exit Sub
With Sheet1
Old_B1_Value = Range("B1")
New_B1_Value = Old_B1_Value + Range("A1")
End With

i = i + 1

Range("B1") = New_B1_Value
End Sub

HTH

Edit: And, of course, format and annotate it properly 😉

And then it occurred to me - there's a couple of unnecessary lines in there - use this instead:

Option Explicit
Public i As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Old_B1_Value, New_B1_Value As Integer

If i = 1 Then Exit Sub

Old_B1_Value = Range("B1")

New_B1_Value = Old_B1_Value + Range("A1")

i = i + 1

Range("B1") = New_B1_Value
End Sub

Dim Old_B1_Value, New_B1_Value As Integer

Doesn't this make the first variable a Variant value type and the second an integer?

I thought you had to use the following format

Dim variableOne as integer
Dim variableTwo as integer

to ensure the correct value type for variables.

RobHilton's solution will work but I confess I don't like what you are doing. If someone types in the wrong number and then tries to change it back everything will get confused very quickly indeed and you will end up having to go back to old records to calculate. Much nicer would be to have a separate worksheet with all the daily records in it so you have a proper history and can make calculations based on that.

If you wanted to keep the front form simple so the user doesn't see the history then you could use a solution like this: http://www.contextures.com/exceldataentryupdateform.html
There appears to be a lot of code but if you look at it you will see it is all quite simple.

If you really want to keep a running total then you might be better using a button rather than detecting when the cell changes and then you can also store the last 10 values so you can 'undo' if someone gets it wrong.

Thank you all, you're all a star. 🙂 ...i need this to make my life easier keeping track of my daily audits but my excel skills are less than elementary... Figures from this input i will need to show me the remaining number of locations to audit in a monthly cycle count and would also show me the average x amount of location per day task so i can keep up the monthly cycle. (Then i have perfect estimation of #of hours spare to spend that time chilling out in this forum while waiting to clock out 😛 )

@leffeboy .. That actually occur to me, what if i put a wrong digit and how would i take it back.. Guess daily daily record on different tab would be wise.. Eventhough this is my personal spreadsheet. Cheers for the link... Looks very educational.

@rob ..this is going to make me a rockstar 8) owe u bud

If it's just for you then don't bother with that link that I sent, just keep daily records on a separate tab. If you are doing several tasks in one day then you can record the name of the task and the number of hours as well then run number of hours per task etc. Time to learn pivottables http://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel

JulianA - Member
Dim Old_B1_Value, New_B1_Value As Integer
Doesn't this make the first variable a Variant value type and the second an integer?

Sure does - good spot. Not done any of this for a few years and got confused with declarations in something else.

RobHilton's solution will work but I confess I don't like what you are doing

I just do what I'm asked without questioning the wisdom 🙂

Excel is the wrong tool for the job.

Excel is [i]almost always[/i] the wrong tool for the job. 😉

Pen & paper would probly do it.

Hi rob, where is the code module behind the input sheet? 😳 i thought its just typing the command in a cell like a vlookup function 🙁

Time to stop right there, you mentioned the word AUDIT, what exactly are you auditing, is it legal or safety - if so more robust solution needed. If it's for quality etc. probably a more robust solution needed.

as with any of these whats the problem you are trying to solve not here is the idea I think will work to fix it.

[grumpy bloke who deals with peoples tech support requests a lot]

😆 😆 😆

Nah.. Nothing spectacular Just mobile phones from major mobile networks..dealing with returns/insurance replacements etc etc... Very messy too many phone ...when i say many it means you wont believe just how many. So need to be on top of it.

my best guess (and preferred method with most record keeping) is to have separate data input sheets/areas for the daily capture for each month and then collate the info into a master view.

Sometimes simple is the better way, if you don't know VB stay out of it on something live and get your head round it on something else first.

Hi rob, where is the code module behind the input sheet?

If you still want to know then:

With Excel open 🙂 press Alt+F11 (opens scripting environment), on left double click on the sheet name the code is to run on, paste code from up there ^ into the main window.

Alt+q returns you to Excel. Save workbook. Will run from now on.

Hi rob, cheers.. Been waiting for this actually.. From my example above what cell should i put that script? A1 or b1. Or any?

You could have emailed - come to think of it I've changed email address though... 🙂

Didn't understand your question - mail me (have updated).