Hi guys,

I have a similar table:

Column A contains the search data.

Column C is for inserting a formula.

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?

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 "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?

VBA Code:

```
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
```

Last edited: