VBA If filtered table has any record, insert string

weirax · Mar 15th 2018

Hello,

I need your help to check what am I doing wrong.This is the table I am working on, called "ActionTable".

What I want: filter for empty fields of status column with VBA code, and insert "NO ACTION STARTED YET" to Status and Letter column, plus the actual date to ADDED_ON column.
The problem is, that if there is no record to show, the macro stops, even if I used an IF statement.

The error message is "runtime error 1004. no cells were found"

Can you please help me?
[TABLE="border: 1, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 108"]HOSTNAME_DB[/TD]
[TD="width: 142"]Finding[/TD]
[TD="width: 203"]DB Cluster[/TD]
[TD="width: 84"]Running on[/TD]
[TD="width: 173"]Comment[/TD]
[TD="width: 158"]Status[/TD]
[TD="width: 461"]Letter[/TD]
[TD="width: 149"]Contacts[/TD]
[TD="width: 85"]ADDED_ON[/TD]
[TD="width: 102"]Letter sent on[/TD]
[TD="width: 105"]Next reminder[/TD]

[/tr]

[tr]

[td]

a

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

a

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

b

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

b

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

c

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

c

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

d

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

d

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

e

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

e

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

f

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

f

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

g

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

g

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[tr]

[td]

h

[/td]

[td]

something

[/td]

[td][/td]

[td]

N/A

[/td]

[td]

comments

[/td]

[td]

status

[/td]

[td]

Info

[/td]

[td]

h

[/td]

[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]

[td][/td]

[/tr]

[/TABLE]

Sheets("ACTION").Select

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

ActiveSheet.ListObjects("ActionTable").Range.AutoFilter Field:=6, Criteria1:="="

If ActiveSheet.Cells(rows.Count, 1).End(xlUp).Row > 1 Then

With Sheets("ACTION")
lLastRow = .Cells(.rows.Count, "A").End(xlUp).Row

With .Range("F2:F" & lLastRow)
.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "NO ACTION STARTED YET" ' Macro stops at this point
End With

With .Range("G2:G" & lLastRow)
.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "NO ACTION STARTED YET"
End With

With .Range("I2:I" & lLastRow)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With

End With

Else
End If

weirax · Mar 15th 2018

For me it seems it somehow ignores this IF condition: [INDENT]If ActiveSheet.Cells(rows.Count, 1).End(xlUp).Row > 1 Then
(...)
Else
End if[/INDENT]

**Ger Plante** · Mar 15th 2018

You should use the "Databodyrange" method for the listobject to work with the data in the table. Databodyrange will always point to your data excluding your headers. When you combine this with specialcells(xlcelltypevisible) you will see how many cells are hidden or not.

You can also set a range to those visible cells and then set their value to whatever you want... for example:

Code

Option Explicit


Public Sub test()


Dim lo As ListObject
Dim r As Range


Set lo = ActiveSheet.ListObjects(1)
lo.Range.AutoFilter 6, "="


Debug.Print "There are " & lo.DataBodyRange.Rows.Count & " rows in the this listobject"
Debug.Print "There are " & lo.DataBodyRange.Columns(6).SpecialCells(xlCellTypeVisible).Cells.Count & " visible rows being displayed"


Set r = lo.DataBodyRange.Columns(6).SpecialCells(xlCellTypeVisible) 'set a range variable to the visible cells. 


r.Value = "This cell is empty" 'updates all the visible cells in the table with a value. 


End Sub

Display More

note - if there are NO blanks in the column 6, the above code will fail, so you should allow the error to occur with "On error resume next" and then trap the error with something like "if not r is nothing then...."

HTH
Ger

weirax · Mar 19th 2018

Thank you, it works as desired combined with the if statement.

VBA If filtered table has any record, insert string

Participate now!

Share