apply formula to SpecialCells(xlCellTypeVisible)

[vmjan02]

This code is working all perfect it finds the column name, but the problem is I am not able to apply formula to the visible cells any suggestion

Sub fillrejectionflag()

    Dim co12 As String, c10find As Range
    Dim co13 As String, cc12find As Range

    co12 = "Cand. Final Status"
    co13 = "Email Flag"
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
With Worksheets("Candidate Master") '<-- reference your relevant worksheet (change "AF" to your actual worksheet name)
        With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)) '<-- reference its row 1 cells from column 1 rightwards to last not empty one
            
            Set c10find = .Rows(1).Find(what:=co12, LookIn:=xlValues, lookat:=xlWhole)
            Set cc12find = .Rows(1).Find(what:=co13, LookIn:=xlValues, lookat:=xlWhole)
            If Not c10find Is Nothing Then '<-- if the header has been found
            [B]Range(c10find & Lastrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "1"  '' Problem is here
            Range(cc12find & Lastrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=now()" ''Problem is here[/B]
                ' do your things
            End If
        End With
        '.AutoFilterMode = False '<-- show all rows back and remove autofilter buttons
    End With
End sub

 

[6StringJazzer]

I am not able to apply formula to the visible cells

            Range(cc12find & Lastrow).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=now()" ''Problem is here[/B]

What happens when you run this code that is different than what you want to happen?

 

[Alex Blakenburg]

Try this:-

Sub fillrejectionflag()

    Dim co12 As String, c10find As Range
    Dim co13 As String, cc12find As Range
   
    Dim Lastrow As Long
    Dim CandColNo As Long, EmailColNo As Long
   
    co12 = "Cand. Final Status"
    co13 = "Email Flag"
    Lastrow = Cells(Rows.Count, 1).End(xlUp).Row 
    With Worksheets("Candidate Master") '<-- reference your relevant worksheet (change "AF" to your actual worksheet name)
        With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)) '<-- reference its row 1 cells from column 1 rightwards to last not empty one
            Set c10find = .Rows(1).Find(what:=co12, LookIn:=xlValues, lookat:=xlWhole)
            CandColNo = c10find.Column
            Set cc12find = .Rows(1).Find(what:=co13, LookIn:=xlValues, lookat:=xlWhole)
            EmailColNo = cc12find.Column
        End With
           
        If Not c10find Is Nothing Then '<-- if the header has been found
            .Range(.Cells(2, CandColNo), .Cells(Lastrow, CandColNo)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "1" '' Problem is here
            .Range(.Cells(2, EmailColNo), .Cells(Lastrow, EmailColNo)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=now()" ''Problem is here
                ' do your things
        End If

        '.AutoFilterMode = False '<-- show all rows back and remove autofilter buttons
    End With
End Sub

 

[vmjan02]

Dim co12 As String, c10find As Range Dim co13 As String, cc12find As Range Dim Lastrow As Long Dim CandColNo As Long, EmailColNo As Long co12 = "Cand. Final Status" co13 = "Email Flag" Lastrow = Cells(Rows.Count, 1).End(xlUp).Row With Worksheets("Candidate Master") '<-- reference your relevant worksheet (change "AF" to your actual worksheet name) With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)) '<-- reference its row 1 cells from column 1 rightwards to last not empty one Set c10find = .Rows(1).Find(what:=co12, LookIn:=xlValues, lookat:=xlWhole) CandColNo = c10find.Column Set cc12find = .Rows(1).Find(what:=co13, LookIn:=xlValues, lookat:=xlWhole) EmailColNo = cc12find.Column End With If Not c10find Is Nothing Then '<-- if the header has been found .Range(.Cells(2, CandColNo), .Cells(Lastrow, CandColNo)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "1" '' Problem is here .Range(.Cells(2, EmailColNo), .Cells(Lastrow, EmailColNo)).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=now()" ''Problem is here ' do your things End If '.AutoFilterMode = False '<-- show all rows back and remove autofilter buttons End With

perfect, its working great.
only think who will =Now() be converted to values ,

sorry missed this part in my first post.

 

[MARK858]

only think who will =Now() be converted to values ,

sorry missed this part in my first post.

Just use Now rather than "=Now()" then you don't need to convert to values

Range(.Cells(2, EmailColNo), .Cells(Lastrow, EmailColNo)).SpecialCells(xlCellTypeVisible).Value = Now

 

[Alex Blakenburg]

@MARK858 beat me by || much

 

[MARK858]

@MARK858 beat me by || much

Was surfing as awake too early

 

[vmjan02]

Just use Now rather than "=Now()" then you don't need to convert to values

Range(.Cells(2, EmailColNo), .Cells(Lastrow, EmailColNo)).SpecialCells(xlCellTypeVisible).Value = Now

kool thanks it working all perfect

 

[MARK858]

You're welcome (from all of us)