how to supplement this macro?Closed

Question

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

venkat1926 · Answer

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?

venkat1926 · Answer

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

How to supplement this macro?

2 responses

Similar discussions

Subscribe To Our Newsletter!