IF COUNTA conversion to VBA

[gabbana]

Morning All,

Can anyone help convert this into VBA for me? I need it to automatically run as the worksheet is filled out:

=IF(COUNTA(M1:V1)=10,TODAY()+60,"")

Currently used in column W Thanks in advance.

 

[baitmaster]

I take it you plan to fill out a section within the worksheet (e.g. not the header row, so only row 2 onwards?), then have the results of this added to a specific column on the same row. I ask because this is a simple request but will need a number of conditions considered, such as what happens if you enter multiple rows at same time, or what happens if you change the cell that this macro writes to?

Try this. It appears to work, but you should always save your work before running untested code. Paste it into the worksheet code module for the sheet that you want it to run on

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range, rngDest As Range, r As Long

Application.EnableEvents = False ' prevent chance of terminal loops

For Each cl In Target
    r = cl.Row
    If r >= 2 And cl.Column <> 23 Then ' only look at row 2 onwards, ignore column W
        
        Set rngDest = Range(Cells(r, 13), Cells(r, 22)) ' create range of cells to be looked at (columns M to V)
        
        If WorksheetFunction.CountA(rngDest) = 10 Then
            Cells(r, 23) = Date + 60 ' enter value
        Else
            Cells(r, 23).ClearContents ' clear value
        End If
        
    End If
Next cl

Application.EnableEvents = True ' return setting to default

End Sub

 

[dermie_72]

activecell.formula="IF(Counta(M1:V1)-10,today()+60,"""")"

 

[gabbana]

Thank you. This works perfectly. The start row needed to be amended, but I worked that bit out.

The cells it references are dates which certain tasks are completed. The date it generates is a 60 day review date upon completion of the last task. (they are all done by different people at different times, so it was a way of ensuring a review is complete.

I had to use VBA as a cell formula kept updating the date every time I opened the sheet.