Autofill or Macro? And how?

Y

YMS_1975

New Member
Joined
Feb 19, 2009
Messages
10
I've created what I was hoping to be a simple spreadsheet. It's gotten a bit complicated.

I'm trying to accomplish two things.

FIRST

Every 11th row I want to have the next sequential date :

A3 = June 11, 2011
A13 = June 12, 2011
A23 = June 13, 2011 and so on....


SECOND

Cell H3 : =D3-(E3+F3+G3)
Cell H4 : =D4-(E4+F4+G4)+H3
Cell H5 : =D5-(E5+F5+G5)+H4
Cell H6 : =D6-(E6+F6+G6)+H5
Cell H7 : =D7-(E7+F7+G7)+H6
Cell H8 : =D8-(E8+F8+G8)+H7
Cell H9 : =D9-(E9+F9+G9)+H8
Cell H10 : =D10-(E10+F10+G10)+H9
Cell H11 : =D11-(E11+F11+G11)+H10
Cell H12 : =D12-(E12+F12+G12)+H11


Every 11th row, the above pattern starts again and so on :

Cell H13 : =D13-(E13+F13+G13)
Cell H14 : =D14-(E14+F14+G14)+H13
Cell H15 : =D15-(E15+F15+G15)+H14
Cell H16 : =D16-(E16+F16+G16)+H15
Cell H17 : =D17-(E17+F17+G17)+H16
Cell H18 : =D18-(E18+F18+G18)+H17
Cell H19 : =D19-(E19+F19+G19)+H18
Cell H20 : =D20-(E20+F20+G20)+H19
Cell H21 : =D21-(E21+F21+G21)+H20
Cell H22 : =D22-(E22+F22+G22)+H21

How do I autofill or use a macro to ensure this goes all the way down to the last row 1048576?????? :confused:
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
YMS_1975 said:
How do I autofill or use a macro to ensure this goes all the way down to the last row 1048576?????? :confused:
Click to expand...

You're serious about filling an entire worksheet like that? What's the use of it?
 
Upvote 0
Code: 
Sub MrExcel_556521()

Application.ScreenUpdating = False

Dim i As Long

Cells(3, 1) = Date
Cells(3, 8).FormulaR1C1 = "=RC[-4]-SUM(RC[-3]:RC[-1])"

For i = 4 To Rows.Count

Application.StatusBar = "Updating row " & i & " ..."

    If Right(i, 1) = 3 Then
        Cells(i, 1).FormulaR1C1 = "=OFFSET(RC,-10,)+1"
        Cells(i, 8).FormulaR1C1 = "=RC[-4]-SUM(RC[-3]:RC[-1])"
    Else
        Cells(i, 8).FormulaR1C1 = "=RC[-4]-SUM(RC[-3]:RC[-1])+R[-1]C"
    End If

Next i

With Application
    .ScreenUpdating = True
    .StatusBar = False
End With

End Sub
 
Upvote 0
wigi said:
You're serious about filling an entire worksheet like that? What's the use of it?
Click to expand...

It's for employment purposes.

I do piecework (commission) type of work everyday. I get like 8 - 10 calls max. and I wanted to be able to calculate how much I'm making each day before calling it quits. If I want, I can stay out and continue to work. If I feel comfortable with what I'm making (net) then I'll call it a day. I alloted 10 calls (rows) for each day. I calculate the earnings, less my deductions and I've got what I need.

Plus it's good for logging purposes. I didn't want too many files (for each day or month). I just wanted one file, one worksheet. :)
 
Upvote 0
YMS_1975 said:
It's for employment purposes.

I do piecework (commission) type of work everyday. I get like 8 - 10 calls max. and I wanted to be able to calculate how much I'm making each day before calling it quits. If I want, I can stay out and continue to work. If I feel comfortable with what I'm making (net) then I'll call it a day. I alloted 10 calls (rows) for each day. I calculate the earnings, less my deductions and I've got what I need.

Plus it's good for logging purposes. I didn't want too many files (for each day or month). I just wanted one file, one worksheet. :)
Click to expand...

But that doesn't explain why you need more than a million rows, unless you seriously expect 10 calls every single day for the next 287 years!!!
 
Upvote 0
njimack said:
But that doesn't explain why you need more than a million rows, unless you seriously expect 10 calls every single day for the next 287 years!!!
Click to expand...

It shouldn't make a difference anyways right? (Or am I wrong?)

I just figured that I'll create it, max out the spreadsheet and not have to worry about limits or or copying and pasting to extend it when I get to the end.

Overkill? :confused:
 
Upvote 0
Indeed, njimack.

YMS_1975, it also means you will be entering calls in a sheet that will save as slow as a crawl... ever recalculated or worked with such "worksheets"?
 
Upvote 0
You could set up your table like this.

The same formula in cell A4 can be copied down in column A as well as the same formula in column H.

Formula in Cell A4:
=IF(MOD(ROW()-3,10)=0,A3+1,A3)

Formula in Cell H3:
=IF(MOD(ROW()-3,10)=0,D3-SUM(E3:G3),H2+D3-SUM(E3:G3))

Here is how it would look...

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Col2</td><td style="font-weight: bold;;">Col3</td><td style="font-weight: bold;;">Val1</td><td style="font-weight: bold;;">Val2</td><td style="font-weight: bold;;">Val3</td><td style="font-weight: bold;;">Val4</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Balance</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-bottom: 1px solid black;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1906</td><td style="text-align: right;;">74</td><td style="text-align: right;;">73</td><td style="text-align: right;border-right: 1px solid black;;">94</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">1665</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1631</td><td style="text-align: right;;">94</td><td style="text-align: right;;">26</td><td style="text-align: right;;">83</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFF99;;">3093</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1268</td><td style="text-align: right;;">68</td><td style="text-align: right;;">77</td><td style="text-align: right;;">82</td><td style="text-align: right;background-color: #FFFF99;;">4134</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1966</td><td style="text-align: right;;">62</td><td style="text-align: right;;">60</td><td style="text-align: right;;">8</td><td style="text-align: right;background-color: #FFFF99;;">5970</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1528</td><td style="text-align: right;;">28</td><td style="text-align: right;;">32</td><td style="text-align: right;;">72</td><td style="text-align: right;background-color: #FFFF99;;">7366</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1739</td><td style="text-align: right;;">46</td><td style="text-align: right;;">88</td><td style="text-align: right;;">71</td><td style="text-align: right;background-color: #FFFF99;;">8900</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1789</td><td style="text-align: right;;">22</td><td style="text-align: right;;">89</td><td style="text-align: right;;">69</td><td style="text-align: right;background-color: #FFFF99;;">10509</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1449</td><td style="text-align: right;;">79</td><td style="text-align: right;;">47</td><td style="text-align: right;;">10</td><td style="text-align: right;background-color: #FFFF99;;">11822</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1620</td><td style="text-align: right;;">72</td><td style="text-align: right;;">68</td><td style="text-align: right;;">99</td><td style="text-align: right;background-color: #FFFF99;;">13203</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1878</td><td style="text-align: right;;">50</td><td style="text-align: right;;">68</td><td style="text-align: right;;">47</td><td style="text-align: right;background-color: #FFFF99;;">14916</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #CCFFCC;;">6/2/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1161</td><td style="text-align: right;;">58</td><td style="text-align: right;;">16</td><td style="text-align: right;;">86</td><td style="text-align: right;background-color: #FFFF99;;">1001</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;background-color: #CCFFCC;;">6/2/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1582</td><td style="text-align: right;;">49</td><td style="text-align: right;;">43</td><td style="text-align: right;;">84</td><td style="text-align: right;background-color: #FFFF99;;">2407</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;background-color: #CCFFCC;;">6/2/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1209</td><td style="text-align: right;;">61</td><td style="text-align: right;;">66</td><td style="text-align: right;;">80</td><td style="text-align: right;background-color: #FFFF99;;">3409</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
Let us know if this works for you.

Gary
 
Upvote 0
wigi said:
Indeed, njimack.

YMS_1975, it also means you will be entering calls in a sheet that will save as slow as a crawl... ever recalculated or worked with such "worksheets"?
Click to expand...

I have actually. The save & the opening/load time is not a concern. When I'm on the road, I open it up (it stays open), I enter in my data, click save and move. It can save until my next call for all I care. The truth is, I work so hard each day ; sometimes up to 15 hours a day. I don't have time to be tinkering around with spreadsheets. What time I have off I want to relax and not worry about work. Today I'm off, so I thought I'd take care of it.
 
Last edited:
Upvote 0
GaryWMn said:
You could set up your table like this.

The same formula in cell A4 can be copied down in column A as well as the same formula in column H.

Formula in Cell A4:
=IF(MOD(ROW()-3,10)=0,A3+1,A3)

Formula in Cell H3:
=IF(MOD(ROW()-3,10)=0,D3-SUM(E3:G3),H2+D3-SUM(E3:G3))

Here is how it would look...

Excel 2003<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Col2</td><td style="font-weight: bold;;">Col3</td><td style="font-weight: bold;;">Val1</td><td style="font-weight: bold;;">Val2</td><td style="font-weight: bold;;">Val3</td><td style="font-weight: bold;;">Val4</td><td style="font-weight: bold;border-bottom: 1px solid black;;">Balance</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-bottom: 1px solid black;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1906</td><td style="text-align: right;;">74</td><td style="text-align: right;;">73</td><td style="text-align: right;border-right: 1px solid black;;">94</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">1665</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;border-left: 1px solid black;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1631</td><td style="text-align: right;;">94</td><td style="text-align: right;;">26</td><td style="text-align: right;;">83</td><td style="text-align: right;border-top: 1px solid black;background-color: #FFFF99;;">3093</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1268</td><td style="text-align: right;;">68</td><td style="text-align: right;;">77</td><td style="text-align: right;;">82</td><td style="text-align: right;background-color: #FFFF99;;">4134</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1966</td><td style="text-align: right;;">62</td><td style="text-align: right;;">60</td><td style="text-align: right;;">8</td><td style="text-align: right;background-color: #FFFF99;;">5970</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1528</td><td style="text-align: right;;">28</td><td style="text-align: right;;">32</td><td style="text-align: right;;">72</td><td style="text-align: right;background-color: #FFFF99;;">7366</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1739</td><td style="text-align: right;;">46</td><td style="text-align: right;;">88</td><td style="text-align: right;;">71</td><td style="text-align: right;background-color: #FFFF99;;">8900</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1789</td><td style="text-align: right;;">22</td><td style="text-align: right;;">89</td><td style="text-align: right;;">69</td><td style="text-align: right;background-color: #FFFF99;;">10509</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1449</td><td style="text-align: right;;">79</td><td style="text-align: right;;">47</td><td style="text-align: right;;">10</td><td style="text-align: right;background-color: #FFFF99;;">11822</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1620</td><td style="text-align: right;;">72</td><td style="text-align: right;;">68</td><td style="text-align: right;;">99</td><td style="text-align: right;background-color: #FFFF99;;">13203</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;background-color: #CCFFCC;;">6/1/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1878</td><td style="text-align: right;;">50</td><td style="text-align: right;;">68</td><td style="text-align: right;;">47</td><td style="text-align: right;background-color: #FFFF99;;">14916</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;background-color: #CCFFCC;;">6/2/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1161</td><td style="text-align: right;;">58</td><td style="text-align: right;;">16</td><td style="text-align: right;;">86</td><td style="text-align: right;background-color: #FFFF99;;">1001</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;background-color: #CCFFCC;;">6/2/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1582</td><td style="text-align: right;;">49</td><td style="text-align: right;;">43</td><td style="text-align: right;;">84</td><td style="text-align: right;background-color: #FFFF99;;">2407</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;background-color: #CCFFCC;;">6/2/2011</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">1209</td><td style="text-align: right;;">61</td><td style="text-align: right;;">66</td><td style="text-align: right;;">80</td><td style="text-align: right;background-color: #FFFF99;;">3409</td></tr></tbody></table>
Sheet1​



Let us know if this works for you.

Gary
Click to expand...

Gary,

Thanks for the suggestion. I copied and pasted your formula (didn't manually type it in) but it's not working. Not sure why. And the other formula didn't work either. It just sat there and did nothing. :confused:

Ugh....this is becoming a nuisance. I didn't want to do the multiple file/worksheet option, but I guess that's probably the better way to do it.

Same idea, but I've created a worksheet for each month now so it's not freezing/crashing (although I've never had that problem before, and that was using Excel 2003; I'm using 2010 now).

I'll start with the January worksheet, and then copy it to the other months/worksheets.

CELL A3 : January 1, 2011
CELL H3 : =D3-(E3+F3+G3)
CELL H4 : =D4-(E4+F4+G4)+H3
CELL H5 : =D5-(E5+F5+G5)+H4
CELL H6 : =D6-(E6+F6+G6)+H5
CELL H7 : =D7-(E7+F7+G7)+H6
CELL H8 : =D8-(E8+F8+G8)+H7
CELL H9 : =D9-(E9+F9+G9)+H8
CELL H10 : =D10-(E10+F10+G10)+H9
CELL H11 : =D11-(E11+F11+G11)+H10
CELL H12 : =D12-(E12+F12+G12)+H11

CELL H13 : =D13-(E13+F13+G13) <---- A new day begins
CELL A13 : ????????????????????????????? (Should be January 2, 2011)

I'd like to post 31 days to each month (regardless if there's 28/30/31).
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Input Form
Replies
2
Views
323
Dokman
D
E
  • Solved
Identify product that have different values with a spilled formula
Replies
3
Views
82
ED38
E
Jeffrey Mahoney
  • Solved
Get Items From Descriptions Based on List
Replies
5
Views
403
Fluff
Fluff
R
  • Question Question
CELL FILL COLOR NOT HOLDING ON WORKSHEET
Replies
1
Views
325
Joe4
Joe4
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,545
Messages
6,179,432
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top