Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > check certain cells for data before saving file
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 275
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 751
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 275
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 751
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 275
Default 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 751
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cells widen after saving and reopening file Sigi Rindler Excel Discussion (Misc queries) 2 November 23rd 06 12:03 AM
How do I prevent saving an excel file if cells are blank? Leighann Excel Worksheet Functions 1 November 4th 06 07:40 PM
Locking The Cells with Input At The Time of Saving The File In Calif Excel Discussion (Misc queries) 2 September 10th 05 05:33 PM
Can i recover my excel file after saving with new data? katieneedshelp! Excel Discussion (Misc queries) 1 August 11th 05 03:13 PM
saving dbf file without losing data govworker Excel Discussion (Misc queries) 0 February 7th 05 11:27 PM


All times are GMT +1. The time now is 02:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement