check certain cells for data before saving file

[Anthony]

Hi all,

I have a macro used to save a worksheet, its placed on the DATA sheet.
is there a way so that when the user clicks this button each row 12:200 is
checked for empty cells in that row (cells B). If a row is found the ref
number in column A of each row is placed into next available row in Sheet3

For example :
A12 = 12345
A13 = 6789
A14 = 9876
A15 = 54321

the DATA sheet is populated with various data in rows 12:15
it is found that B12, L12, E14,F14,P15 all have no data, so the values of
A12,A14 and A15 are placed into next row in Sheet3

Result in sheet3

A2= 12345 (valuse of A12 in DATA Sheet)
A3= 9876 (value of A14 in DATA Sheet)
A4= 54321 (value of A15 in DATA sheet)

Finally a message box to state there is missing data, for example

"there is missing data from refs 12345 , 9876 , 54321" ie the list pasted
into sheet3

Hope somebody can help and understand my goal(s)

Many thanks in advance

 

[vezerid]

This macro does, I believe, what you are asking for:

Sub CheckMissing()
rn = 2
cnt = 0
For i = 12 To 200
For j = 2 To 16
If Sheets("DATA").Cells(i, j) = "" Then
j = 17
Sheets("sheet3").Cells(rn, 1) = Sheets("DATA").Cells(i, 1)
rn = rn + 1
If cnt = 0 Then
msg = "There is missing data from refs " &
Sheets("DATA").Cells(i, 1)
Else
msg = msg & ", " & Sheets("DATA").Cells(i, 1)
End If
cnt = cnt + 1
End If
Next j
Next i
If cnt = 0 Then
MsgBox "All rows filled"
Else
MsgBox msg
End If
End Sub


HTH
Kostis Vezerides

 

[Anthony]

Thanks for ur help
2 questions if I may?

1) is it possible to omit the check from column O of the check ie alow this
to be unpopulated
2) is it possible to remove the ,,,,,,,,,,,,,,,,,,,,,,,,,, at the end of the
msgbox for the unused rows??

thanks again

 

[vezerid]

For question #1: Yes it is, here is the modified routine:

Sub CheckMissing()
rn = 2
cnt = 0
For i = 12 To 200
For j = 2 To 16
If j <> 15 Then
If Sheets("DATA").Cells(i, j) = "" Then
j = 17
Sheets("sheet3").Cells(rn, 1) =
Sheets("DATA").Cells(i, 1)
rn = rn + 1
If cnt = 0 Then
msg = "There is missing data from refs " &
Sheets("DATA").Cells(i, 1)
Else
msg = msg & ", " & Sheets("DATA").Cells(i, 1)
End If
cnt = cnt + 1
End If
End If
Next j
Next i
If cnt = 0 Then
MsgBox "All rows filled"
Else
MsgBox msg
End If
End Sub

For question #2, I don't understand. Do you not wish the message to
appear? Do you not wish the offending refs to be listed in the
message?

Kostis

 

[Anthony]

,Thanks , I'll try the new code,

Question 2.....
Once the code is run the msg box appears with the message like this..

There is missing data from refs
45677,2345,12344,67678,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

I don't want the ',,,,,,,,,,,,,,,,,,,,,,' to appear

thanks

 

[vezerid]

The commas appear b/c you don't have data all the way down to 200, as
indicated in your original post. I changed the code to start checking
from row 2 and as far down as there are data (assuming no empty rows).

Sub CheckMissing()
rn = 2
i = 2
cnt = 0
While Sheets("DATA").Cells(i, 1) <> ""
For j = 2 To 16
If j <> 15 Then
If Sheets("DATA").Cells(i, j) = "" Then
j = 17
Sheets("sheet3").Cells(rn, 1) =
Sheets("DATA").Cells(i, 1)
rn = rn + 1
If cnt = 0 Then
msg = "There is missing data from refs " &
Sheets("DATA").Cells(i, 1)
Else
msg = msg & ", " & Sheets("DATA").Cells(i, 1)
End If
cnt = cnt + 1
End If
End If
Next j
i = i + 1
Wend
If cnt = 0 Then
MsgBox "All rows filled"
Else
MsgBox msg
End If
End Sub

HTW
Kostis