Excel Macro search for cell with any value

Hi,

I found some code here, https://support.microsoft.com/en-us/kb/299036, that shows how to loop through a range of cells until a value is found. However the code exits and continues to say "value not found". I am new to VBA and looking to go through column A until any value is found.

I modified the If statement to If Not isempty(activecell.value) so that if it finds a cell with data it will set the variable as True. It should then exit the loop and go to the next If statement and since found = true list that the value is found. Eventually once the value is found I will set the IF found = True then ActiveCell.Copy Destination:=Range("A17"). However I can't get the first Do While statement to actually exit with found = true.

Is this happening because it is stopping on the first loop because IsEmpty(activecell) is being used? In the loop we do move down to the next row in the column.

Sub enterName()
      Dim x As String
     Dim found As Boolean
      ' Select first line of data.
      Range("A17").Select
      ' Set search variable value.
      x = "test"
      ' Set Boolean variable "found" to false.
      found = False
      ' Set Do loop to stop at empty cell.
      Do Until IsEmpty(ActiveCell)
         ' Check active cell for search value.
         If Not IsEmpty(ActiveCell.Value) Then
            found = True
            Exit Do
         End If
         ' Step down 1 row from present location.
         ActiveCell.Offset(1, 0).Select
      Loop
   ' Check for found.
      If found = True Then
         MsgBox "Value found in cell " & ActiveCell.Address
      Else
         MsgBox "Value not found"
      End If
End Sub
August 17th, 2015 2:00pm

That code is very inefficient of you're looking for a specific value. You can use the Find method for that:

Sub enterName()
    Dim x As String
    Dim cel As Range
    ' Set search variable value.
    x = "test"
    Set cel = Range("A17:A" & Rows.Count).Find(What:=x, After:=Range("A17"), _
        LookIn:=xlValues, LookAt:=xlWhole)
    If cel Is Nothing Then
        MsgBox "Value not found"
    Else
        MsgBox "Value found in cell " & cel.Address
    End If
End Sub

Free Windows Admin Tool Kit Click here and download it now
August 17th, 2015 3:09pm

Hi,

What if I want to find any value. Say all the rows in column A are empty except for one. I want to find that one and copy/paste it into a specific cell. If not then ask the user for input and put that in the specific cell. That's one of the reasons I thought the loop would work and then valuate the cell based on the if statement.

-Thanks

August 17th, 2015 3:28pm

If you want to search for any value, change

    x = "test"

to

    x = "*"

Free Windows Admin Tool Kit Click here and download it now
August 17th, 2015 4:01pm

Hi,

Thank you for your assistance with my question. I was able to go from there and write the rest of the code necessary to complete what I wanted. For reference here is the updated code.

I know I call copy twice. Once in If and once in Else. The if is if a user name is not found, enter it. The Else is if a user name is found, copy it to the correct cells.

Essentially I am preparing the excel spreadsheet to be saved as a CSV for import.

-Thanks

Sub entername()
    Dim x As String
    Dim cel As Range
    ' Set search variable value.
    x = "*"
    Set cel = Range("A17:A" & Rows.Count).Find(What:=x, After:=Range("A17"), _
        LookIn:=xlValues, LookAt:=xlWhole)
    If cel Is Nothing Then
        Range("A17").Value = Application.InputBox("What is the employee Name", Type:=2)
        Range("A17").Copy Destination:=Range("A17:A263")
     Else
        With cel
        .Copy Destination:=Range("A17:A263")
        End With
    End If
End Sub

August 17th, 2015 5:51pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics