search and report

[Shmim]

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

<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

 

[JLGWhiz]

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]

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]

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]

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]

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]

The error Im getting says "Invalid outside procedure"

 

[JLGWhiz]

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]

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:

5707	5/14/12	NA
5708	3/26/12	NA
5709		NA	16M
5710	4/24/12	NA
5711		NA
5712	5/21/12	NA
5713	2/24/12	NA
5714		NA
5715		NA
5716	4/2/12	NA	16M
5717	4/23/12	NA
5718	5/7/12	NA
5719
5720	5/1/12	na
5721
5722	5/11/12	0612WA03888
5723	4/26/12	0910WA02891
5724	6/15/12	0909WA02581
5725	4/29/10	0508wa02446
5726	6/18/12	0512WA01356
5727	5/1/12	0611wa01871
5728	5/8/12	0612WA03884
5729	3/27/12	0502WA01666

<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