How to convert this conditional excel formula to VBA code ??

[Thuvarak]

Hello All,

I've got a perfectly working Macro assigned to a button to copy paste the contents from one sheet (Designed like a form) to another sheet (Master database - Locked and Protected). This workbook will be shared with many people to fetch data from each.

Now, I want my user not to leave some cells empty on certain conditions. I tried coding on macro but there is some error displayed. So here I am pasting the formula which I've made in excel cell and I seek you guys help to code it in VBA coding format.

=IF(C26="A"; IF(AND(C30="";C31="";C32="";C33="";C34="";C35="";C36="");("Enter Volume");(IF(OR(C37="";C38="");("Input Revenue & Gross Margin");("All Good"))));("No need of any entry"))

Also here I am pasting the VBA code assigned to the button; So Please guide me where exactly the above coding needs to be pasted. Because as per the above formula if the result is not "All Good" or "No need of any entry" the sheet should not save.

Sub Submit_New_Entry()
'
' Submit_New_Entry Macro
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="1234"
Range("C8:C38").Select
Selection.Copy
Sheets("Data").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Entry").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll Down:=-6
Range("C9:C27").Select
ActiveWindow.SmallScroll Down:=6
Range("C9:C27,C29:C38").Select
Range("C29").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-18
Range("C9").Select
ActiveWorkbook.Save
Sheet1.Protect Password:="1234"
Application.ScreenUpdating = True

End Sub

Thank you in Advance !!

 

[rlv01]

One example

Sub Submit_New_Entry()
'
' Submit_New_Entry Macro
    Application.ScreenUpdating = False
    Sheet1.Unprotect Password:="1234"
    Range("C8:C38").Select
    Selection.Copy
    Sheets("Data").Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    Sheets("Entry").Select
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=-6
    Range("C9:C27").Select
    ActiveWindow.SmallScroll Down:=6
    Range("C9:C27,C29:C38").Select
    Range("C29").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-18
    Range("C9").Select

[COLOR=#ff0000]    Select Case CheckInputData
    Case "All Good", "No need of any entry"
        ActiveWorkbook.Save
    Case Else
        MsgBox CheckInputData
    End Select[/COLOR]

    Sheet1.Protect Password:="1234"
    Application.ScreenUpdating = True
End Sub

Function CheckInputData() As String
    Dim R As Range, rngInput As Range, rngAnd As Range, rngOR As Range
    Dim MyResult As String, SAnd As String, SOr As String

    With ActiveSheet
        Set rngInput = .Range("C26")
        Set rngAnd = .Range("C30:C36")
        Set rngOR = .Range("C37:C38")
    End With
    
    If rngInput.Value = "A" Then
        For Each R In rngAnd
            SAnd = SAnd & Trim(R.Value)
        Next R

        For Each R In rngOR
            SOr = SOr & Trim(R.Value)
        Next R

        If SAnd = "" Then
            MyResult = "Enter Volume"
        ElseIf SOr = "" Then
            MyResult = "Input Revenue & Gross Margin"
        Else
            MyResult = "All Good"
        End If
    Else
        MyResult = "No need of any entry"
    End If
    CheckInputData = MyResult
End Function