CODEWITHSUNDEEP
vbaexcel
user2574
user2574

Reputation: 61

VBA Code to Autofill

Have a column H with alphanumeric characters. Some cells in this column have the content (RAM) followed by 5 digits starting from 00000 to 99999.
If cell H219 has the content (RAM) 23596 then i have to fill cell A219 with a comment "completed".
This has to be done for all cells with the content "(RAM) followed by 5 digits" 

Sub Macro16_B()
    ' ' Macro16_B Macro ' '
    intRowCount = Worksheets("Reconciliation").UsedRange.Rows.Count
    For i = 11 To intRowCount
        If InStr(Range("H" & i).Value, "(RAM 00000-99999") Then
            Range("A" & i).Value = "Completed"
        End If
    Next i
End Sub

Upvotes: 0

Views: 440

Answers (3)

Excelosaurus
Excelosaurus

Reputation: 2849

Well, there are already 2 good answers, but allow me to paste my code here for good measure, the goal being to submerge @user2574 with code that can be re-used in his/her next endeavors:

Sub Macro16_B()
    'In the search spec below, * stands for anything, and # for a digit.
    'Remove the * characters if you expect the content to be limited to "(RAM #####)" only.
    Const SEARCH_SPEC As String = "*(RAM #####)*"

    Dim bScreenUpdating As Boolean
    Dim bEnableEvents As Boolean

    'Keep track of some settings.
    bScreenUpdating = Application.ScreenUpdating
    bEnableEvents = Application.EnableEvents

    On Error GoTo errHandler

    'Prevent Excel from updating the screen in real-time,
    'and disable events to prevent unwanted side effects.
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    'Down with business...

    Dim scanRange As Excel.Range
    Dim cell As Excel.Range
    Dim content As String
    Dim ramOffset As Long

    With ThisWorkbook.Worksheets("Reconciliation").Columns("H")
        Set scanRange = .Worksheet.Range(.Cells(11), .Cells(.Cells.Count).End(xlUp))
    End With

    For Each cell In scanRange
        content = CStr(cell.Value2)
        If content Like SEARCH_SPEC Then
            cell.EntireRow.Columns("A").Value = "Completed"
        End If
    Next

Recover:
    On Error Resume Next
    'Restore the settings as they were upon entering this sub.
    Application.ScreenUpdating = bScreenUpdating
    Application.EnableEvents = bEnableEvents
    Exit Sub

errHandler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, "Error"
    Resume Recover
End Sub

Upvotes: 0

SJR
SJR

Reputation: 23081

One way is to use the Like operator. The precise format of your string is not clear so you may have to amend (and assuming case insensitive). # represents a single number; the * represents zero or more characters.

Sub Macro16_B()
    Dim intRowCount As Long, i As Long
    ' ' Macro16_B Macro ' '
    intRowCount = Worksheets("Reconciliation").UsedRange.Rows.Count
    For i = 11 To intRowCount
        If Range("H" & i).Value Like "(RAM) #####*" Then
            Range("A" & i).Value = "Completed"
        End If
    Next i
End Sub

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

A non-VBA answer could be (if the cell doesn't have extra text other than (RAM) & 5 numbers): 

=IFERROR(IF(LEN(VALUE(TRIM(SUBSTITUTE(H1,"(RAM)",""))))=5,"completed",""),"")

My VBA answer would be: 

Sub Test()

    Dim rLastCell As Range
    Dim rCell As Range

    With Worksheets("Reconciliation")
        Set rLastCell = .Columns(8).Find("*", , , , xlByColumns, xlPrevious)
        If Not rLastCell Is Nothing Then
            For Each rCell In .Range(.Cells(1, 8), rLastCell)
                If rCell Like "*(RAM) #####*" Then
                    rCell.Offset(, -7) = "complete"
                End If
            Next rCell
        End If
    End With

End Sub

Cheers @Excelosaurus for heads up on the * would've forgotten it as well. :)

Upvotes: 1

Related Questions