VBA - Edit visible data after autofilter?

[Axcell]

This should be simple but I have exhausted my abilities for a solution


My worksheet (jobsheet) contains jobs which are posted with work orde
number, customer, location, type of work, etc, across columns in row.

I have a cell on each row to input the contract amount and a cell t
check off if customer has been invoiced.

I currently invoice the customer via a userform and would like to en
this code with applying a "check mark" or an "X" in the cell for tha
customer on the job sheet.

I use autofilter with workorder number for criteria to reflect row.

How can I add an X or check mark to this row in column "I" via VBA whe
visible?

Thanks!

 

[Dave Peterson]

I think that this will work for you:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim rngF As Range
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
If .AutoFilterMode = False Then
MsgBox "Please apply a filter"
Exit Sub
ElseIf .FilterMode = False Then
MsgBox "Please filter something"
Exit Sub
End If
End With

With wks.AutoFilter.Range
Set rngF = Nothing
On Error Resume Next
Set rngF = .Offset(1).Resize(.Rows.Count - 1) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "no visible rows in the filtered range!"
Exit Sub
End If

'add "X " to the existing value in the cell?
'For Each myCell In Intersect(rngF.EntireRow, .Range("I:I")).Cells
' myCell.Value = "X " & myCell.Value
'Next myCell

'or just overlay an X in that cell?
Intersect(rngF.EntireRow, .Range("I:I")).Value = "X"

End With

End Sub

 

[Axcell]

Hi Dave,

Works great!

Thanks for your help.