2

I have a current Sub that organizes data certain way for me and even enters a formula within the first row of the worksheet, but I am running into the issue of wanting it to be able to adjust how far down to fill the formula based on an adjacent column's empty/not empty status. For example, each time I run a report, I will get an unpredictable amount of returned records that will fill out rows in column A, however, since I want to extract strings from those returned records into different Columns, I have to enter formulas for each iteration within the next three columns (B, C, and D). Is there a way to insert a line that will evaluate the number of rows in Column A that are not blank, and then fill out the formulas in Columns B, C, and D to that final row? (I know that tables will do this automatically once information is entered in the first row, but for logistical reasons, I cannot use tables).

My current code that fills out the formula in Column B, Row 2 is:

Range("B2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""By:"",RC[-1])+3,22)"

Thanks!

user3794203
  • 205
  • 2
  • 7
  • 23

2 Answers2

3

The formula that you actually need is 

=IF(A2="","",MID(A2,FIND("By:",A2)+3,22))

instead of

=MID(A2,FIND("By:",A2)+3,22) '"=MID(RC[-1],FIND(""By:"",RC[-1])+3,22)"

This checks if there is anything in cell A and then act "accordingly"

Also Excel allows you to enter formula in a range in one go. So if you want the formula to go into cells say, A1:A10, then you can use this

Range("A1:A10").Formula = "=IF(A2="","",MID(A2,FIND("By:",A2)+3,22))"

Now how do we determine that 10? i.e the Last row. Simple

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long

    '~~> Change the name of the sheet as applicable
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last Row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("B2:B" & lRow).Formula = "=IF(A2="""","""",MID(A2,FIND(""By:"",A2)+3,22))"
    End With
End Sub

More About How To Find Last Row

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

You can use this to populate columns B:D based on Column A

Range("B2:D" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
    "=MID($A2,FIND(""By:"",$A2)+3,22)"
SierraOscar
  • 17,507
  • 6
  • 40
  • 68