ExcelBanter - check certain cells for data before saving file

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - check certain cells for data before saving file (https://www.excelbanter.com/excel-worksheet-functions/193458-check-certain-cells-data-before-saving-file.html)

check certain cells for data before saving file

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:P). 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

check certain cells for data before saving file

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
On Jul 2, 5:55 pm, Anthony wrote:
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:P). 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

check certain cells for data before saving file

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" wrote:

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
On Jul 2, 5:55 pm, Anthony wrote:
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:P). 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

check certain cells for data before saving file

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

On Jul 2, 8:13 pm, Anthony wrote:
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" wrote:
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
On Jul 2, 5:55 pm, Anthony wrote:
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:P). 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

check certain cells for data before saving file

,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" wrote:

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

On Jul 2, 8:13 pm, Anthony wrote:
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" wrote:
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
On Jul 2, 5:55 pm, Anthony wrote:
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:P). 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

check certain cells for data before saving file

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

On Jul 2, 9:02 pm, Anthony wrote:
,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" wrote:
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

On Jul 2, 8:13 pm, Anthony wrote:
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" wrote:
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
On Jul 2, 5:55 pm, Anthony wrote:
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:P). 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