[HOW] Format a variable?

[Guest]

Hey, I`ve got a little problem, it`s not that hard but I want to put my finger on it.

This is a piece of my macro:

Sub workbook_beforeclose(cancel As Boolean)
Dim lc As Long, scan As Boolean
'scanning for empty cells in row 96
scan = True
For lc = 1 To 21
If Len(Cells(96, lc)) = 0 Then
scan = False
Exit For
End If
Next
If scan = False Then
MsgBox "value in colomn " & CStr(lc) & " is missing. Sheet will not be closed" _
, vbCritical, "uncomplete!"
cancel = True
Exit Sub
End If
If scan = True Then
scanpast
Else
End If
End Sub

As you can see, what is does is scan for empty cells in row 96. If it all cells a filled it will continue to the next routine (which is not shown here) if one of the cell in 96 is empty, a message will appear telling you which cell exactly is empty.


Now: what I want to do is format the variable LC so that the message not be:


"value in colomn 3 is missing. Sheet will not be closed"

but:

"value for Temperature is missing. Sheet will not be closed"

Can anyone help me with this!?

Thanks a lot!

 

[Juan Pablo González]

Do you keep the column title in the first row of each column ? If so, then, instead of using just LC, use Cells(1,LC), that should give you the Column Name.

Another, thing, I HATE loops ! those are just too slow ! try with this formula instead:

scan = Not Evaluate("OR(LEN(A96:U96)=0)")

In one line you know if any cell in A96:U96 is empty. Now, to know which column is empty, try this:

LC = Evaluate("MATCH("""",A96:U96,0)")

Or in VBA language:
LC = Application.Match("",Range("A96:U96"),0)

How's that ?

 

[Juan Pablo González]

Just thought for a better formula for LC

LC = Evaluate("MATCH(0,LEN(A96:U96),0)")

This can't be obtanied directly in VBA because it's an array formula, which would force you to put the result in a cell first, and then, read the content of that cell.