Thank you, it works as desired combined with the if statement.
Posts by weirax
-
-
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] -
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?
[tr]
[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]
[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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/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][/td]
[TD="align: right"]06.02.2018[/TD]
[TD="align: right"]06.02.2018[/TD]
[/tr]
[/TABLE]Sheets("ACTION").Select
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End IfActiveSheet.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).RowWith .Range("F2:F" & lLastRow)
.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "NO ACTION STARTED YET" ' Macro stops at this point
End WithWith .Range("G2:G" & lLastRow)
.SpecialCells(xlCellTypeVisible).FormulaR1C1 = "NO ACTION STARTED YET"
End WithWith .Range("I2:I" & lLastRow)
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End WithEnd With
Else
End If -
Hello,
I think I found the problem. The reason was the filtering.
I removed the filters, rerun the macro and only after then enabled the filter and now it works fine.
Thank you very much for your help! -
It should not be the problem as I clear all results at the end of the execution. So no previous lookup results can remain in the table.
[tr]
I tried your above code and now the result is: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[/tr]
[TD="width: 64"]RESULT[/TD]
[TD="width: 154"]LOOKUP[/TD]
[tr]
[td]aaa
[/td]
[td]comment1
[/td]
[/tr]
[tr]
[td]bbb
[/td]
[td]comment2
[/td]
[/tr]
[tr]
[td]ccc
[/td]
[td][/td]
[/tr]
[tr]
[td]ddd
[/td]
[td][/td]
[/tr]
[tr]
[td]eee
[/td]
[td]comment1
[/td]
[/tr]
[tr]
[td]fff
[/td]
[td]comment1
[/td]
[/tr]
[tr]
[td]ggg
[/td]
[td]comment1
[/td]
[/tr]
[tr]
[td]hhh
[/td]
[td]comment1
[/td]
[/tr]
[tr]
[td]zzz
[/td]
[td]comment1
[/td]
[/tr]
[/TABLE]
Which is not correct. -
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.JPG","data-attachmentid":1200801}[/ATTACH]
-
Hello, thank you very much for your answer.
Maybe the problem is on my side.
It is still shows incorrect records.
For example, for me in my workbook, ffff and zzz shows "comment1" instead of "N/A".
I replaced the code to yours and the result is still the same.
Do you have any idea, what can be the problem? -
Here you are
-
Hi All,
I'm new in this forum - good day to everyone.
I need your help in a problem that I could not figure myself why it is not working.I have 2 tabs involved in my problem:
CLEANUP = this tab contains a table with header (A,B columns) which is filtered by A column criterias, B column is empty
ACTION = this tab contains a table with header (A:H)In CLEANUP tab, I want a VLOOKUP function in column B. Here is my code:
=============================================
Dim lLastRow2 As LongWith Sheets("CLEANUP")
lLastRow2 = .Cells(.Rows.Count, "A").End(xlUp).RowWith .Range("B2:B" & lLastRow2)
.FormulaR1C1 = "=VLOOKUP(RC[-1], ACTION!R2C1:R200C5,5,FALSE)"
.Value = .Value
End WithEnd With
=============================================The problem is that for the last few results are incorrect (there are no such records in ACTION tab table as I look up, however, it lists the first ACTION entry row result).
I tried with 0 instead of FALSE aswell, without success.Can you please help me what can be the reason the exact match is not working properly?
Thank you in advance!