PDA

View Full Version : check cells content before executes macro



alienscript
07-29-2007, 10:21 AM
Hi VB gurus,

In my sample worksheet I have done long formulas and packaged the task in a macro. My problem is I dont know how to code in order to check for specific range of cells for the content before my macro is allowed to take off. I like the macro to firstly check that if the following cells text in row 1 are not matched, use MsgBox to display the wrong versus correct cells string text, then vbOK to exit macro. Also, if Range("B2:G2","J2") are completely blank, use MsgBox to display "missing data", then vbOK to exit macro.

cell B =PurR
cell C =STL
cell D =CS
cell E =PO
cell F =SO
cell G =IB
cell L =Unit Cost

If these cell texts (Upper or Lower case) are in their specific cells, macro will proceed.

If either one or some of these cell texts are not matched, display MsgBox of the wrong and correct cell texts. Then stop macro.

If Range("B2:G2","J2") are completely blank, MsgBox to display "missing data", Then stop macro.

This is the missing piece of code i need to complete the whole work task. Appreciate very much if VB experts can help me with this last piece of code. Thousands Thanks in advance :)

Bob Phillips
07-29-2007, 10:57 AM
Hi VB gurus,

In my sample worksheet I have done long formulas and packaged the task in a macro. My problem is I dont know how to code in order to check for specific range of cells for the content before my macro is allowed to take off. I like the macro to firstly check that if the following cells text in row 1 are not matched, use MsgBox to display the wrong versus correct cells string text, then vbOK to exit macro. Also, if Range("B2:G2","J2") are completely blank, use MsgBox to display "missing data", then vbOK to exit macro.

cell B =PurR
cell C =STL
cell D =CS
cell E =PO
cell F =SO
cell G =IB
cell L =Unit Cost

If these cell texts (Upper or Lower case) are in their specific cells, macro will proceed.

If either one or some of these cell texts are not matched, display MsgBox of the wrong and correct cell texts. Then stop macro.

If Range("B2:G2","J2") are completely blank, MsgBox to display "missing data", Then stop macro.

This is the missing piece of code i need to complete the whole work task. Appreciate very much if VB experts can help me with this last piece of code. Thousands Thanks in advance :)



Dim aryVals
Dim i As Long
aryVals = Array("", "", "PurR", "STL", "CS", "PO", "SO", "IB")
For i = 2 To 7
If Cells(1, i).Value <> aryVals(i) Then
MsgBox "Cell " & Cells(1, i).Address(0, 0) & " should be " & aryVals(i)
Exit Sub
End If
Next i
If Range("L1").Value <> "Unit Costs" Then
MsgBox "Cell L1 should be UYnit Costs"
Exit Sub
End If
If Application.Count(Range("B2:G2")) <> 6 Or _
Range("J2") = "" Then
MsgBox "Missing Data"
Exit Sub
End If

alienscript
07-29-2007, 12:00 PM
Thank you so much for your help. Really really appreciate that! There is only one problem. The check for row 1 cells text is case-sensitive. The users could have input correctly except not in proper case. Can you disable that in your code so that it will accept either Upper, Lower or Proper case?

Thanks in advance :thumb

Bob Phillips
07-29-2007, 01:39 PM
This should do it



Dim aryVals
Dim i As Long
aryVals = Array("", "", "PurR", "STL", "CS", "PO", "SO", "IB")
For i = 2 To 7
If LCase(Cells(1, i).Value) <> LCase(aryVals(i)) Then
MsgBox "Cell " & Cells(1, i).Address(0, 0) & " should be " & aryVals(i)
Exit Sub
End If
Next i
If LCase(Range("L1").Value) <> LCase("Unit Costs") Then
MsgBox "Cell L1 should be UYnit Costs"
Exit Sub
End If
If Application.Count(Range("B2:G2")) <> 6 Or _
Range("J2") = "" Then
MsgBox "Missing Data"
Exit Sub
End If

alienscript
07-29-2007, 01:47 PM
Thank you so much!!! I stayed up until 4.45 AM just for this answer even though I got to wake up at 8.00 AM to work. I'm glad you responsed. Thanks.

rbrhodes
07-29-2007, 02:30 PM
Looks as though OP has a solution but may need a Xpost remainder:

http://www.ozgrid.com/forum/showthread.php?t=74030