Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax needed to validate cells on spreadsheet
I am trying to write some code to test the following.
If column C contains a value then check that columns n to k also contain a value. This check will apply to a number of rows on the activesheet. It is possible that column c may not contain any values at all but I need a test to ensure that if there are, then columns n to k are not empty. Would a Do Until Not IsEmpty check on column c be more efficient in place of a For Each type statement to avoid checking each row? However, I need to ensure that the code does not get stuck in a continuous loop. In summary the code needs to skip any empty cells in column c but if it finds a value then check columns n to k are populated with a value. If these columns contain a value on the same row as the test in column c then skip else I need a message box warning the user that they've entered a value in column c but not completed their input in columns n to k. Does that make sense? Can anyone kindly help please? Many thanks Jacqui |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax needed to validate cells on spreadsheet
I think I'd use a formula in an adjacent cell (maybe insert a new column D and
have all things shift to the right): =IF(C1="","",IF(COUNTA(L1:O1)=4,"","Please complete L:O!")) Format that cell in big bold red letters. ======== In code: Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Dim wks As Worksheet Dim myRngToCheck As Range Set wks = Worksheets("sheet1") With wks Set myRng = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp)) For Each myCell In myRng.Cells If myCell.Value = "" Then 'do nothing Else Set myRngToCheck = .Cells(myCell.Row, "K").Resize(1, 4) If Application.CountA(myRngToCheck) _ < myRngToCheck.Cells.Count Then MsgBox "Please fix Row: " & myCell.Row End If End If Next myCell End With End Sub Jacqui wrote: I am trying to write some code to test the following. If column C contains a value then check that columns n to k also contain a value. This check will apply to a number of rows on the activesheet. It is possible that column c may not contain any values at all but I need a test to ensure that if there are, then columns n to k are not empty. Would a Do Until Not IsEmpty check on column c be more efficient in place of a For Each type statement to avoid checking each row? However, I need to ensure that the code does not get stuck in a continuous loop. In summary the code needs to skip any empty cells in column c but if it finds a value then check columns n to k are populated with a value. If these columns contain a value on the same row as the test in column c then skip else I need a message box warning the user that they've entered a value in column c but not completed their input in columns n to k. Does that make sense? Can anyone kindly help please? Many thanks Jacqui -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to sum a row: syntax needed | Excel Discussion (Misc queries) | |||
proper syntax in code needed | Excel Programming | |||
Please explain syntax needed | Excel Programming | |||
Multiple Condition syntax needed | Excel Programming | |||
Syntax needed for With | Excel Programming |