Formula be replaced with macro

K0st4din

Active Member
Joined
Feb 8, 2012
Messages
488
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Hello,
can anyone tell me and help whether this kind of formula can be replaced by macro and perform the same function. This is just an example, then I will change my columns.
The idea is to search in one column (chosen by me) and the string of text, if I find search terms to bring them back again for each row in another column.
For example, looking at column A2:A and returns in column B2:B
Code:
=REPLACE(REPT("+AF266";COUNTIF(A2;"*AF266*"))&REPT("+AF267";COUNTIF(A2;"*AF267*"))&REPT("+AF268";COUNTIF(A2;"*AF268*"))&REPT("+AF269";COUNTIF(A2;"*AF269*"))&REPT("+AF311";COUNTIF(A2;"*AF311*"))&REPT("+CF706";COUNTIF(A2;"*CF706*"))&REPT("+CF707";COUNTIF(A2;"*CF707*"))&REPT("+CF708";COUNTIF(A2;"*CF708*"))&REPT("+CF512";COUNTIF(A2;"*CF512*"))&REPT("+CF508";COUNTIF(A2;"*CF508*"))&REPT("+CF437";COUNTIF(A2;"*CF437*"))&REPT("+CF648";COUNTIF(A2;"*CF648*"))&REPT("+CF649";COUNTIF(A2;"*CF649*"))&REPT("+CF444";COUNTIF(A2;"*CF444*"))&REPT("+HF095";COUNTIF(A2;"*HF095*"))&REPT("+NF520";COUNTIF(A2;"*NF520*"))&REPT("+NF521";COUNTIF(A2;"*NF521*"))&REPT("+NF522";COUNTIF(A2;"*NF522*"))&REPT("+NF523";COUNTIF(A2;"*NF523*"))&REPT("+AF386";COUNTIF(A2;"*AF386*"));1;1;"")

Thanks in advance.
 
Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Friends, if anyone could help me?
 
Upvote 0
Friends, Can anybody help me? Is it impossible to find a solution? Please someone help me "hit a shoulder".
 
Upvote 0
Give this macro a try. First, it will ask you to select any cell in the column containing your data, then it will ask you to select any cell in the column where you want the output to go to.... after that it will calculate the text strings you want and place them.

Code:
Sub CheckCellsForCodes()
  Dim X As Long, Combo As String, Codes As Variant
  Dim Cell As Range, DataCol As Range, OutCol As Range
  Codes = Array("AF266", "AF267", "AF268", "AF269", "AF311", "CF706", "CF707", _
                "CF708", "CF512", "CF508", "CF437", "CF648", "CF649", "CF444", _
                "HF095", "NF520", "NF521", "NF522", "NF523", "AF386")
  Set DataCol = Application.InputBox("Please select any cell in the column with your data.", Type:=8)
  Set OutCol = Application.InputBox("Please select any cell in the column for the output.", Type:=8)
  For Each Cell In Range(Cells(2, DataCol.Column), Cells(Rows.Count, DataCol.Column).End(xlUp))
    Combo = ""
    For X = LBound(Codes) To UBound(Codes)
      If InStr(Cell.Value, Codes(X)) Then Combo = Combo & "+" & Codes(X)
    Next
    Cells(Cell.Row, OutCol.Column).Value = Mid(Combo, 2)
  Next
End Sub
 
Upvote 0
Hello and thank you very much for your help.
I have only one question and I hope you can solve a problem. I just gave an example, but in my table and I'll have words when I tried to find a word if I did not fit properly , then do not macro finds her .
Here is what I have in mind:
If you write orange ( looking ) and it is written in lowercase (for example) it finds it , but if it is written in capital letters did not detect it .
The reference I can you make something that any letters to be written to be able to detect them , because otherwise , I'll miss having to make innumerable combinations ?
Ie in the macro as you wrote the word "orange", and if it is written in orange or ORAnge or ORANGE or whatever to show me.
Thank you very much.
 
Upvote 0
Hello and thank you very much for your help.
I have only one question and I hope you can solve a problem. I just gave an example, but in my table and I'll have words when I tried to find a word if I did not fit properly , then do not macro finds her .
Here is what I have in mind:
If you write orange ( looking ) and it is written in lowercase (for example) it finds it , but if it is written in capital letters did not detect it .
The reference I can you make something that any letters to be written to be able to detect them , because otherwise , I'll miss having to make innumerable combinations ?
Ie in the macro as you wrote the word "orange", and if it is written in orange or ORAnge or ORANGE or whatever to show me.
Thank you very much.

Do you mean besides text that looks like "AF266", "AF267", etc., you also have words? If so, give this macro a try...

Code:
Sub CheckCellsForCodes()
  Dim X As Long, Combo As String, Codes As Variant
  Dim Cell As Range, DataCol As Range, OutCol As Range
  Codes = Array("AF266", "AF267", "AF268", "AF269", "AF311", "CF706", "CF707", _
                "CF708", "CF512", "CF508", "CF437", "CF648", "CF649", "CF444", _
                "HF095", "NF520", "NF521", "NF522", "NF523", "AF386")
  Set DataCol = Application.InputBox("Please select any cell in the column with your data.", Type:=8)
  Set OutCol = Application.InputBox("Please select any cell in the column for the output.", Type:=8)
  For Each Cell In Range(Cells(2, DataCol.Column), Cells(Rows.Count, DataCol.Column).End(xlUp))
    Combo = ""
    For X = LBound(Codes) To UBound(Codes)
      If InStr(1, Cell.Value, Codes(X), vbTextCompare) Then Combo = Combo & "+" & Codes(X)
    Next
    Cells(Cell.Row, OutCol.Column).Value = Mid(Combo, 2)
  Next
End Sub
 
Upvote 0
Hello, I mean that in addition to such codes as I have done, I could have only words.
Ie second proposal is looking for words, no matter how small or written in large letters or randomly. Now works perfectly.
Again thank you warmly.
Be alive and well.
 
Upvote 0
Hello
I would like to ask you one more addition - in this formula that I have done, can be done, so if looking for a word to show me a code or another word. This macro can I do so that I can ask a condition like this: If you find this or that word, show me these letters?
Example:
peach, apricot, banana 4 pcs - Pea+Apr+Ban 4
But the idea is to be written and all the action happens in the macro automatically.
Thank you very much for your cooperation on your part.
 
Upvote 0
Sir, would it be possible to help me a little, of course if you can add to this macro.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top