I know I am asking alot but I'm lost

[austenr]

I need some major help from anyone on this. My skill level is not that high and I want to learn how. If someone is willing to help please read the zip file. Thanks in advance.

[mdmackillop]

Hi Austenr,
Can you zip and post the spreadsheet?
MD

[Zack Barresse]

Hi,

The attachment in your first post is a text file. Was it supposed to be a .xls file?

[austenr]

Here is the EXCEL file for those of you that have asked for it

[Zack Barresse]

In your text file, you describe file A and file B, which file will this macro be run from?

[austenr]

The Macro would be ran from file A.

[Anne Troy]

BIG contributions due at the end of the year, austen. BIG.
ROFL!!

:D
I wouldn't want to have to do that job either.

[Zack Barresse]

Okay, getting this straight..

You only want a row 'flagged' if there is a blank in Col C of FileA. If there is a blank, you want that row (in FileA) along with the row above it to be transferred to a report of sorts. Then continue on down col C in FileA looking for blanks. Print all 3 (FileA, FileB & report) when done.

Do you want to save the report? Is that going to be it's own workbook if so? What are the chances of multiple blanks in Col C of FileA back to back? Curious.

[austenr]

there is not a possibility of back to back blanks. The report should be saved to a seperate sheet. Hope that helps.

[Zack Barresse]

From your notepad and your posts, it seems there are two issues here. 1) You want to check for blanks in FileA, col C. Copy blank row and row above it to another 'report' sheet. 2) You want to check FileB for any values from FileA that it does not posess.

What else is missing here? Sounds like 2 seperate functions here..

[austenr]

Lets try to make this as simple as possible if there is such a thing in this case.

If there is a blank cell in column "C", print the line then return to the matching
routine. If someone can tell me how to do it, that is what the sticking point is.
Perhaps checking the next line before you write anything would be easiest,
but I am just guessing.

[Zack Barresse]

Okay, not sure if this is what you're looking for ...

Option Explicit
Sub checkFile()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim cel As Range, rng As Range, mrSht As String
Set rng = Sheets("Sheet1").Range("C1", Sheets("Sheet1").Range("C65536").End(xlUp))
Sheets.Add before:=Sheets(1)
On Error GoTo badName
ActiveSheet.Name = "MatchReport"
GoTo goodName
badName:
MsgBox "There has been an error. Please try again.", vbOKOnly + vbCritical, "ERROR"
ActiveSheet.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Exit Sub
goodName:
mrSht = ActiveSheet.Name
With Sheets(mrSht)
.Cells.Clear
.[A1].Value = "Master Report"
End With
For Each cel In rng
If cel.Value = "" Then
cel.EntireRow.Copy Destination:=Sheets(mrSht).Range("A65536").End(xlUp).Offset(1)
End If
Next cel
Sheets(mrSht).Cells.EntireColumn.AutoFit
Sheets(mrSht).PrintOut copies:=1
Sheets("Sheet1").PrintOut copies:=1
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
This will only take the line with a blank in column C. If you want to take that row and the row above, try adding something like this ...


cel.Offset(-1).EntireRow.Copy Destination:=Sheets(mrSht).Range("A65536").End(xlUp).Offset(1)
right after this line...


If cel.Value = "" Then

[austenr]

thanks so much...will try it tomorrow from work. i will let you know

[Jacob Hilderbrand]

austenr

Were you able to get everything working the way you wanted?

[austenr]

yes thanks

[Zack Barresse]

Okay, I marked this Solved then. If you still have more items you need help on, please don't hesitate to post it. :)