CODEWITHSUNDEEP
excelexcel-2003vba
user1663562
user1663562

Reputation: 53

Excel VBA macro: Locating first empty cell in a column and automatically filling it

I have two columns, Column (A) and Column (B) in a spreadsheet.

Column (A) contains names extracted from a query (ex. Brian, Bob, Bill, etc...) and column (B) contains one of three statuses (Assigned, In Progress, or Pending).

However, this query sometimes pulls up some line items showing "Assigned" for the status with no name, therefore corresponding cell representing the name in Column (A) is blank. So I manually fill in those empty cells with "Unknown".

What I want to do is to create a macro that finds the every empty cell in column (A) and fill in the word "Unknown" if the cell to its right contains the word "Assinged".

So the conditions are:

  1. Blank cell in column (A)

  2. Correspoding cell to its right (column B) contains the word "assinged"

This is my Code:

Private Sub CommandButton2_Click()

    For Each cell In Columns("A")
        If ActiveCell.Value = Empty And ActiveCell.Offset(0, 1).Value = "Assigned" Then ActiveCell.Value = "Unknown"
    Next cell

End Sub

Upvotes: 5

Views: 26789

Answers (3)

Scott Holtzman
Scott Holtzman

Reputation: 27259

Welcome to SO.

Try this code. It will work a bit faster and should get you what you want.

Update: Made the code more bullet proof!

Private Sub CommandButton2_Click()

Dim cel As Range, rngFind As Range, rngFilter As Range
Dim wks As Worksheet

Set wks = Sheets("sheet1")

With wks

    '-> Error check to make sure "blanks" exist
    Set rngFind = .Range("A1:A" & .Range("B" & Rows.Count).End(xlUp).Row).Find("", lookat:=xlWhole)

    If Not rngFind Is Nothing Then

        Set rngFilter = .Range("A1:B" & .Range("B" & Rows.Count).End(xlUp).Row)

        rngFilter.AutoFilter 1, "="

        '-> Error check to make sure "assigned" exists for blank cells
        Set rngFind = .Columns("B:B").SpecialCells(xlCellTypeVisible).Find("Assigned", lookat:=xlWhole)

        If Not rngFind Is Nothing Then
        '-> okay, it exists. filter and loop through cells

            rngFilter.AutoFilter 2, "Assigned"

            Set rngFind = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(1)).SpecialCells(xlCellTypeVisible)

            For Each cel In rngFind

                If cel.Offset(0, 1).Value = "Assigned" Then cel.Value = "Unknown"

            Next cel

        End If

    End If

End With


End Sub

Upvotes: 2

Reafidy
Reafidy

Reputation: 8441

There is no need to loop here, take advantage of excels built in methods which will execute faster.

Private Sub CommandButton2_Click()

    Application.ScreenUpdating = False

    With ActiveSheet.UsedRange
        .AutoFilter Field:=1, Criteria1:=""
        .AutoFilter Field:=2, Criteria1:="Assigned"

        If WorksheetFunction.CountBlank(.Columns(1)) > 0 Then
            If .Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
                .Columns(1).SpecialCells(xlCellTypeBlanks).Value = "Unknown"
            End If
        End If

        .AutoFilter
    End With

    Application.ScreenUpdating = True

End Sub

Upvotes: 8

Brad
Brad

Reputation: 12255

If you only need to do this a few times you could

  1. format your used range as a table
  2. on column A filter to only show "(Blanks)"
  3. on column B filter to only show "assinged"
  4. select all the resulting cells in column B
  5. press alt + : to select only the visible cells
  6. press F2
  7. type "unknown"
  8. press ctrl + enter

Your bad data should be good now!

Obviously this is a non-vba based solution but if you can avoid coding it's probably for the best.

Upvotes: 1

Related Questions