search and report

Shmim

New Member
Joined
Jul 6, 2012
Messages
6
Hi,

I hope this is an easy one. I have a worksheet with 4 columns of info in it. Looks like this

1089 NA
10906/19/12NAAlready done
10916/8/12NA
10924/26/12NA
10934/4/12NA
10944/26/12NA

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>


I manually search by the number in column A and see if there is a date in column B, if yes, then I look at the data in column C and D to see whats there. The search set I use is variable and comes from a different program. I would like to be able to use a tab in the workbook to paste my search set in column a, run a search and then report any numbers with data in column B, as well as the data included in the other two columns to another tab or set of columns in the workbook.
So the logic seems pretty straight forward : I manually fill a search set, run search, IF Data in Column B for any of the numbers searched Then Report row to ? (I dont care where just so I can find it)

Any help would be appreciated. I'm still a novice and this one is over my head.

Thanks
SHmim
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I hope this is an easy one. I have a worksheet with 4 columns of info in it. Looks like this

1089
NA
1090
6/19/12
NA
Already done
1091
6/8/12
NA
1092
4/26/12
NA
1093
4/4/12
NA
1094
4/26/12
NA

<TBODY>
</TBODY>


I manually search by the number in column A and see if there is a date in column B, if yes, then I look at the data in column C and D to see whats there. The search set I use is variable and comes from a different program. I would like to be able to use a tab in the workbook to paste my search set in column a, run a search and then report any numbers with data in column B, as well as the data included in the other two columns to another tab or set of columns in the workbook.
So the logic seems pretty straight forward : I manually fill a search set, run search, IF Data in Column B for any of the numbers searched Then Report row to ? (I dont care where just so I can find it)

Any help would be appreciated. I'm still a novice and this one is over my head.

Thanks
SHmim

I used sheet2 as the sheet for your external source data, but you can make it whatever sheet you want by changing the line: RptSh = Sheets(2)
This procedure walks down column A of the external source data sheet and looks for each item in column A of your local worksheet. If a match is found, it looks at column B for any data, if not blank, it will copy any data in columns B:D to the external data sheet on the same row as the matched item.

Code:
Sub Rept()
Dim sh As Worksheet, lr As Long, rng As Range, newSh As Worksheet, lr2 As Long, sItem As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set RptSh = Sheets(2) 'Edit sheet name
lr2 = RptSh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = RptSh.Range("A2:A" & lr2)
For Each c In rng
Set sItem = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues)
If Not sItem Is Nothing Then
If sItem.Offset(0, 1) > "" Then
sItem.Offset(0, 1).Resize(1, 3).Copy c.Offset(0, 1)
End If
End If
Next
End Sub
Code:
 

Shmim

New Member
Joined
Jul 6, 2012
Messages
6
Wow Thank you! ummm I screwed up tho lol. I'm feeling a little silly right now, maybe i'll blame it on the heat wave. I reversed my logic. I am actually looking for the rows that have "Blank" in column b, to be reported not the other way around as I originally asked. Basically i have a set of numbers Im looking for, and if they have a date in column b it means that they have been done and i need not worry about them, if they Do Not have a date in column b, it means I need to work on them. sorry about the mix up and thank you so much for your time.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Wow Thank you! ummm I screwed up tho lol. I'm feeling a little silly right now, maybe i'll blame it on the heat wave. I reversed my logic. I am actually looking for the rows that have "Blank" in column b, to be reported not the other way around as I originally asked. Basically i have a set of numbers Im looking for, and if they have a date in column b it means that they have been done and i need not worry about them, if they Do Not have a date in column b, it means I need to work on them. sorry about the mix up and thank you so much for your time.

That's an easy fix.

Code:
Sub Rept2()
Dim sh As Worksheet, lr As Long, rng As Range, newSh As Worksheet, lr2 As Long, sItem As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set RptSh = Sheets(2) 'Edit sheet name
lr2 = RptSh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = RptSh.Range("A2:A" & lr2)
For Each c In rng
Set sItem = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues)
If Not sItem Is Nothing Then
If sItem.Offset(0, 1) = "" Then
sItem.Offset(0, 2).Resize(1, 2).Copy c.Offset(0, 2)
End If
End If
Next
End Sub
Code:
 

Shmim

New Member
Joined
Jul 6, 2012
Messages
6

ADVERTISEMENT

Ya know what. I just ran this to play with it and it does exactly what i need it to. It tells me what I need to know right there. Thank you so much for your help!
 

Shmim

New Member
Joined
Jul 6, 2012
Messages
6
ok im sorry but I ran the first code once, and it worked, however I deleted it and tried the second code and now no matter what I do i get a compile error. I can only assume since it worked the first time that I messed something up. any ideas? questions?

Shmim
 

Shmim

New Member
Joined
Jul 6, 2012
Messages
6

ADVERTISEMENT

The error Im getting says "Invalid outside procedure"
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
That error occurs when there is something in the module that the code tries to accomodate but it won't compute. Suggest you wipe it all out and recopy and paste the revised version that was posted. See if that works.
 

Shmim

New Member
Joined
Jul 6, 2012
Messages
6
I made a silly mistake and now I have it working. Thank you. If you are looking for a challenge, I have your two pieces of code, plus a third to delete rows that are left (after i run the first two) with a date in column b. So I basically run these 1,2,3 and end up with what I am looking for. my question is: can these be combined as they are. I've tried a few ways to combine them to run in the order that i manually run them, and I keep getting errors.

the steps are follows

Code:
Sub Rept1()
Dim sh As Worksheet, lr As Long, rng As Range, newSh As Worksheet, lr2 As Long, sItem As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set RptSh = Sheets(2) 'Edit sheet name
lr2 = RptSh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = RptSh.Range("A2:A" & lr2)
For Each c In rng
Set sItem = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues)
If Not sItem Is Nothing Then
If sItem.Offset(0, 1) = "" Then
sItem.Offset(0, 2).Resize(1, 2).Copy c.Offset(0, 2)
End If
End If
Next
End Sub
Code:

Code:
Sub Rept2()
Dim sh As Worksheet, lr As Long, rng As Range, newSh As Worksheet, lr2 As Long, sItem As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 3).End(xlUp).Row
Set RptSh = Sheets(2) 'Edit sheet name
lr2 = RptSh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = RptSh.Range("A2:A" & lr2)
For Each c In rng
Set sItem = sh.Range("A2:A" & lr).Find(c.Value, LookIn:=xlValues)
If Not sItem Is Nothing Then
If sItem.Offset(0, 1) > "" Then

sItem.Offset(0, 1).Resize(1, 3).Copy c.Offset(0, 1)
End If
End If
Next
End Sub
Code:

Code:
Sub Rept3Dlt()
Dim i As Long, lr As Long
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


For i = lr To 2 Step -1
If InStr(1, Cells(i, 2).Value, "/", vbTextCompare) > 0 Then
Cells(i, 2).EntireRow.delete
End If
Next i
End Sub
Code:

I would attach a sample workbook but seems unnecessary. Here is a sample of the data I am looking thru:

57075/14/12NA
57083/26/12NA
5709 NA16M
57104/24/12NA
5711 NA
57125/21/12NA
57132/24/12NA
5714 NA
5715 NA
57164/2/12NA16M
57174/23/12NA
57185/7/12NA
5719
57205/1/12na
5721
57225/11/120612WA03888
57234/26/120910WA02891
57246/15/120909WA02581
57254/29/100508wa02446
57266/18/120512WA01356
57275/1/120611wa01871
57285/8/120612WA03884
57293/27/120502WA01666





<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Basically I need to keep any row that Does not have a date in it and I need the data in cells C and D to be included. I know that some of the functions in this code are redundant but the result is exactly what I need. So I'm just banging sticks and rocks together, and If I am asking too much please just say so, I don't want to put you out. I appreciate your time thus far and thank you.

Shmim
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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
Top