Posts by moore.1671

moore.1671 · Sep 18th 2013

Hi i am wanting to create a macro that will search the cell in the same row but one column over for value. Here's what i go so far.

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oSht As Worksheet
    Dim strSearch As String
    Dim aCell As Range
    Set oSht = Sheets("Statement")
    strSearch = "Prior Month Premium"
    Set aCell = oSht.Cells.Find(What:=strSearch, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If strSearch = "Prior Month Premium" Then
    
  [B]   If InStr(Range(0, 1).Value = "" Then `creating error message
[/B]        MsgBox "Please enter a Prior Month Premium"
            Cancel = True
    End If
    End If
    Exit Sub

Display More

End Sub

moore.1671 · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

One more thing. now that the macro is searching for specific text how do i get it to stop once the cell to the right has a value and is no longer empty?

moore.1671 · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

Thank you soo soo soo much... you are awesome :rock:

moore.1671 · Sep 17th 2013

Re: I want a macro that will not mess up if rows are deleted...

forum.ozgrid.com/index.php?attachment/56405/

moore.1671 · Sep 17th 2013

I am trying to create macro to find specific text "Prior Month Premium" and then check cell to the right to see if left empty and if so then Msgbox the user to enter missing info. Here's what I got so far which is specific to certain cells being populated...

Code

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If (Sheets("Statement").Range("h84").Value = "") Then
        MsgBox "Please enter a Prior Month Premium"
        Cancel = True
    End If
    Dim c As Range
    For Each c In Sheets("Statement").Range("j86")
            If Not IsEmpty(c) And (Sheets("Statement").Range("j86") = "adjustment") Then
                If (Sheets("Statement").Range("c82") = "") Then
                        MsgBox "Please explain the difference between prior and current month premiums before saving." & Chr(13)
                        Cancel = True
                End If
        End If
    Next c
 
End Sub

Display More