none
dynamic Formula generation in next cell RRS feed

  • Question

  • I am preparing a technical sheet and writing a program for checking its completeness. As of now, all the input fields are manually identified by me and colored as yellow. After every row, I am checking whether the required yellow colored fields in every row are empty or not. In any single row, not more than 10 inputs will be required.

    Lets say I will check completeness of inputs in working field of sheet from BN column onwards.

    If the yellow cell (in a row in a working area) is filled with some input, then the value in the cell (In which i am checking the emptiness of the cell) becomes 1. After 10 such cells (i.e. BN to BW), I am taking sum in the next (i.e. 11th) cell. In 12th cell, I have put the no. of required inputs manually as of now(i.e. Total number of yellowed cells in the working row). comparing 11th and 12th row's values, I am getting either 'N' or 'Y' in 13th (i.e. BZ column's) cell depending upon whether BX22<BY22 or not.

    Now,

    I want to generate the above mentioned logic dynamically through VB. In brief the requirement is - 

    If a particular cell is identified as a required input, a button for dynamic programming should be able to do following things

    1. It will make the selected cell yellow and unlock the cell for editing.

    2. It will formulate all the required conditions explained in above logic automatically (i.e. for all 13 cells I have explained above.)

    Kindly help me as to how should I proceed for this.

    BasicVBLearner

    Wednesday, September 27, 2017 5:51 AM

All replies

  • Hi BasicVB,

    Can you share your demo excel here, it is helpful to us to analyze your issue.

    By the way, your issue is related to Excel for developer, so I move your thread to Excel for Developer forum for professional help.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 28, 2017 6:05 AM
  • Hi BasicVB Learner,

    Is this what you want?


    If so, please refer to below code.

    Private Sub CommandButton1_Click()
    
    If ActiveCell.Interior.Color = vbYellow Then Exit Sub
    
    Dim cel As Range
    
    I = ActiveCell.Row
    
    yellowCellCount = 0
    
    For Each cel In ActiveSheet.Range("A" & I & ":M" & I)
    
    If cel.Interior.Color = vbYellow Then
    
    yellowCellCount = yellowCellCount + 1
    
    End If
    
    Next cel
    
    If yellowCellCount < 10 Then
    
    On Error Resume Next
    
    ActiveSheet.Unprotect
    
    ActiveCell.Locked = False
    
    ActiveCell.Interior.Color = vbYellow
    
    ActiveSheet.Range("N" & I).Offset(0, yellowCellCount).Formula = "=If(isblank(" & ActiveCell.Address & "),0,1)"
    
    ActiveSheet.Protect
    
    End If
    
    End Sub

    If not, please share some screenshots to show your requirement.

    You could share screenshot via One Drive and put link here.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 29, 2017 5:47 AM