Inserting a value in the first non-zero cell in a column

I want to insert a value from a cell (say A1) into another cell. This 'other cell' is to be the first non-zero cell working downwards in a column of cells (all of which are numbers), most (but not all) of which will contain zero. The values in the column are all derived from formulae. I want the value in cell A1 to be inserted into that first non-zero cell in the column, replacing the zero with the value in cell A1, without affecting anything else above or below it in the column. Can this be done without using VBA? If it has to be VBA, then how?
Thanks.
BlosMusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If I understand you correctly you want to insert the value from A1 into, say, A15and the current zero in A15 is derived from a formula. Placing A1'a value into A15 would remove the formula in A15, Do you want to do that?
0
BlosMusicAuthor Commented:
Yes, that's fine, except that A15 (say) wouldn't be zero: it would be >0. I just want to put the value, by which I mean the actual value, as a number, that is in cell A1, into the first cell working downwards in the column, the first cell that isn't zero. In doing so, the formula currently in that cell (as you say, say A15), which is currently deriving a value >0 will be overridden. That's not a problem.
0
Martin LissOlder than dirtCommented:
OK here's a macro.

Sub ReplaceNonZero()
Dim lngRow As Long

With ActiveSheet
    For lngRow = 2 To .UsedRange.Rows.Count
        If .Cells(lngRow, "A").Value > 0 Then
            .Cells(lngRow, "A").Value = Range("A1").Value
            Exit For
        End If
    Next
End With
End Sub

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

BlosMusicAuthor Commented:
Martin,
Whoops. I think I got what I wanted to ask a bit wrong. Sorry.
What I really meant was - how do I add the number in cell E215 (not A1) to the first cell in a column (P4:P201) that is the one immediately AFTER the first non-zero cell in that column? Is that too complicated or impossible?
Sorry about the confusion. . . . .
0
Martin LissOlder than dirtCommented:
Sub ReplaceNonZero()
Dim lngRow As Long

With ActiveSheet
    For lngRow = 4 To 201
        If .Cells(lngRow, "P").Value > 0 Then
            .Cells(lngRow + 1, "P").Value = .Cells(lngRow + 1, "P").Value + Range("E215").Value
            Exit For
        End If
    Next
End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlosMusicAuthor Commented:
Brilliant. I will just have to learn some VBA!
Many thanks. Neat and quick.
0
BlosMusicAuthor Commented:
Very quick, very good.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you and/or help you learn VBA including these two new ones.
Creating your own Excel Formulas and doing the impossible
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
BlosMusicAuthor Commented:
Marty,
I will link to your articles for sure. Also, if I were to send you the full spreadsheet, could you help me further? The solution you gave was absolutely perfect, but I realize that the problem I have is a little more complex still.
Let me explain.
The figure in cell E215 (say it's value is 8) is a figure that goes into the cell immediately after the first non-zero in column P4:P201, as you've done it; but then, after I do some other things elsewhere that add to cell E215, to make it (say) 17, I need that extra amount of 9 (17-8=9)  to be put in the next available zero-value cell going downwards.
This might seem an unnecessary thing to do, but if you saw my spreadsheet you'd understand. I'm not asking you to do my work for me, but I am really out of my depth here!
Let me know. In the meantime I shall look at your links.
Regards,
Richard
0
BlosMusicAuthor Commented:
Marty,
Again I've said it wrong! You can give up on me if you want! Let me start again.

The figure in cell E215 (say it's value is 8) is a figure that goes into the cell immediately after the first non-zero in column P4:P201, as you've done it; but then, after I do some other things elsewhere that add to cell E215, to make it (say) 17, I need that extra amount of 9 (17-8=9)  to be added to the figure that is currently in the cell immediately below the first non-zero cell in the column. In other words, what you did is absolutely correct, but as I keep adding value to cell E215 I want that extra amount to be added to the cell we are talking about in the column. Does that make sense?
R
0
Martin LissOlder than dirtCommented:
Isn't that the same as saying that you want the cell after the first non-zero cell in P4:P201 to always be equal to E215?
0
Martin LissOlder than dirtCommented:
Oh, and I have a question; can the P4:P201 range expand or shrink over time?
0
BlosMusicAuthor Commented:
Let me think about what I really want to achieve - I don't want to waste your time until I have decided EXACTLY what I want to do. I do know what I want to do, but i need to take a step back and write a coherent question that you could (I hope!) answer in one go rather than me keep rethinking it!
I hope that's OK. Maybe I should send you the whole spreadsheet.
0
BlosMusicAuthor Commented:
I definitely think i need to send you the whole spreadsheet. Would that be OK?
0
Martin LissOlder than dirtCommented:
Sure, attach the spreadsheet and explain the process the best you can, perhaps by first recording a macro while you do it manually. I may eventually ask you to post a new question but in any case I'm happy to help.
0
BlosMusicAuthor Commented:
Thanks very much. I am very grateful to you. I don't expect you to do my work for me, but this is taxing me!
On worksheet "Timesheet entries", I (or rather our data-entry clerk) can put in times spent on a job, by using drop-down boxes for the names, and drop-down boxes for the projects. Then putting in the hours - with a default for "Working hours", but with options for Sunday working, Statutory Sick Pay [SSP] and holidays - the Employee number and Project Number go in automatically, and the output goes to various places. I get cumulative hours for each employee, with ordinary hours, time-and-a-half and double time, plus SSP and holidays shown on the other side of the "Timesheet entries" worksheet (headed 'Timesheet Output'); cumulative hours (not bothering with whether or not the hours are normal, time-and-a-half or double time) for any project (worksheet "Project Times"), and then an output for each employee on a separate worksheet (because I finally have to output data in .csv format for our accountant's system, one lot of data for each employee). The most important bit is this last bit, and that's what I need help with. The other stuff I've described is useful, and I want it; but the separate worksheets for each employee are the essential thing.
When hours are entered on the "Timesheet entries" worksheet, they accumulate until 40 hours is reached (no matter which project), at which time any hours (unless Sunday working [double-time] is selected) will be automatically sent forward as overtime [time-and-a-half]. This works on the "Timesheet entries" worksheet OK, but not on the individual Employee's worksheets (the ones with A Jerrum .csv, &c. - note that these are not in .csv format but they will be the basis for .csv stuff I'll sort out later). On the individual worksheets at row 215 you will see three summations: one for total hours, one for the maximum normal hours (if they are reached) and one for overtime (time-and-a-half). I then want the overtime to go up to the individual projects on these so-called "Employees .csv" worksheets, and set against the actual project. The actual project appears automatically if any hours have been worked on it. I just want it to be that any hours over the first 40 worked are set against the relevant category in column P (the value being put in column O). Once we go over 40 hours, then any hours worked are shown as "Overtime" in column P, next to the relevant project as entered on the "Timesheet entries" worksheet. It works for the first project entered on the "Timesheet entries" worksheet, but not thereafter (for fairly clear reasons). But I don't know how to do what you have perfectly done for the first project (which could, of course, be anywhere in column Q.
Phew!
If you check out my spreadsheet, you can play with employee and project entries, and I think you'll see what I mean.
Sorry about this lengthy message, but I needed to try to explain it clearly. I hope I have succeeded!
Input-hours-from-timesheets--Sylvia.xlsm
0
Martin LissOlder than dirtCommented:
I'm working through your detailed explanation and I'm at the part where you mention the hours in column P of the Timesheet entries sheet. Those cells contain a formula that refers to column E on the Drop-down data sheet. I'd like to see what is in that column but it's hidden and the worksheet is protected so I can't. Can you tell me the password or send it to me in an EE message?
0
Martin LissOlder than dirtCommented:
It works for the first project entered on the "Timesheet entries" worksheet, but not thereafter (for fairly clear reasons).
Sorry, but while the earlier part was fairly clear the above isn't.

In the attached workbook I added some hours for Mr. Short. Let me know what should be different.
28818959.xlsm
0
BlosMusicAuthor Commented:
Password is ereaec72. What I meant was that what you did earlier separates the overtime from the ordinary hours on the first project, but it needs to work for any project going down the column.
For Mr Short - or indeed for any of the employees - I need their overtime (anything >40 hours) to appear next to the relevant project they were working on on their specific worksheet. So for Mr Short, he's worked 50 hours on Anaergia, and the 15 hours needs to show as Overtime, not Standard hours on his worksheet. Anything anybody does >40 hours needs to be overtime (unless it's Sunday working, which works separately), and therefore whatever anybody does that is >40 hours needs to be shown as Overtime against every new job he works on. Hope that's clearer.
I really appreciate your help in this, Martin.
0
Martin LissOlder than dirtCommented:
I'm sorry but I think you'll need to get someone who is more familiar with formulas to fix this for you.
0
BlosMusicAuthor Commented:
OK Martin, but thanks anyway for your input. You have helped a great deal, and I've learnt much. I think I may be over-complicating it all!
R
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.