CODEWITHSUNDEEP
vbaexcel
sql-n00b
sql-n00b

Reputation: 91

Excel - how to increment formula reference along a row

I have really long strings of text saved in roughly 1000 cells down Column A.

I have created the following formula (some VBA included for FindN), placed in cell B1:

=MID($A1,FindN("978",$A1,1),13)

I can copy this formula down Column B just fine. However, I also want to copy this formula across each row, so for example the formulas for the cells across the row should be as follows:

Cell C1: =MID($A1,FindN("978",$A1,2),13)

Cell D1: =MID($A1,FindN("978",$A1,3),13)

Cell E1: =MID($A1,FindN("978",$A1,4),13)

etc...

If I copy the formula in Cell B1 across the row, it will copy across =MID($A1,FindN("978",$A1,1),13) - but I need the "1" to increment by 1 each time.

I think I'd need to adjust the formula slightly, but a bit lost on how to do this...

Any help would be greatly appreciated. Please let me know if I should clarify further.

Upvotes: 2

Views: 1521

Answers (3)

Dawid
Dawid

Reputation: 786

You need use CELL formula to get current column number. Try something like this:

=MID($A1,FindN("978",$A1,CELL("column";A1)+1),13)

I dont have English Excel and im not sure first argument in CELL forumla is "column"

Upvotes: 1

citizenkong
citizenkong

Reputation: 679

Use COLUMN() - it gives the column number of the current cell. You can offset this as required.

In this case for your incrementing number use COLUMN() - 1, so that in B you have 1, C; 2 etc.

Upvotes: 2

R3uK
R3uK

Reputation: 14537

Try this :

Sub Fill()

With Sheets("Sheet1")
    For i = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
        .Cells(i, 1).FormulaR1C1 = "=MID($A1,FindN(" & _
                     Chr(34) & "978" & Chr(34) & _
                     ",$A1," & i - 1 & "),13)"
    Next i
End With

End Sub

Upvotes: 1

Related Questions