1

I have asked it here too: http://www.mrexcel.com/forum/excel-questions/796167-numbering-increments-1-1-n.html

I'm using a named cell, from that position, 13 positions to the right, I'd want to start numbering until it reaches the last cell in the column that's 2 positions from that same named cell. I ask because my table is going to change and having a named reference is good for that matter.

This is what I got after recording a macro and cleaning up its .Select, it still has a hard coded destination range.

Here's an illustrative example:

Sub Macro2()
    Range("endofheaders").Offset(0, 13).FormulaR1C1 = "1"
    Range("endofheaders").Offset(0, 13).AutoFill Destination:=Range("DZ6:DZ21"), Type:=xlFillSeries
End Sub

I hope this doesn't get too confusing, but the next code is what I used to have when I wasn't taking into account that my source table was going to change its size. It had hard coded ranges everywhere, which is why when I edited the table, it stopped working.

        With Range("EA6:EA" & Range("DN" & Rows.count).End(xlUp).Row)
            .Cells(1, 1).Value = 1
            .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
        End With

Thanks in advance.

L42
  • 19,427
  • 11
  • 44
  • 68
e561414
  • 21
  • 4
  • What is it that this code does not accomplish? The dynamic destination range? Also I'm having trouble connecting to your link, could you perhaps illustrate before and after data sample? – L42 Aug 04 '14 at 01:39
  • Yeah, the destination range is hard coded. Its initial position will always be in the same row, but not in the same column. Also, the range is gonna be smaller or larger, depending on the source data. – e561414 Aug 04 '14 at 04:26
  • And in what column do you base the last effective row? Or it could be any column with the most data? [Btw, this is how you get the effective last row.](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) Can you take it from here or do you need an example? – L42 Aug 04 '14 at 04:55
  • OK, I checked the link in your last post, it's not really what I want since it's still referencing a fixed column instead of a named cell, I was using that method in the past. Right now the column with the last effective row is located two columns to the right of the named cell. In reality, my named cell is in cell DM6, so it's from cell DO6 to its last row. That's gonna change when I add or remove columns, of course, that's why I use a named cell. I hope this makes sense and thank you for your time. – e561414 Aug 04 '14 at 05:02
  • That's clever using a named range :). Btw, I posted a possible solution to your issue. – L42 Aug 04 '14 at 05:19

2 Answers2

0

All I-column cell formulas can be written: =IF(INDIRECT("E" & ROW()) <> "", <formula>, "").
All P-column cell formulas can be written: =IF(INDIRECT("E" & ROW()) <> "", ROW() + 1, "").

If these need to be relative to your named reference they would be written as follows:
I-column: 

=IF(INDIRECT("R" & ROW() & "C" & COLUMN(EOH) + 1, FALSE)  <> "", <formula>, "")

P-column:

=IF(INDIRECT("R" & ROW() & "C" & COLUMN(EOH) + 1, FALSE)  <> "", ROW() + 1, "")

where EOH is your named reference.

Drag these two columns' formulas down 1000+ rows. Done.

usncahill
  • 469
  • 6
  • 16
  • Come see me if you want to get rid of that pesky "End of Headers" named reference. Hehe. – usncahill Aug 04 '14 at 01:48
  • Thank you. I'm sorry, I didn't understand what the "R" and the "C" are for, so when I tested it, it returned an error. As for the named reference, you're right, I wasn't quite content with its name, I'm gonna use yours. And I wanted to point out that I'd rather do it through VBA, since my intention is to make everything appear with a single button instead of having the formulas always there. – e561414 Aug 04 '14 at 05:13
  • "R" & "C" are part of the "R1C1" cell referencing method - as opposed to "A1" that is default in Excel. I actually find the "R1C1" method so much easier to work with because, like this answer, you can do maths on both the row and column references. It's much harder to do "G" + 5. – Enigmativity Aug 04 '14 at 05:31
  • I wasn't aware of that, but upon changing the setting to R1C1 in the options, these two formulas worked perfectly. I'm definitely going to use them in the future. Thanks, everyone. – e561414 Aug 04 '14 at 06:32
  • So this answered your question but wasn't chosen? Why choose a code you need to activate for the cells to change? Also, the named ranged is unnecessary, as much as people seem to love the idea. – usncahill Aug 04 '14 at 11:05
  • I wasn't familiar with the R1C1 method. And I'm using vba code because I'm going to hire cheap labour, so the workbook must be errorproof. The users will simply **fill the table and press a button**. The code will then rearrange the table in different ways to paste several hundred AutoCAD scripts that will draw tedious diagrams in just a second, instead of hours. I wanted to vote up your solution because it will be excellent for other projects, but I didn't have enough reputation, it seems. – e561414 Aug 05 '14 at 04:53
  • Understood. Glad you found the right solution for your application! – usncahill Aug 05 '14 at 21:40
0

Clever using a named range. Btw, try this:

Edit1: I've included the code if you want to stick to AutoFill (see the commented lines)

Sub Test()
    Dim rng As Range, rngtofill As Range
    Dim lrow As Long, myformula As String

    myformula = "your_formula"

    With Sheet8 '~~> Change to your actual sheet
        Set rng = .Range("endofheaders")
        lrow = .Range(Split(rng.Offset(0, 2).Address, "$")(1) _
            & .Rows.Count).End(xlUp).Row
        Debug.Print lrow
        Set rngtofill = .Range(rng.Offset(0, 13).Address, _
            Split(rng.Offset(0, 13).Address, "$")(1) & lrow)
        Debug.Print rngtofill.Address
        rngtofill.Offset(0, -7).FormulaR1C1 = myformula
        rngtofill.Formula = "=ROW(A1)"
        rngtofill.Value = rngtofill.Value
        'rngtofill.Resize(1, 1).FormulaR1C1 = "1"
        'rngtofill.Resize(1, 1).AutoFill rngtofill, xlFillSeries
    End With
End Sub

I did another approach in doing the numbering.
The extra lines with Debug.Print is just to check if I get the right values.
If you want to use the AutoFill, it's up to you.
I have commented it in the code. HTH.

L42
  • 19,427
  • 11
  • 44
  • 68
  • The numbering is happening, but the formula isn't filling. Maybe I'm doing something wrong, give me a sec. – e561414 Aug 04 '14 at 05:30
  • @e561414 Which formula isn't happening? – L42 Aug 04 '14 at 05:32
  • Ooooh, you're right I only talked about the formula in the link that you couldn't visit. Hehehe, sorry. If this code is solely for the numbering, then it's completely perfect. BTW, what if I wanted to use the same method to paste a formula? forget about the numbering, does that belong to another thread or can I change something in this code to fit that need? – e561414 Aug 04 '14 at 05:40
  • @e561414 oh ok. What is the formula and where do you want to put it? As in illustrated data it is 6 offset from your *endofheaders*. Btw, yes. See how I put the formula `=ROW(A1)`? Well that is entirely the reason why I used that approach. Cool right? :D – L42 Aug 04 '14 at 05:44
  • @e561414 I don't get your formula but see my edit on how you'll put it in the code. – L42 Aug 04 '14 at 06:02
  • Everything is working now; your code fills both the formula and the numbering. Fantastic, thanks a lot, man. Yeah, it's VERY cool. – e561414 Aug 04 '14 at 06:02