Converting a Function to a Sub Routine

[wazzulu1]

Hi;

I made this function to check entries into cells, and it works for what was needed if I manually add it in the spreadsheet.

Is there a way to change this to a sub routine to check all cells in Column A that are not Null, and place the return values in Column T of the same row?

Function ValidEntry(strEntry As String) As String
  If strEntry Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]" Then
    ValidEntry = "Pass"
  ElseIf strEntry Like "[a-z][a-z][a-z][a-z][a-z][a-z][a-z]" Then
    ValidEntry = "Fail"
  Else
    ValidEntry = "N/A"
  End If
End Function

I am using Excel 2010

 

[JoeMo]

Hi;

I made this function to check entries into cells, and it works for what was needed if I manually add it in the spreadsheet.

Is there a way to change this to a sub routine to check all cells in Column A that are not Null, and place the return values in Column T of the same row?

Function ValidEntry(strEntry As String) As String
  If strEntry Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]" Then
    ValidEntry = "Pass"
  ElseIf strEntry Like "[a-z][a-z][a-z][a-z][a-z][a-z][a-z]" Then
    ValidEntry = "Fail"
  Else
    ValidEntry = "N/A"
  End If
End Function

I am using Excel 2010

Your function seems to be concerned with 7-character entries, not null entries. Why do you want to convert it for the purpose you describe? Why not just create a subroutine from scratch.

 

[wazzulu1]

Hi JoeMo;

I need to maintain the validation that this is performing, checking for the 7 character string, making sure it's numbers, and nothing else. I had described this need in another thread, and came up with this solution, but I would need to manually add this as a formula with each entry in Column A, to add this in Column T somehow. I need to have the column formatted as text, to keep lead zeros, not the 7 characters need to be numbers only.

 

[JoeMo]

Hi JoeMo;

I need to maintain the validation that this is performing, checking for the 7 character string, making sure it's numbers, and nothing else. I had described this need in another thread, and came up with this solution, but I would need to manually add this as a formula with each entry in Column A, to add this in Column T somehow. I need to have the column formatted as text, to keep lead zeros, not the 7 characters need to be numbers only.

Do I understand correctly: You want to check every non-null entry in col A and if the entry is a 7-digit number place "Pass" in col T, if a 7-alpha characters entry place "Fail" in col T, and if anything else place "N/A" in col T?

 

[wazzulu1]

Checking every entry that is Not Null (that has an entry entered into the cell, and only entering "Pass" or "Fail" (or just Pass if this simplifies matters). The entry needs to be 7 characters only, all numbers in a text formatted column.

 

[JoeMo]

Checking every entry that is Not Null (that has an entry entered into the cell, and only entering "Pass" or "Fail" (or just Pass if this simplifies matters). The entry needs to be 7 characters only, all numbers in a text formatted column.

If you just want to pass the 7-digit entries, it would be simpler and less cluttered in col T to return nothing to col T except the passes.

See if this works for you - assumes your data start in A1 - adjust start cell to suit.

Sub SevenDigits()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Not IsEmpty(c) Then
        If c.Value Like "#######" Then Cells(c.Row, "T").Value = "Pass"
    End If
Next c
Application.ScreenUpdating = True
End Sub

 

[wazzulu1]

Hi JoeMo;

Question: if a valid entry is made, T is filled with "Pass", but if it's removed/deleted, the Pass still remains, and it does not recalculate to remove it.

 

[Rick Rothstein]

Hi JoeMo;

Question: if a valid entry is made, T is filled with "Pass", but if it's removed/deleted, the Pass still remains, and it does not recalculate to remove it.

Try modifying JoeMo's code like this...

Sub SevenDigits()
  Dim c As Range
  Application.ScreenUpdating = False
  For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If Not IsEmpty(c) Then
      If c.Value Like "#######" Then
        Cells(c.Row, "T").Value = "Pass"
      Else
        Cells(c.Row, "T").Value = ""
      End If
    End If
  Next c
  Application.ScreenUpdating = True
End Sub

 

[wazzulu1]

Hi Rick;

Thanks, this works perfectly!