VBA warning if no input

[garypea123]

Good Morning,

A little more a complicated one for you experts.

I have created a easier mock version of my sheet for simplicity but what I am looking for is.

When the macro button is pressed I would like it look at column C and Column E.

If there is an input in Column C then it needs to be mandatory that there is a reason given (before the macro can run).

An alert should then be displayed to advise it cannot run the macro until the field is completed.

I am sure this can be done, but I am getting into the realms of something which I have never done before.

 

[aRandomHelper]

Probably need a loop of some sort to achieve that. Are you already running some loop in your current code? Can you show what you have in your code so far

 

[garypea123]

Probably need a loop of some sort to achieve that. Are you already running some loop in your current code? Can you show what you have in your code so fa

There is no loop in the VBA - and in fairness this macro is very basic where it it just rewriting data based on the on variance.

But where I am having issues is I keep forgetting to include a reason when I apply a variance and this then causes an issue further down the line and potentially I have to start again because I did not provide a reason.



SUB A_Rewrite

Application.ScreenUpdating = False

Dim varResponse As Variant
varResponse = MsgBox("Are you sure you want to re-write with carry over ammendment? 'Yes' or 'No'", vbYesNo, "Selection")
If varResponse <> vbYes Then Exit Sub


Sheets("Carry Over Sheet").Select
Columns("E:AI").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select

Sheets("Carry Over Sheet").Select
Range("M8:M200").Select
Selection.Copy

Sheets("Consolidated View").Select
Range("V8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Carry Over Sheet").Select
Range("S8:S200").Select
Selection.Copy

Sheets("Consolidated View").Select
Range("X8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Carry Over Sheet").Select
Range("Y8:Y200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("Z8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Carry Over Sheet").Select
Range("AE8:AE200").Select
Selection.Copy
Sheets("Consolidated View").Select

Range("AB8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Sheets("Carry Over Sheet").Select
Range("AK8:AK200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("AD8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Consolidated View").Select
Range("W8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",V8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"

Range("Y8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",X8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"

Range("AA8").Select
ActiveCell.Formula = _

"=IF(M8="""","""",Z8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"

Range("AC8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",AB8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"

Range("AE8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",AD8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"


Range("W8:W" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("Y8:Y" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("AA8:AA" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("AC8:AC" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("AE8:AE" & Range("K" & Rows.Count).End(xlUp).Row).FillDown


Application.Goto Range("A1"), True









End Sub

 

[aRandomHelper]

Ok, so it seems it would be best to keep it a separate check before executing your current code. Try this:

Sub A_Rewrite()

    Application.ScreenUpdating = False
   
    Dim varResponse As Variant
    varResponse = MsgBox("Are you sure you want to re-write with carry over ammendment? 'Yes' or 'No'", vbYesNo, "Selection")
    If varResponse <> vbYes Then Exit Sub
   
    Dim c As Range
    For Each c In Range("C3:C" & Cells(Rows.Count, "B").End(xlUp).Row)  ' First cell at C3. Change accordingly if it's on different row
        If c.Value <> "" Then
            If c.Offset(, 2).Value = "" Then
                If MsgBox("Missing reason at cell [ " & c.Offset(, 2).Address(0, 0) & " ]" & vbCrLf & vbCrLf & "Click OK to go to cell.", vbOKCancel) = vbOK Then
                    c.Offset(, 2).Select
                End If
                Application.ScreenUpdating = True
                Exit Sub
            End If
        End If
    Next
   
    '...
    '...
    'All your remaining codes here
    '...
    '...
   

End Sub

 

[mohadin]

Hi
Try to allocate this code to the Macro Button

Sub test()
    For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
        If Cells(i, 3) = "" Or Cells(i, 5) = "" Then
            MsgBox ("cannot run the macro until the field is completed?")
            Exit Sub
        End If
    Next
    Call A_Rewrite
End Sub

 

[garypea123]

Thank you, Both codes seem to work, but I do like the code from ArandomHelper as it pin points which cell needs data (which is a bonus to my request).

Additionally I do understand the language of both scripts which is great.

I have tested on basic sheet, but I need to add this tomorrow into my main projection, so hopefully I do not get any error messages

Thanks guys