VBA Coding: Please help!

[sealion1]

Hi all,

I was wondering if somebody could help me code the following in VBA - I know a little bit about VBA, but am struggling with this.

I am looking at a way of searching column B2 in my "Raw Data" tab for keywords that are in my "Keywords" tab. I then want to copy any of the matching rows to my "Dashboard" tab.

I have included a copy of my spreadsheet to this post.

Thanks so much in advance!

 

[Mike H..]

Hi all,

I was wondering if somebody could help me code the following in VBA - I know a little bit about VBA, but am struggling with this.

I am looking at a way of searching column B2 in my "Raw Data" tab for keywords that are in my "Keywords" tab. I then want to copy any of the matching rows to my "Dashboard" tab.

I have included a copy of my spreadsheet to this post.

Thanks so much in advance!

Have a look at your workbook and click the button.

 

[sealion1]

Hi Mike,

That works brilliantly - thank you.

In the actual spreadsheet that I need to use at work, the "raw data" is actually drawn from row "AG" - where do I change this in the coding?

Thanks.

 

[Mike H..]

Hi Mike,

That works brilliantly - thank you.

In the actual spreadsheet that I need to use at work, the "raw data" is actually drawn from row "AG" - where do I change this in the coding?

Thanks.

Hi,

This now looks at column AG

Sub somesub()
Dim LastRow As Long, c As Range
Dim LastRow1 As Long, LastRow2 As Long
Dim MyRange As Range, ChkRange As Range
Dim Src As Worksheet
Dim Dst As Worksheet
Set Src = Sheets("Raw Data")
Set Dst = Sheets("Dashboard")
LastRow1 = Sheets("Keywords").Cells(Rows.Count, "A").End(xlUp).Row
Set ChkRange = Sheets("Keywords").Range("A2:A" & LastRow1)
LastRow = Src.Cells(Rows.Count, "AG").End(xlUp).Row
Set MyRange = Src.Range("AG2:AG" & LastRow)
For Each c In MyRange
If Not IsError(Application.Match(c.Value, ChkRange, 0)) Then
LastRow2 = Dst.Cells(Rows.Count, "A").End(xlUp).Row + 1
c.EntireRow.Copy Dst.Cells(LastRow2, "A")

End If
Next
End Sub

 

[sealion1]

Again - brilliant Mike, thanks!

Last question, the data is column AG is going to have the keyword in, but it could be mixed in with other words, for example;

Warehouse 13, X Lane 1, Roehampton
Unit 88 - 92, X Lane 12, Hammersmith

Will the code above still pick this out or will it bypass it thinking that the data doesn't 100% match?

Thanks.

 

[Mike H..]

Again - brilliant Mike, thanks!

Last question, the data is column AG is going to have the keyword in, but it could be mixed in with other words, for example;

Warehouse 13, X Lane 1, Roehampton
Unit 88 - 92, X Lane 12, Hammersmith

Will the code above still pick this out or will it bypass it thinking that the data doesn't 100% match?

Thanks.

Hi,

The code now will only copy lines over if it finds the full string. What you want is doable but more complicated because if we search for (say) X lane 1 as a partial string it would fine that in X lane 12. I'm going out right now and if no one has given you a solution I'll work on it later.

 

[sealion1]

Thanks for all your help Mike - would really appreciate you taking a look when you get back later if no-one else has found a solution.

 

[Mike H..]

Thanks for all your help Mike - would really appreciate you taking a look when you get back later if no-one else has found a solution.

Hi,

I've based this code upon these 2 examples.

Warehouse 13, X Lane 1, Roehampton
Unit 88 - 92, X Lane 12, Hammersmith

I have assumed that there will always be a comma or space before/after the string we're looking for.