VBA Help - Check if multiple String Variables are Blank

[Johnny Thunder]

Hello All,

I have a small script that takes values from one sheet and populates other tabs based on specific criteria.

I have a small declaration of variables that I am trying to validate that there are values within the variables, if not I want to change the variables to "N/A" as the output. Not sure how to write a script like thought without repeating a IF/Then statement for each of my variables.

List of Variables (Small Sample List):
ProjectName = .Cells(r, 1).Value
ProjectDesc = .Cells(r, 2).Value
Status = .Cells(r, 3).Value
Time = .Cells(r, 4).Value

This is how I did it but there has to be a better way -

if ProjectName = vbnullstring then Projectname = "N/A"

And I just repeated this 10 times for all the other variables. Any help is appreciated.

 

[ranman256]

run it thru a function:

Public Sub RunApp()
ProjectName = Assign1(.Cells(r, 1).Value)
ProjectDesc = Assign1(.Cells(r, 2).Value)
Status = Assign1(.Cells(r, 3).Value)
Time = Assign1(.Cells(r, 4).Value)

End Sub


Public function Assign1(ByVal pvVal)
If (pvVal) = "" Or IsNull(pvVal) Then
   Assign1 = "N/A"
Else
   Assign1 = pvVal
End If
End function

 

[Johnny Thunder]

Thats awesome! Thanks for that!