Simple Macro for Autofilling N/A into range

[monkeyspank]

I want to write a macro that will do the following:

IF(B12:FD12<=0,"N/A","") want the N/A to populate in b36:fd36
IF(B12:FD12<=1,"N/A","") want the N/A to populate in b37:fd37
IF(B12:FD12<=2,"N/A","") want the N/A to populate in b38:fd38
IF(B12:FD12<=3"N/A","") want the N/A to populate in b39:fd39

Is this possible?

 

[PGuru]

Quicker to write the formulae and copy across than write a macro?

I admit that's based on the belief that you actually want IF(B12<=0,"N/A","") in B36 and IF(FD12<=0,"N/A","") in FD36

 

[wrightyrx7]

Is that formula meant to be a sum of range B12:FD12??

 

[monkeyspank]

Well? I currently have the formula in each cell in the range of b36:fd36 but we also type information in those cells when they aren't N/A. I wanting to eliminate in potential error. Say someone clears the formula for any of those cells then my N/A will not show and perhaps it should have been.

 

[PGuru]

ADVERTISEMENT

OK, there is VBA that will populate a cell with whatever formula you like.

But based on your comment, I still don't think you want it - if the macro is going to overwrite the manually-entered value, why not lock the cell and prevent them manually entering in the first place? Or, give users another row for their entry and have the formula use that value if it exists, and the current formula otherwise.

 

[monkeyspank]

Yes. I know. I wish it could be that easy but unfortunately I need to be able to do both. So, I guess I will just leave the way I have it? Don't suppose you have that VBA code though?

 

[monkeyspank]

ADVERTISEMENT

No its not a sum of range. It would be for any cell in that range that has a any number >0 then preceding ranges would autofill a N/A

 

[wrightyrx7]

Amended a macro I created today, see if its what you want. This code need to go in you worksheet code and not a module.

The macro will only run when a cell is changed in range("B12:FD12")

Private Sub Worksheet_Change(ByVal Target As Excel.Range)


    Dim cRng As Range, cCell As Range
    
    On Error Resume Next
        Set cRng = Intersect(Me.Range("B12:FD12"), Target)
    On Error GoTo Err_handle
    
    If Not cRng Is Nothing Then
        Application.EnableEvents = False
            For Each cCell In cRng
                With cCell
                    If .Value <> vbNullString Then
                        .Offset(24).Resize(4).Value = ""
                        If .Value <= 0 Then
                            .Offset(24, 0).Value = "N/A"
                        ElseIf .Value <= 1 Then
                            .Offset(25, 0).Value = "N/A"
                        ElseIf .Value <= 2 Then
                            .Offset(26, 0).Value = "N/A"
                        ElseIf .Value <= 3 Then
                            .Offset(27, 0).Value = "N/A"
                        End If
                    Else
                        .Offset(24).Resize(4).Value = ""
                    End If
                End With
            Next cCell
        Application.EnableEvents = True
    End If
Exit Sub


Err_handle:
    Application.EnableEvents = True
End Sub

 

[wrightyrx7]

This will overwrite/clear any values that have been manually typed in though.

Will clear row 36-39 below which ever cell is being edited, then put the N/A where it should go.