VBA

Slavio

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi guys,
I have a similar table:
Column A contains the search data.
Column C is for inserting a formula.
ABC
CarBig=CONCATENATE(A1;" ";B1) 'Result (Auto Big)
CarSmall=CONCATENATE(A2;" ";B2) 'Result (Auto Small)
BikeRoad=CONCATENATE(B3;" ";A3) 'Result (Big Bike)
BikeMountain=CONCATENATE(B4;" ";A4) 'Result (Road Bike)
BikeYellow=CONCATENATE(B5;" ";A5) 'Result (Mountain Bike)
AppleRed=CONCATENATE(A6;" ";B6) 'Result (Red Apple)
AppleGreen=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?
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:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,327
Office Version
  1. 2010
Platform
  1. Windows
why are you inserting formulae into the cells in C? cant you just put the result
 

Slavio

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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

Well-known Member
Joined
May 20, 2004
Messages
3,327
Office Version
  1. 2010
Platform
  1. Windows
see if this makes a difference:
change these lines
VBA Code:
        If InStr(iCell.Value, strSearch) Then

to

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

Slavio

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

see if this makes a difference:
change these lines
VBA Code:
        If InStr(iCell.Value, strSearch) Then

to

VBA Code:
        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

Well-known Member
Joined
May 20, 2004
Messages
3,327
Office Version
  1. 2010
Platform
  1. Windows
this is structurally correct and should run

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

New Member
Joined
Mar 28, 2021
Messages
39
Office Version
  1. 365
Platform
  1. Windows
  2. Web
this is structurally correct and should run

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)

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

Watch MrExcel Video

Forum statistics

Threads
1,132,908
Messages
5,655,898
Members
418,250
Latest member
Jebacmakro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top