CODEWITHSUNDEEP
excelvbaautofill
Arianne_2130p
Arianne_2130p

Reputation: 15

Automatically fill and execute formula to "Cell C2 and so forth" as I enter data in "Cell A2 and so forth"

Would like to automatically fill Cell (C:C) minus the heading, as I enter data in to Cell (A:A) minus the heading, using VBA. For the formula that I want to automatically fill into Cell C:C is a logic true/false, but instead replaced it with Scanner 1 and 2. 

=IF(ISNUMBER(SEARCH("$",$A2)),"Scanner 2",IF(ISNUMBER(SEARCH("#",$A2)),"Scanner 1","Error"))

Below is the VBA code I attempted, could it might be wrong since I am new to VBA: (maybe use a function instead of private sub?) 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rINT As Range
Dim rCell As Range
Dim tCell As Range

Set rINT = Intersect(Target, Range("A:A"))
If Not rINT Is Nothing Then
    For Each rCell In rINT
        Set tCell = rCell.Offset(0, 2)
        If IsEmpty(tCell) Then
            With ThisWorkbook.Sheets("BOD_Barcode")
            tCell = tCell.Formula = "=IF(ISNUMBER(SEARCH(""$"",$A2)),""Scanner 2"",IF(ISNUMBER(SEARCH(""#"",$A2)),""Scanner 1"",""Error""))"
            End With
        End If
    Next
End If
End Sub

Upvotes: 0

Views: 62

Answers (1)

Arianne_2130p
Arianne_2130p

Reputation: 15

Instead of using a VBA I used a formula straight to the cell and enable iteration from the options setting. Therefore as you enter data it will check if there is data if not- it will run in my case the formula but you can use it for timestamp or any function. 

=IF($A2<>"",IF($C2="",IF(ISNUMBER(SEARCH("$",$A2)),"Scanner 2", IF(ISNUMBER(SEARCH("#",$A2)),"Scanner 1","Error")),$C2),"")

Upvotes: 1

Related Questions