How to supplement this macro?

[Closed]
Report
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
 Dg83 -
Hi Guys,

I was advised the below macro which works wonderfully, searches for the desired cells from one column and copies them to where I want. I wonder if there is a possibility to integrate in this macro a condition to find the desired cell when the search word is not an exact match. For example I am looking for Jenkinson but I enter jenkins. With the below formula I would not be able to have the desired results returned because this is not an exact match. Any way to remedy this?
thanks so much!


Sub test()
Dim lRow As Integer
Dim SearchName As String
lRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

SearchName = InputBox("Please input name:")

For Each cell In Sheets("Sheet2").Range("A1:A" & lRow)
If cell.Value = SearchName Then
Range(cell, cell.Offset(0, 4)).Copy
Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Next

End Sub

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
must be possible to use wildcard like
"*jenk*"


do an experiment

type jenkins in A1

in immediate window type
?cells.Find (what:="*jenk*").Address

and hit enter key you get A1


will this help you to modify the macro?
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

thanks venkat1926, unfortunately I was not able to do do what you have suggested. Did you mean to enter ?cells.Find (what:="*jenk*").Address into B1? or to enter it as a macro?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
probably you do not know what is "immediate" window . this is the window to test any code statement while writing the macro in themodule.

open vb editor and hit control+R.you will see the list of all open files. highlight your relevant file and click insert(menu in vb editor)-module
a window opens (on the right side) which is a module
now click view(menu in vb editor)-immediate window ., a small window opens below the module window. that is immediate window
now follow instructions in the previous message. and if you are satsified you can use this in your macro

if you do not want a code then Jenkinson in A1
control+F
click option uncheck "match entire cell contents" if it is checked otherwise leave it
click find next. the cursor will got to A1

use these ideas to write a macro

immediate window is useful window to check you code statement and tweak or write macros
Thanks so much, will try this out. Fascinating to be able to learn new things.