VBA Macro for search specific word and input specific value

**Toton6868** · 01-09-2013, 03:22 AM

I am newbie in Excel Macro. I am trying to do a very simple macro for excel. The Macro will works like IF a specific cell (for example D) contains some categorized string then it will return a specific value in another cell. For an example if any cell of column D contains 'Books', 'Food', 'Fruits' string anywhere of its cell then it will return 01, 02, 03 respectively in Column E. For sure it will very easy task and probably this kind of question asked earlier but as I am newbie please let me know in a brief or any link to get the solution.

I am trying to use this code I found.

Sub Test()
    For Counter = 1 To 10000
        Set curCell = Worksheets("Sheet1").Cells(Counter, 4)
        '  'Books', 'Foods', 'Fruits
        If curCell.Value = "Books" Then Worksheets("Sheet1").Cells(Counter, 5) = "01"
        elseIf curCell.Value = "Foods" Then Worksheets("Sheet1").Cells(Counter, 5) = "02"
        elseIf curCell.Value = "Fruits" Then Worksheets("Sheet1").Cells(Counter, 5) = "03"
        End If
    Next Counter
End Sub

But It returns an error: Else without IF. What should I do?

Thanks in advance.

**RHCPgergo** · 01-09-2013, 03:29 AM

Hi,

the macro should run if you put a line break in the first "If" line. (Explanation: if the whole "If...Then" session is in one line, you don't need "End if".)

Sub Test()
    For Counter = 1 To 10000
        Set curCell = Worksheets("Sheet1").Cells(Counter, 4)
        '  'Books', 'Foods', 'Fruits
        If curCell.Value = "Books" Then
            Worksheets("Sheet1").Cells(Counter, 5) = "01"
        ElseIf curCell.Value = "Foods" Then Worksheets("Sheet1").Cells(Counter, 5) = "02"
        ElseIf curCell.Value = "Fruits" Then Worksheets("Sheet1").Cells(Counter, 5) = "03"
        End If
    Next Counter
End Sub

**jraj1106** · 01-09-2013, 03:31 AM

Hi

Try this:

assignvalues.xlsm

The code for this is:

Private Sub CommandButton1_Click()
    Dim rcnt As Long
    
    rcnt = Range("D" & Rows.Count).End(xlUp).Row
    
    For i = 2 To rcnt
        If InStr(1, Range("D" & i).Value, "Books") = 1 Then
            Range("E" & i).Value = 1
        ElseIf InStr(1, Range("D" & i).Value, "Food") = 1 Then
            Range("E" & i).Value = 2
        ElseIf InStr(1, Range("D" & i).Value, "Fruits") = 1 Then
            Range("E" & i).Value = 3
        End If
    Next
End Sub

I have a button and the above code is for that button.

**OllieB** · 01-09-2013, 03:35 AM

Many solutions possible

Sub Test()
     With Worksheets("Sheet1")
          For Counter = 1 To .Range("D").CurrentRegion.Rows.Count
               .Cells(Counter, "E").Value = Switch(.Cells(Counter, "D").Value = "Books", "01", .Cells(Counter, "D").Value = "Foods", "02", .Cells(Counter, "D").Value = "Fruits", "03")
          Next Counter
     End With
End Sub

**Toton6868** · 01-09-2013, 03:36 AM

Thank you. But there is a problem also. Actually I want to find Books, Fruits or foods from a group of string of Cell and return 01, 02, 03. In my Selected cell It contains "Amin Books", "BV Foods", "Fresh Fruits". So I think the code need some changes. I also want to ignore case sensitivity.

**jraj1106** · 01-09-2013, 03:43 AM

Now try this code:

Private Sub CommandButton1_Click()
    Dim rcnt As Long
    
    rcnt = Range("D" & Rows.Count).End(xlUp).Row
    
    For i = 2 To rcnt
        If InStr(1, Range("D" & i).Value, "Books") > 0 Then
            Range("E" & i).Value = 1
        ElseIf InStr(1, Range("D" & i).Value, "Food") > 0 Then
            Range("E" & i).Value = 2
        ElseIf InStr(1, Range("D" & i).Value, "Fruits") > 0 Then
            Range("E" & i).Value = 3
        End If
    Next
End Sub

**Toton6868** · 01-09-2013, 03:49 AM

Just Last one thing. If I want to input string instead of number what should I do (Such as 01 instead of 1). I tried

Range("E" & i).Value = "03"

instead of

Range("E" & i).Value = 3

**OllieB** · 01-09-2013, 03:58 AM

@jraj1106, I do not want to hijack your solution but Toton also requested case insensitive matching so I suggest to add using UCASE to both the contents of the cell and the search string

**jraj1106** · 01-09-2013, 04:02 AM

Okay. You can do it.

**Toton6868** · 01-09-2013, 04:13 AM

@jraj1106....So what will be the final code boss? I need string in output and case insensitivity.

**OllieB** · 01-09-2013, 04:23 AM

Private Sub CommandButton1_Click()
     Dim rcnt As Long
     With ActiveSheet
          rcnt = .Range("D" & Rows.Count).End(xlUp).Row
          For i = 2 To rcnt
               CellValue = UCase$(.Range("D" & i).Value)
               If InStr(1, CellValue, "BOOKS") > 0 Then
                    .Range("E" & i).Value = "01"
               ElseIf InStr(1, CellValue, "FOOD") > 0 Then
                    .Range("E" & i).Value = "02"
               ElseIf InStr(1, CellValue, "FRUITS") > 0 Then
                    .Range("E" & i).Value = "03"
               End If
          Next i
     End With
End Sub

**Toton6868** · 01-09-2013, 05:25 AM

Its solved the case problem but still its giving 1, 2 and 3 instead of 01, 02 and 03 respectively.

**jraj1106** · 01-09-2013, 06:01 AM

Change the format of col E to text format. Then run the macro.

**Toton6868** · 01-10-2013, 07:14 AM

Originally Posted by jraj1106

Change the format of col E to text format. Then run the macro.

Thanks a lot...

VBA Macro for search specific word and input specific value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Re: VBA Macro for search specific word and input specific value

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions