Code doesn't update-excel vba 1

[Jaybye]

Good day

I'm new to vba and to this site. I need desperate help please. Below is my code. The code checks if data was captured for the selected month and returns a message if so. The code works perfectly the first time it runs but as from the second time it returns the message that data was already recorded although it wasn't. Could someone check my code please?

Thanks for the help.

Jaybye

Private Sub cmdRecord_Click()
Dim Hosp As String
Dim FindStringAll As String
Dim FindStringSG As String
Dim rng As Range
Dim rngSG As Range
Hosp = Sheets("RECALL").Range("AD2").Value
FindStringAll = Sheets("RECALL").Range("AD35").Value
FindStringSG = Sheets("RECSG").Range("AD35").Value

ActiveWindow.WindowState = xlMaximized
If cboHosp.ListIndex < 0 Then
MsgBox "Select hospital.", vbExclamation, "Invalid Hospital"
cboHosp.SetFocus
Exit Sub
End If

If cboMonth.ListIndex < 0 Then
MsgBox "Select reporting month.", vbExclamation, "Invalid Month"
cboMonth.SetFocus
Exit Sub
End If

Select Case txtDataType.Value 'Sheets("RecSG").ScrollArea = "E5:F34"
Case "ALL DATA"
Sheets("RecAll").Activate
Sheets("RECALL").Range("AD35").Value = ""
Sheets("RecAll").Range("A2").Value = txtHosp.Value
Sheets("RecAll").Range("AD2").Value = txtPrefix.Value
Sheets("RecAll").Range("F35").Value = cboMonth.Value
Sheets("RecAll").Range("G35").Value = txtFY.Value
Sheets("RECALL").Range("AD35").Value = Sheets("RECALL").Range("F35").Value + Sheets("RECALL").Range("G35").Value
Sheets("RecAll").Range("E5:F34,AC5:AC34").ClearContents
Sheets("RecAll").Range("E5").Select
Unload Me

Case "IN PATIENT DATA"
Sheets("RecIP").Activate
Sheets("RECIP").Range("AD35").Value = ""
Sheets("RECIP").Range("AD35").Value = Sheets("RECIP").Range("F35").Value + Sheets("RECIP").Range("G35").Value
Sheets("RecIP").Range("A2").Value = txtHosp.Value
Sheets("RecIP").Range("AD2").Value = txtPrefix.Value
Sheets("RecIP").Range("F35").Value = cboMonth.Value
Sheets("RecIP").Range("G35").Value = txtFY.Value
Sheets("RECIP").Range("AD35").Value = Sheets("RECIP").Range("F35").Value + Sheets("RECIP").Range("G35").Value
Sheets("RecIP").Range("AD35").Value = Sheets("RECIP").Range("F35").Value + Sheets("RECIP").Range("G35").Value
Sheets("RecIP").Range("E5:F34").ClearContents
Sheets("RecIP").Range("E5").Select
Unload Me

Case "OUT PATIENT DATA"
Sheets("RecOP").Activate
Sheets("RECOP").Range("AD35").Value = ""
Sheets("RECOP").Range("AD35").Value = Sheets("RECOP").Range("F35").Value + Sheets("RECOP").Range("G35").Value
Sheets("RecOP").Range("A2").Value = txtHosp.Value
Sheets("RecOP").Range("AD2").Value = txtPrefix.Value
Sheets("RecOP").Range("F35").Value = cboMonth.Value
Sheets("RecOP").Range("G35").Value = txtFY.Value
Sheets("RECOP").Range("AD35").Value = Sheets("RECOP").Range("F35").Value + Sheets("RECOP").Range("G35").Value
Sheets("RecOP").Range("AD35").Value = Sheets("RECOP").Range("F35").Value + Sheets("RECOP").Range("G35").Value
Sheets("RecOP").Range("E5:F34").ClearContents
Sheets("RecOP").Range("E5").Select
Unload Me

Case "SERVICE GROUP DATA"
Sheets("RecSG").Activate
Sheets("RECSG").Range("AD35").Value = ""
Sheets("RecSG").Range("A2").Value = txtHosp.Value
Sheets("RecSG").Range("AD2").Value = txtPrefix.Value
Sheets("RecSG").Range("F35").Value = cboMonth.Value
Sheets("RecSG").Range("G35").Value = txtFY.Value
Sheets("RecSG").Range("AD35").Value = Sheets("RECSG").Range("F35").Value + Sheets("RECSG").Range("G35").Value
Sheets("RecSG").Range("E5:F34").ClearContents
Sheets("RecSG").Range("E5").Select
Unload Me
End Select

Select Case Hosp
Case "ERH"
With Sheets("ERH").Range("AD:AD")
FindStringAll = Sheets("RECALL").Range("AD35").Value
Set rng = .Find(What:=FindStringAll, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
MsgBox "Data for selected month is already recorded.", vbInformation + vbOKOnly, "Data already recorded"
frmMain.Show
End If
End With

Case "HHH"
With Sheets("HHH").Range("AD:AD")
FindStringAll = Sheets("RECALL").Range("AD35").Value
Set rng = .Find(What:=FindStringAll, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
MsgBox "Data for selected month is already recorded.", vbInformation + vbOKOnly, "Data already recorded"
frmMain.Show
End If
End With

Case "LGH"
With Sheets("LGH").Range("AD:AD")
FindStringAll = Sheets("RECALL").Range("AD35").Value
Set rng = .Find(What:=FindStringAll, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
MsgBox "Data for selected month is already recorded.", vbInformation + vbOKOnly, "Data already recorded"
frmMain.Show
End If
End With
End Select

Select Case Hosp
Case "OPC"
With Sheets("OPC").Range("AD:AD")
FindStringSG = Sheets("RECSG").Range("AD35").Value
Set rngSG = .Find(What:=FindStringSG, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngSG Is Nothing Then
MsgBox "Data for selected month is already recorded.", vbInformation + vbOKOnly, "Data already recorded"
frmMain.Show
End If
End With

Case "WCR"
With Sheets("WCR").Range("AD:AD")
FindStringSG = Sheets("RECSG").Range("AD35").Value
Set rngSG = .Find(What:=FindStringSG, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngSG Is Nothing Then
MsgBox "Data for selected month is already recorded.", vbInformation + vbOKOnly, "Data already recorded"
frmMain.Show
End If
End With
End Select
End Sub

 

[SkipVought]

hi,

Your Select Case and range test statements may possible distil to...

    Select Case Hosp
        Case "ERH", "HHH", "LGH"
            FindStringAll = Sheets("RECALL").Range("AD35").Value
        Case "OPC", "WCR"
            FindStringAll = Sheets("RECSG").Range("AD35").Value
    End Select
    
    With Sheets(Hosp).Range("AD:AD")
        Set rng = .Find(What:=FindStringAll, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
    End With
    
    If Not rng Is Nothing Then
        MsgBox "Data for selected month is already recorded.", _
            vbInformation + vbOKOnly, _
            "Data already recorded"
        frmMain.Show
    End If

If you don't get a message then the lookup value was found in the lookup range. Put a BREAK in your code and step thru this portion of the code to discover what's happening. Use the Watch Window to observe the values in your variables.

faq707-4594

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

in fact, here are all your select case statements

    Dim sName As String
    
    Select Case txtDataType.Value 'Sheets("RecSG").ScrollArea = "E5:F34"
        Case "ALL DATA": sName = "RECALL"
        Case "IN PATIENT DATA": sName = "RECIP"
        Case "OUT PATIENT DATA": sName = "RECOP"
        Case "SERVICE GROUP DATA": sName = "RECSG"
    End Select
    
    With Sheets(sName)
        .Range("AD35").Value = ""
        .Range("A2").Value = txtHosp.Value
        .Range("AD2").Value = txtPrefix.Value
        .Range("F35").Value = cboMonth.Value
        .Range("G35").Value = txtFY.Value
        .Range("AD35").Value = .Range("F35").Value + .Range("G35").Value
        .Range("E5:F34,AC5:AC34").ClearContents
    End With
    
    Unload Me
    
    Select Case Hosp
        Case "ERH", "HHH", "LGH"
            FindStringAll = Sheets("RECALL").Range("AD35").Value
        Case "OPC", "WCR"
            FindStringAll = Sheets("RECSG").Range("AD35").Value
    End Select
    
    With Sheets(Hosp).Range("AD:AD")
        Set rng = .Find(What:=FindStringAll, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
    End With
    
    If Not rng Is Nothing Then
        MsgBox "Data for selected month is already recorded.", _
            vbInformation + vbOKOnly, _
            "Data already recorded"
        frmMain.Show
    End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jaybye]

Hi Skip

Thank you so much. It's perfect! I didn't expect an answer so promptly.
I knew my code was quite long but I wanted to make sure the correct sheets gets activated. I really learned much with your code.

Thank you once again.

John

(I have more questions to ask pertaining charts but will post another thread with the details)

 

[SkipVought]

Any time!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}