Simple Macro for Autofilling N/A into range

monkeyspank

Board Regular
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

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

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.

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.

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?

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

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``````

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.

Replies
1
Views
164
Replies
0
Views
324
Replies
3
Views
228
Replies
24
Views
926
Replies
6
Views
438

1,219,958
Messages
6,151,147
Members
451,011
Latest member
Pigdog89

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.

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

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