Excel Macro Question PLEASE HELP!

[Plouffe21]

Okay, I am very very new to Excel Macros but I know the power of them. I'll get straight to the point, I want to make a Macro that it can check a column of cells individually to see if it has a particular number of digits, (9 in this case for P.O. number), then depending on what is in the cell, have it yield a variety of different results into a separate worksheet. For example, I get 8,000 lines of data daily of vendors fedex data, some put P.O. numbers in, some don't and some just put in the wrong field or wrong format. I want to make a macro that will automatically go through reference field and p.o. field columns and tell me how many are correct and how many are wrong.

From here I probably could do the rest to generate charts and graphs to display the data. I don't even know if this is possible, please help!

 

[John Davis]

Okay, I am very very new to Excel Macros but I know the power of them. I'll get straight to the point, I want to make a Macro that it can check a column of cells individually to see if it has a particular number of digits, (9 in this case for P.O. number), then depending on what is in the cell, have it yield a variety of different results into a separate worksheet. For example, I get 8,000 lines of data daily of vendors fedex data, some put P.O. numbers in, some don't and some just put in the wrong field or wrong format. I want to make a macro that will automatically go through reference field and p.o. field columns and tell me how many are correct and how many are wrong.

From here I probably could do the rest to generate charts and graphs to display the data. I don't even know if this is possible, please help!

Nothing fancy, but maybe something like this could get you started:

Sub Plouffe21()

'This uses Column F as PO Field

Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 5).End(3).Row

Columns("G:H").Insert Shift:=xlToRight

Range("G2:G" & lr).Formula = "=IF(LEN(F2)=9,""COUNT"","""")"
Range("G2:G" & lr).Value = Range("G2:G" & lr).Value
Range("H2").Formula = "=COUNTIF(G2:G1000,""=COUNT"")"
Range("H3").Formula = "=COUNTIF(G2:G1000,""<>COUNT"")"
Range("H2:H3").Value = Range("H2:H3").Value
MsgBox "there are " & Range("H2").Value & " correct PO Nos and " & Range("H3").Value & " incorrect PO Nos."

Columns("G:H").Delete Shift:=xlToLeft


End Sub