VBA

[Slavio]

Hi guys,
I have a similar table:
Column A contains the search data.
Column C is for inserting a formula.

A	B	C
Car	Big	=CONCATENATE(A1;" ";B1) 'Result (Auto Big)
Car	Small	=CONCATENATE(A2;" ";B2) 'Result (Auto Small)
Bike	Road	=CONCATENATE(B3;" ";A3) 'Result (Big Bike)
Bike	Mountain	=CONCATENATE(B4;" ";A4) 'Result (Road Bike)
Bike	Yellow	=CONCATENATE(B5;" ";A5) 'Result (Mountain Bike)
Apple	Red	=CONCATENATE(A6;" ";B6) 'Result (Red Apple)
Apple	Green	=CONCATENATE(A7;" ";B7) 'Result (Green Apple)

If the word "Car" is in Column A, use formula no. 1 and insert it into cell C.
If the word "Bicycle" is in column A, use formula no. 2 and insert it into cell C.
If the word "Apple" is in Column A, use formula no. 3 and insert it into cell C.
If the cell in column A is empty, then nothing. Continue until there are 3 empty cells in a row.

I'm working on this code but it doesn't work Could you please help me?

Sub FindInCollA()

    Dim rngEnd      As Range
    Dim rngBeg      As Range
    Dim iCell       As Range
    Dim strSearch   As String
    Dim mylastCell  As String

    Set rngBeg = Range("A1")
    Set rngEnd = Range("A" & Range("A1").End(xlDown).Row)

    strSearch = "Car"
    strSearch2 = "Bike"
    strSearch3 = "Apple"
  
    ' If the word "Car" is in Column A, use formula no. 1 and insert it into cell C.
    If strSearch = "Car" Then
    For Each iCell In Range(rngBeg, rngEnd)
        If InStr(iCell.Value, strSearch) Then
            iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-3],"" "",RC[-2])"
        End If
  
    'If the word "Bicycle" is in column A, use formula no. 2 and insert it into cell C.
    ElseIf strSearch2 = "Bike" Then
    For Each iCell In Range(rngBeg, rngEnd)
        If InStr(iCell.Value, strSearch2) Then
            iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-2],"" "",RC[-3])"
        End If

    'If the word "Apple" is in Column A, use formula no. 3 and insert it into cell C.
    ElseIf strSearch3 = "Apple" Then
    For Each iCell In Range(rngBeg, rngEnd)
        If InStr(iCell.Value, strSearch3) Then
            iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-2],"" "",RC[-3])"
        End If
     
    Next iCell
  
    '??? Do While ActiveCell.Row < 3 lastrow
    ' mylastCell = "(LastCell +2)"
  
    End If
  
End Sub

 

[diddi]

why are you inserting formulae into the cells in C? cant you just put the result

 

[Slavio]

why are you inserting formulae into the cells in C? cant you just put the result

I have to add formulas at the end. First, I compose a spreadsheet from different sheets with data that is constantly changing.

 

[diddi]

see if this makes a difference:
change these lines

        If InStr(iCell.Value, strSearch) Then

to

        If InStr(iCell.Value, strSearch) >0 Then

 

[Slavio]

see if this makes a difference:
change these lines

        If InStr(iCell.Value, strSearch) Then

to

        If InStr(iCell.Value, strSearch) >0 Then

Thank You diddi, It probably won't be enough. "Elseif" and "If" I have defined badly, wants to fix it just don't know how.

 

[diddi]

this is structurally correct and should run

Sub FindInCollA()
    Dim rngEnd      As Range
    Dim rngBeg      As Range
    Dim iCell       As Range
    Dim strSearch   As String
    Dim mylastCell  As String
   
    Set rngBeg = Range("A1")
    Set rngEnd = Range("A" & Range("A1").End(xlDown).Row)
   
    Select Case strSearch
   
        ' If the word "Car" is in Column A, use formula no. 1 and insert it into cell C.
        Case "Car"
            For Each iCell In Range(rngBeg, rngEnd)
                If InStr(iCell.Value, strSearch) > 0 Then
                    iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-3],"" "",RC[-2])"
                End If
            Next iCell
     
        'If the word "Bicycle" is in column A, use formula no. 2 and insert it into cell C.
        Case "Bicycle"
            For Each iCell In Range(rngBeg, rngEnd)
                If InStr(iCell.Value, strSearch) > 0 Then
                    iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-2],"" "",RC[-3])"
                End If
            Next iCell
   
        'If the word "Apple" is in Column A, use formula no. 3 and insert it into cell C.
            Case "Apple"
            For Each iCell In Range(rngBeg, rngEnd)
                If InStr(iCell.Value, strSearch) > 0 Then
                    iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-2],"" "",RC[-3])"
                End If
            Next iCell
   
    '??? Do While ActiveCell.Row < 3 lastrow
    ' mylastCell = "(LastCell +2)"
   
    End Select
End Sub

 

[Slavio]

this is structurally correct and should run

Sub FindInCollA()
    Dim rngEnd      As Range
    Dim rngBeg      As Range
    Dim iCell       As Range
    Dim strSearch   As String
    Dim mylastCell  As String

    Set rngBeg = Range("A1")
    Set rngEnd = Range("A" & Range("A1").End(xlDown).Row)

    Select Case strSearch

        ' If the word "Car" is in Column A, use formula no. 1 and insert it into cell C.
        Case "Car"
            For Each iCell In Range(rngBeg, rngEnd)
                If InStr(iCell.Value, strSearch) > 0 Then
                    iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-3],"" "",RC[-2])"
                End If
            Next iCell
  
        'If the word "Bicycle" is in column A, use formula no. 2 and insert it into cell C.
        Case "Bicycle"
            For Each iCell In Range(rngBeg, rngEnd)
                If InStr(iCell.Value, strSearch) > 0 Then
                    iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-2],"" "",RC[-3])"
                End If
            Next iCell

        'If the word "Apple" is in Column A, use formula no. 3 and insert it into cell C.
            Case "Apple"
            For Each iCell In Range(rngBeg, rngEnd)
                If InStr(iCell.Value, strSearch) > 0 Then
                    iCell.Offset(0, 2).Value = "=CONCATENATE(RC[-2],"" "",RC[-3])"
                End If
            Next iCell

    '??? Do While ActiveCell.Row < 3 lastrow
    ' mylastCell = "(LastCell +2)"

    End Select
End Sub

diddi, thank you for removing the redundant "smog" from the code. I am learning and so I learned that: FOR EACH iCell statements but only one Next iCell statement. If strSearch = "Car" ElseIf strSearch2 = "Bike" and ElseIf strSearch3 = "Apple" are all redundant because you set the value of the three variables at the top of the sub and they never change.
What else do I need to modify to make the code work and write the formula in column "C"?