|
Guest
Posts: n/a
|
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 <Anth...@discussions.microsoft.com> 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 <Anth...@discussions.microsoft.com> 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 <Anth...@discussions.microsoft.com> 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
|
|
Similar Threads
