Updating a progress claim in excel

[chiron]

I would like to figure out a way to parse through an excel sheet and replace cell values with a formula. I'm guessing this is done with a macro but I've never created macros on my own and so I am not sure how to start.

In the example I want to check if cell A10 is a number in which case, D10 to be replaced with the value of D10+E10, E10 to be replaced with zero and H10 to be same as G10.

Posting screenshots as I can't attach files:

 

[Col Delane]

Aren't all the values (Account Codes?) in column A numbers, so why the need to to test them?
Why would some Account Codes be numbers and others not?

If all the cells in column D:J holds values (vs formulas) then the simple way to achieve what you're after is:

1. Build a table of helper cells to the right of your current table that link by formula to the row (Account Code) values you need to manipulate. That is:
2. Link to D, Link to E, Sum of [Links to D+E], A column of zeroes, Link to G.
3. Then when it comes time to "roll-over" your Budget:
   1. Copy [Sum of Links to D+E : A column of zeroes] and PasteSpecialValues into original D6:E6
   2. Copy [Link to G] and PasteSpecialValues into [original H6]

 

[chiron]

I cannot change the way the sheet is set up because my company likes it that way. So I have to go through each section and do the paste special and do it for several files a day. Even if I have helper cells I still have to do the copy paste actions several times. In google sheets I could do this easier by making multiple selections and pasting but Excel's paste action does not work that way.

What I want to do is parse through a sheet and update the numeric values in the cells based on a formula.

 

[chiron]

I guess it is something like;

Sub UpdateClaim()
For i = 1 To 1000
If ActiveSheet.Cells(i, 2).Value > 0 Then
ActiveSheet.Cells(i, 5).Value = ActiveSheet.Cells(i, 5).Value + ActiveSheet.Cells(i, 6).Value
ActiveSheet.Cells(i, 6).Value = 0
ActiveSheet.Cells(i, 12).Value = ActiveSheet.Cells(i, 10).Value
End If
Next i
End Sub

Now, how to get make it a shortcut button and apply easily into my sheets.

 

[Col Delane]

I cannot change the way the sheet is set up because my company likes it that way. So I have to go through each section and do the paste special and do it for several files a day. Even if I have helper cells I still have to do the copy paste actions several times. In google sheets I could do this easier by making multiple selections and pasting but Excel's paste action does not work that way.

Are you saying you can or cannot have helper cells?
IF you can, then you could do what I suggested and automate the copy/paste with a simple macro linked to a button on the QAT.

 

[chiron]

Are you saying you can or cannot have helper cells?
IF you can, then you could do what I suggested and automate the copy/paste with a simple macro linked to a button on the QAT.

Yeah, figured it out now. I can't change anything in the visible part of the sheet even though I'd have prefered not to have all the subtotals in-between like that.

 

[Col Delane]

There is no shortage of real estate in an Excel sheet (16300+ columns! ), so you can easily locate helper cells/columns well away from any section that needs to be viewed by others, and hide them if necessary (whether via Format / Hide & Unhide [NOT my preference] or Grouping under an Outline (better as easily identifiable and quick to show/hide).

 

[Sajan]

Hey LinkedIn fam! I'm diving into the world of Excel macros and need some guidance. I want to automate a process in my spreadsheet where, for instance, if cell A10 is a number, I'd like to replace D10 with the sum of D10 and E10, set E10 to zero, and make H10 identical to G10.

I'm guessing this involves creating a macro, but honestly, I'm a bit of a newbie here. Has anyone tackled something similar and can share insights or tips on how to get started?