Simple Macro for Autofilling N/A into range

monkeyspank

Board Regular
Joined
Feb 12, 2014
Messages
73
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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")


Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top