asked on

Excel 2016 - Using VBA, how do I populate a range of cells with a formula?

I want to use VBA to populate the cells in a single column with the following formula.

=IF(ISNUMBER(SEARCH("501020",A2)),"x","Y")

where A2 is the starting location of the text that I am searching for "501020", and C2 is the starting location of the result (x or Y). Each subsequent row would have different text in column A and the appropriate result in column C. The number of rows in the worksheet will vary ranging up to 500 rows.

I want to duplicate the same results that I get if I copied the above formula in C2, and pasted it down to the last row containing data in column A.

Shums Faruk

Hi Steve,

Where do you want result to be, in which column?

Steve Lowry

ASKER

Hi Shums,

The result will be in column C.

Shums Faruk

Try below:

Sub UpdateCells()
Dim Ws As Worksheet
Dim LRow As Long, i As Long
Set Ws = ActiveSheet
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To LRow
    If Ws.Cells(i, 1) = "501020" Then
        Ws.Cells(i, 3) = "x"
    Else
        Ws.Cells(i, 3) = "y"
    End If
Next i
Application.ScreenUpdating = True
End Sub

Open in new window

Steve Lowry

ASKER

Shums,

The code works, but it is looking for exact text. The text in column A may contain more than one variation of text containing "501020".

Is it possible to do some with a statement similar to

Range (xxxx).Formula = "=IF(ISNUMBER(SEARCH("501020",A2)),"x","Y")"

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Lowry

ASKER

Shums,

This works perfectly. This will let me learn how to apply this to other columns that I need to populate with similar formulas.

Thanks.
Steve

Shums Faruk

You're Welcome Steve! Glad I was able to help.