Complete empty cells with a value

[nburaq]

Hi Gents,
I have a huge list which includes columns namely group and points. In group column there is always a value but in points column the value might not exists like value C and E in column group. If there is not any value in column points then a value needs to be written as "N/A". I tried to do it myself using IF ISBLANK function then it writes values to every empty cells. Thanks in advance for all your help. I attach mini sheet and screenshot.

Book1
	I
14
Sheet1

 

[maabadi]

I don't think you can do it with formula. Try VBA Method:

Sub AddNA()
Dim i As Long, Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
If Range("A" & i).Value <> "" And Range("B" & i).Value = "" Then Range("B" & i).Value = "N/A"
Next i
End Sub

 

[nburaq]

I don't think you can do it with formula. Try VBA Method:

Sub AddNA()
Dim i As Long, Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr
If Range("A" & i).Value <> "" And Range("B" & i).Value = "" Then Range("B" & i).Value = "N/A"
Next i
End Sub

thanks a lot for the quick reply it works like a charm! I always wonder which programming language vba uses? the code you write in C++?

 

[maabadi]

You're Welcome & Thanks for Feedback.
The programming languages have very similarity.

 

[nburaq]

Hi Maabadi,
One extra request, I have two columns and column B has two values Labor and Services and Services cell should be equal to Labor value. For example if labor is 805 and Services should be 805. I guess it is similar code u write in VBA. If you could help me to solve this I would be so grateful. Thanks again for your help!

 

[maabadi]

One thing I see you have 2 Labor and then Services then what value you want to see at services row. First (801) or Second (802).
if you want Last value before services Try this:

Sub Addvalues()
Dim i As Long, Lr As Long, K As String
Lr = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To Lr
If Range("B" & i).Value = "Labor" Then
K = Range("A" & i).Value
ElseIf Range("B" & i).Value = "Services" Then
Range("A" & i).Value = K
K = ""
End If
Next i
End Sub

 

[nburaq]

Every service number will be equal to its own labor numberif there is any services. SO IF Labor is 802 service is also 802 and if labor is 805 service is also 805...

 

[maabadi]

I modified Post #6 Try it. if have problems, please report here.

 

[nburaq]

Thanks for the solution! one more thing to ask if values are more than two; lets say Labor, Services and Material then which part of the code needs to be repeated?

 

[maabadi]

How about:

 Sub Addvalues()
Dim i As Long, Lr As Long, K As String
Lr = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To Lr
If Range("B" & i).Value = "Labor" Then K = Range("A" & i).Value
ElseIf Range("B" & i).Value = "Services"  OR Range("B" & i).Value = "Material"  Then
Range("A" & i).Value = K 
End If 
Next i 
End Sub