excel - How to search on worksheet by VBA Code?

Question

I have a worksheet with 2 columns "Key" and "Value". by VBA code, i want search Input_key on Key columns, if not exist, I will add new row [input-key]-[input-value]. How do i code?

Answer 1

You will realise from the comments that "please solve my problem for me" questions are not popular.

I will guess that you do not know where to start and will give you some initial guidance.

Go to Google and type in "excel vba tutorial". You will be offered many sites. They are all different so try a few and find one that is right for you.

Try the macro recorder. I set up a worksheet which matches your description, switched on the macro recorder, selected column A, clicked Ctrl+F to get the Find screen and clicked the option button to show me all the options. The result is:

Look at the options. For example, is case important? Select as required. I ticked "match entire cell contents", entered "k" and clicked Find Next. The cursor jumped to the cell containing "k". I then switched the macro recorder off.

The code saved for me was:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/02/2012 by Tony Dallimore
'
  Columns("A:A").Select
  Selection.Find(What:="k", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False).Activate
End Sub

This is valid VBA but is not good VBA. The macro recorder has recorded each action as you performed it. It does not know your intentions. So we need to tidy this code up.

The key changes are:

• We do not want to select column A or activate the cell containing the value found.
• We need to allow for the value not being found.

Copy the macro below to the module in which the macro recorder saved its code. I created this macro by amending the saved code to create a test vehicle for you to play with. It asks for a value, searches for it in column A and says whether the value was found or not. This is the basis of the code you need.

Sub PlayMacro()

  Dim Prompt As String
  Dim RetValue As String
  Dim Rng As Range
  Dim RowCrnt As Long

  Prompt = ""

  ' The macro recorder has used the active worksheet.  This says which
  ' worksheet is to be used whether it is active or not.  Change "Sheet4"
  ' to the name of your worksheet.
  With Sheets("Sheet4")

    ' This will loop forever unless a statement within
    ' the loop exits the Do.
    Do While True

      RetValue = InputBox(Prompt & "Give me a value to look for")
      'RetValue will be empty if you click cancel
      If RetValue = "" Then
        Exit Do
      End If

      ' I do not wish to active the cell containing the required value.
      ' I want to know where it is.
      Set Rng = .Columns("A:A").Find(What:=RetValue, After:=.Range("A1"), _
                LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

      If Rng Is Nothing Then
        ' The entered value could not be found
        Prompt = "I could not find """ & RetValue & """"
      Else
        ' The entered value was found
        RowCrnt = Rng.Row
        Prompt = "I found """ & RetValue & """ on row " & RowCrnt
      End If
      Prompt = Prompt & vbLf
    Loop

  End With

End Sub

Now switch the macro recorder on again. Position the cursor in column A below any rows with values. Click Ctrl+UpArrow. The cursor will jump to the last value in column A. Switch the macro recorder off.

The saved code will look like:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 27/02/2012 by Tony Dallimore
'

'
    Range("A64").Select
    Selection.End(xlUp).Select
    Range("A28").Select
End Sub

End(xlUp) is the VBA for Ctrl+UpArrow. It is the easiest way of finding the last used row.

To add a new row, which you want to do if the value is not found:

RowCrnt = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(RowCrnt,1),Value = "Key"
.Cells(RowCrnt,2),Value = "Value"

If you look at other questions you will discover that End will sometimes not give you the result you expect. Try Ctrl+DownArrow and Ctrl+UpArrow on a empty column, a column with one then two values at the top, a column with one then two values at the bottom and a column with several values separated by blank rows.

This should get you started. Welcome to Excel programming. Good luck.