Match six digits and copy specified row

K

K

I have two sheets one is call "Data" and other one is call "Search".
I have data (see below) in Sheets("Data")

  A             B       C---- columns
555360     XX1    12  
555360 - 555361   XX2     13  
555360pg     XX3    14  
555360pg     XX4    15    
523600     GH5    16    
523600     GH6    17    
523600st    GH7    18    
589632     ZR2     19    
589632tr     ZR3     20    
589632tr     ZR4     21    

I have macro (see below) set on a button in Sheets("Search")

Sub srch()
Range("A5:C50000").ClearContents
DataShRowCount = 1
SrchShRowCount = 5
Srchnum = Sheets("Search").Range("A1").Value
With Sheets("Data")
Do While .Range("A" & DataShRowCount) <> ""
If .Range("A" & DataShRowCount) = Srchnum Then
Set CopyRange = .Range("A" & DataShRowCount & ":C" & DataShRowCount)
CopyRange.Copy Destination:=Sheets("Search").Range("A" &
SrchShRowCount)
SrchShRowCount = SrchShRowCount + 1
End If
DataShRowCount = DataShRowCount + 1
Loop
End With
End Sub

When I put six digit number in Range("A1") of sheet "Search" like for
example if put 555360 and by clicking the button macro will look for
that number in column A of sheet "Data" and copy row from column A to
C of that six digit into sheet "Search". But sometime i have extra
characters with those six digits as show in above data. At the moment
when i put 555360 and click the button i get result (see below)

  A             B       C---- columns
555360     XX1    12  

but I want result some thing like this

  A             B       C---- columns
555360     XX1    12  
555360 - 555361   XX2     13  
555360pg     XX3    14  
555360pg     XX4    15    

Macro should copy all those rows in which 555360 is appearing. And
also some time I have digit like 555360 - 555361 so if i put 555361in
"Search" sheet then macro should be able to find this row as well as
though these six digits are not starting from begining. I hope I was
able to explain my qestion. I know there is some code line needed in
above macro but I don’t know what. Can any friend can help
 
R

Rick Rothstein

Try changing this line...

If .Range("A" & DataShRowCount) = Srchnum Then

to this instead and see if that works for you...

If .Range("A" & DataShRowCount) Like "*" & Srchnum & "*" Then

--
Rick (MVP - Excel)


I have two sheets one is call "Data" and other one is call "Search".
I have data (see below) in Sheets("Data")

A B C---- columns
555360 XX1 12
555360 - 555361 XX2 13
555360pg XX3 14
555360pg XX4 15
523600 GH5 16
523600 GH6 17
523600st GH7 18
589632 ZR2 19
589632tr ZR3 20
589632tr ZR4 21

I have macro (see below) set on a button in Sheets("Search")

Sub srch()
Range("A5:C50000").ClearContents
DataShRowCount = 1
SrchShRowCount = 5
Srchnum = Sheets("Search").Range("A1").Value
With Sheets("Data")
Do While .Range("A" & DataShRowCount) <> ""
If .Range("A" & DataShRowCount) = Srchnum Then
Set CopyRange = .Range("A" & DataShRowCount & ":C" & DataShRowCount)
CopyRange.Copy Destination:=Sheets("Search").Range("A" &
SrchShRowCount)
SrchShRowCount = SrchShRowCount + 1
End If
DataShRowCount = DataShRowCount + 1
Loop
End With
End Sub

When I put six digit number in Range("A1") of sheet "Search" like for
example if put 555360 and by clicking the button macro will look for
that number in column A of sheet "Data" and copy row from column A to
C of that six digit into sheet "Search". But sometime i have extra
characters with those six digits as show in above data. At the moment
when i put 555360 and click the button i get result (see below)

A B C---- columns
555360 XX1 12

but I want result some thing like this

A B C---- columns
555360 XX1 12
555360 - 555361 XX2 13
555360pg XX3 14
555360pg XX4 15

Macro should copy all those rows in which 555360 is appearing. And
also some time I have digit like 555360 - 555361 so if i put 555361in
"Search" sheet then macro should be able to find this row as well as
though these six digits are not starting from begining. I hope I was
able to explain my qestion. I know there is some code line needed in
above macro but I don’t know what. Can any friend can help
 
P

Per Jessen

Hi

Try this:

If Left(.Range("A" & DataShRowCount), 6) = Srchnum Then

Regards,
Per

"K" <[email protected]> skrev i meddelelsen
I have two sheets one is call "Data" and other one is call "Search".
I have data (see below) in Sheets("Data")

A B C---- columns
555360 XX1 12
555360 - 555361 XX2 13
555360pg XX3 14
555360pg XX4 15
523600 GH5 16
523600 GH6 17
523600st GH7 18
589632 ZR2 19
589632tr ZR3 20
589632tr ZR4 21

I have macro (see below) set on a button in Sheets("Search")

Sub srch()
Range("A5:C50000").ClearContents
DataShRowCount = 1
SrchShRowCount = 5
Srchnum = Sheets("Search").Range("A1").Value
With Sheets("Data")
Do While .Range("A" & DataShRowCount) <> ""
If .Range("A" & DataShRowCount) = Srchnum Then
Set CopyRange = .Range("A" & DataShRowCount & ":C" & DataShRowCount)
CopyRange.Copy Destination:=Sheets("Search").Range("A" &
SrchShRowCount)
SrchShRowCount = SrchShRowCount + 1
End If
DataShRowCount = DataShRowCount + 1
Loop
End With
End Sub

When I put six digit number in Range("A1") of sheet "Search" like for
example if put 555360 and by clicking the button macro will look for
that number in column A of sheet "Data" and copy row from column A to
C of that six digit into sheet "Search". But sometime i have extra
characters with those six digits as show in above data. At the moment
when i put 555360 and click the button i get result (see below)

A B C---- columns
555360 XX1 12

but I want result some thing like this

A B C---- columns
555360 XX1 12
555360 - 555361 XX2 13
555360pg XX3 14
555360pg XX4 15

Macro should copy all those rows in which 555360 is appearing. And
also some time I have digit like 555360 - 555361 so if i put 555361in
"Search" sheet then macro should be able to find this row as well as
though these six digits are not starting from begining. I hope I was
able to explain my qestion. I know there is some code line needed in
above macro but I don’t know what. Can any friend can help
 
M

Mike H

A completely different approach. Paste code into sheet Search and run

Sub srch()
Dim CopyRange As Range
mystring = CStr(Range("A1").Value)
Range("A5:C50000").ClearContents
lastrow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("data").Range("A1:A" & lastrow)
For Each c In MyRange
If CStr(Left(c, Len(mystring))) = mystring Then
If CopyRange Is Nothing Then
Set CopyRange = c.Resize(, 3)
Else
Set CopyRange = Union(CopyRange, c.Resize(, 3))
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Copy Range("A5")
End If
End Sub

Mike
 
K

K

A completely different approach. Paste code into sheet Search and run

Sub srch()
Dim CopyRange As Range
mystring = CStr(Range("A1").Value)
Range("A5:C50000").ClearContents
lastrow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("data").Range("A1:A" & lastrow)
For Each c In MyRange
    If CStr(Left(c, Len(mystring))) = mystring Then
        If CopyRange Is Nothing Then
            Set CopyRange = c.Resize(, 3)
        Else
            Set CopyRange = Union(CopyRange, c.Resize(, 3))
        End If
    End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Copy Range("A5")
End If
End Sub

Mike













- Show quoted text -
Click to expand...

Thanks lot guys
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Progress Bar 1
Get Data from One sheet to Other 4
Check a 'Logsheet' for a match, before continuing a sub 3
Copy Array to Array, how to and should I? 13
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
OnTime Change_Event macro AND copy to sheet2 macro trouble 7
Copy row info but not in normal column sequence 6
Match Values and Copy Rows 3

Top