Find N/A and fill same "N/A in next available blank cells

[Mrsaad]

Holdings	-	-	Duration
N/A	N/A	N/A	Meets Expectation
N/A	-	-	Meets Expectation
Meets Expectation	-	-	Meets Expectation
N/A	-	-	Meets Expectation

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

"There is N/A under Holdings and I filled N/A in next columns manually, before "Meets Expectation" column, also there are N/A below and I want them filled automatically with some VBA coding till its blank.

Thanks in Advance

 

[Yongle]

which columns?
starting at which row?

 

[Mrsaad]

Means in entire sheet, if it find N/A under any header so fill N/A automatically, till next filled column.

 

[Yongle]

Test on a COPY of your worksheet

Sub NotApplicable()
    Const N = "N/A": Dim Cel As Range
    
    For Each Cel In ActiveSheet.UsedRange
        If UCase(Cel.Offset(, -1) & Cel) = N Then Cel = N
    Next
End Sub

Perhaps too many N/A ??

 

[Mrsaad]

Yes with too many N/A .. Not working

 

[Yongle]

It is working!
It is not working how you want - I expected that.

Not working

That comment is not going to get us any further, so please explain what needs changing ...
- which ROW are the Headers in?
- you need to tell me when the N/A must stop based on a value in the HEADER row (or another row)

I need to give VBA a value it can look up and know to stop inserting N/A in next cell

 

[Mrsaad]

yeah its not working as i want..

ok let me explain.. U see main header there "Holdings" and then next main header "Duration", i get N/A part under the main header with other part "Meet Expectations", I need to put N/A manually between "Holdings" and Duration manually (Under Sub headers), which is comes blank by default.
I want that if Excel find N/A under any main header, so it automatically put N/A under other sub headers.

Hope u get this now, I tried my best actually..

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub DistributeNAs()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:C" & LastRow) = Evaluate(Replace("IF(A2:A#=""N/A"",""N/A"",IF(B2:C#="""","""",B2:C#))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]