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

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?

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.

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

Select allOpen in new window

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. . . . .

Brilliant. I will just have to learn some VBA!
Many thanks. Neat and quick.

Very quick, very good.

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

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

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

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?

Oh, and I have a question; can the P4:P201 range expand or shrink over time?

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.

I definitely think i need to send you the whole spreadsheet. Would that be OK?

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.

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

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?

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

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.

I'm sorry but I think you'll need to get someone who is more familiar with formulas to fix this for you.

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