Function Help
 

I'm not that good with VBA, so I was wondering if someone could help write a
function. Essentially I have a spreadsheet where the columns represent
various forms, tests, etc. that we track for new hires. Unfortunately, there
is no easy way to see all of the missing documentation. My thought is to
write a function that takes in a range of rows (e.g., b50:f50) , checks to
see if there is a value in the first cell, if there is, move to the next
cell, if there isn't write "Missing" and the column header (e.g., b1) and
then move to next cell in the range, perform the same check and concatenate
the return string. This would make the output a text string that would read
something like: "Missing B, Missing D, Missing E". Can anyone recommend an
approach I could try? Thanks in advance for your assistance!

Function Help
 

Sub CheckDate()
Dim rng as Range, cell as Range
Dim sStr as String
set rng = Range(cells(activecell.row,"B"),cells(activecell.r ow,"F"))
for each cell in rng
if isempty(cell) then
sStr = cells(1,cell.Column) & ", "
end if
Next
sStr = left(sStr,len(sStr)-2)
msgbox sStr
end sub

--
Regards,
Tom Ogilvy



"Matt Gehrmann" wrote in message
...
I'm not that good with VBA, so I was wondering if someone could help write
a
function. Essentially I have a spreadsheet where the columns represent
various forms, tests, etc. that we track for new hires. Unfortunately,
there
is no easy way to see all of the missing documentation. My thought is to
write a function that takes in a range of rows (e.g., b50:f50) , checks to
see if there is a value in the first cell, if there is, move to the next
cell, if there isn't write "Missing" and the column header (e.g., b1) and
then move to next cell in the range, perform the same check and
concatenate
the return string. This would make the output a text string that would
read
something like: "Missing B, Missing D, Missing E". Can anyone recommend
an
approach I could try? Thanks in advance for your assistance!

Function Help
 

Tom,

This is great, thanks for the help. Unfortunately, I have another stupid
question. Since I have many rows of data that I need to check, how can I get
the output to be in the last column? For example, assuming we were somewhere
in row 50 of the spreadsheet, is there a way to write this as a function that
would be more like =CheckDate(b50:f50) which would return Missing B, Missing
C? This way I could copy it down to examine all of the rows of data I have.
If this is not possible, could you recommend something that might work?
Thanks again, I appreciate it.

Matt

"Tom Ogilvy" wrote:

Sub CheckDate()
Dim rng as Range, cell as Range
Dim sStr as String
set rng = Range(cells(activecell.row,"B"),cells(activecell.r ow,"F"))
for each cell in rng
if isempty(cell) then
sStr = cells(1,cell.Column) & ", "
end if
Next
sStr = left(sStr,len(sStr)-2)
msgbox sStr
end sub

--
Regards,
Tom Ogilvy



"Matt Gehrmann" wrote in message
...
I'm not that good with VBA, so I was wondering if someone could help write
a
function. Essentially I have a spreadsheet where the columns represent
various forms, tests, etc. that we track for new hires. Unfortunately,
there
is no easy way to see all of the missing documentation. My thought is to
write a function that takes in a range of rows (e.g., b50:f50) , checks to
see if there is a value in the first cell, if there is, move to the next
cell, if there isn't write "Missing" and the column header (e.g., b1) and
then move to next cell in the range, perform the same check and
concatenate
the return string. This would make the output a text string that would
read
something like: "Missing B, Missing D, Missing E". Can anyone recommend
an
approach I could try? Thanks in advance for your assistance!

Function Help
 

Hi Matt

Adapted from Tom's macro:

Function CheckDate(ChkRange As Range) As String
Dim cell As Range
For Each cell In ChkRange
If IsEmpty(cell) Then
CheckDate = CheckDate & "Missing " & _
Mid(cell.Address, 2, InStr(2, cell.Address, "$") - 2) _
& ", "
End If
Next
If CheckDate < "" Then
CheckDate = Left(CheckDate, Len(CheckDate) - 2)
Else
CheckDate = "Everything OK"
End If
End Function

Paste this into a standard module and then you will be able to use
=CheckDate(B50:F50) as a worksheet function.

Hope this helps
Rowan

Matt Gehrmann wrote:
Tom,

This is great, thanks for the help. Unfortunately, I have another stupid
question. Since I have many rows of data that I need to check, how can I get
the output to be in the last column? For example, assuming we were somewhere
in row 50 of the spreadsheet, is there a way to write this as a function that
would be more like =CheckDate(b50:f50) which would return Missing B, Missing
C? This way I could copy it down to examine all of the rows of data I have.
If this is not possible, could you recommend something that might work?
Thanks again, I appreciate it.

Matt

"Tom Ogilvy" wrote:


Sub CheckDate()
Dim rng as Range, cell as Range
Dim sStr as String
set rng = Range(cells(activecell.row,"B"),cells(activecell.r ow,"F"))
for each cell in rng
if isempty(cell) then
sStr = cells(1,cell.Column) & ", "
end if
Next
sStr = left(sStr,len(sStr)-2)
msgbox sStr
end sub

--
Regards,
Tom Ogilvy



"Matt Gehrmann" wrote in message
...

I'm not that good with VBA, so I was wondering if someone could help write

a

function. Essentially I have a spreadsheet where the columns represent
various forms, tests, etc. that we track for new hires. Unfortunately,

there

is no easy way to see all of the missing documentation. My thought is to
write a function that takes in a range of rows (e.g., b50:f50) , checks to
see if there is a value in the first cell, if there is, move to the next
cell, if there isn't write "Missing" and the column header (e.g., b1) and
then move to next cell in the range, perform the same check and

concatenate

the return string. This would make the output a text string that would

read

something like: "Missing B, Missing D, Missing E". Can anyone recommend

an

approach I could try? Thanks in advance for your assistance!