Autofill

[tuckje02]

I would like to fill a series with this forumla but only as long as the data set continues. I used
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[1],1)=""R"",MID(RC[1],4,4),LEFT(RC[1],4))"
This formula is set to be in B but I can't get it to fill down to go on to the next forumla.

Thanks in advance for your help!

 

[jbeaucaire]

You need to move away from the use of "activecell" and "select", stuff you get from the macro recorder because it recorded your actions. VBA doesn't need to do that, so it should be edited out when you can.

Assuming the data starts in row 2 and goes down some random number of rows..
Assuming that column C has data in it and can be used to "spot" the last row of data

Dim LR as Long

LR = Range("C" & Rows.Count).End(xlup).Row

Range("B2:B" & LR).FormulaR1C1 = _
    "=IF(LEFT(RC[1],1)=""R"",MID(RC[1],4,4),LEFT(RC[1],4))"

Not autofill needed this way, either, the formula is inserted into the entire range of cells all at once.

 

[tuckje02]

That totally works great! I really appreciate the help!

Can you kind of explain a little what you did so that I can do my own for the next formula and understand what I'm doing? I do have one at the end where the column to the right can't determine the end.

 

[jbeaucaire]

We pick a column, then look UP that column from the bottom of the spreadsheet to find that last cell with anything in it. That's anything, a formula or a value, even if the current result shows as blank.

Then we store that ROW number in the LR variable.

Then we insert that number via variable into the Range reference where we are applying a new formula.