#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    59
    Rep Power
    12

    Run time error:'1004': Method 'Range' of object '_Global' failed


    Hi, I have a form with a button, when I click on the button, it will display data in an excel page (the form didn't close). However, when I close the excel file, and reclick the button again, it has runtime error message like this: Method 'Range' of object '_Global' failed." ... why is that? Did I missed out something important? My code as follow:-

    ======================================

    Public Sub getExcelByBank()

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim getReport As String

    Dim excelFileName As String
    Dim xlsApp As Excel.Application
    Dim newWorkBook As Excel.Workbook
    Dim newWorkSheet As Excel.Worksheet

    Set cn = New ADODB.Connection

    Set xlsApp = CreateObject("Excel.application")

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & App.Path & "\account_db.mdb"

    Set xlsApp = New Excel.Application

    If xlsApp Is Nothing Then
    MsgBox "Cannot Open Excel Application"
    Exit Sub
    End If

    excelFileName = App.Path & "\Report_Summary_ByBank.xls"

    'Delete summary report for updated summary report

    If Dir(excelFileName) <> "" Then
    Kill (excelFileName)
    End If

    Set newWorkSheet = xlsApp.Workbooks.Add.Worksheets.Add

    With newWorkSheet

    .Range(.Cells(1, 1), .Cells(3, 8)).Select
    xlsApp.Selection.Columns.AutoFit

    .Range(.Cells(1, 1), .Cells(3, 8)).RowHeight = 20
    .Rows(1).Font.Bold = True
    .Rows(2).Font.Bold = True
    .Rows(3).Font.Bold = True
    .Cells(1, 4).Value = "Andy Tey"
    .Cells(2, 4).Value = "Financial Report Summary"
    .Cells(3, 4).Value = "****************************************************************"
    .Rows(1).HorizontalAlignment = xlHAlignCenter
    .Rows(2).HorizontalAlignment = xlHAlignCenter
    .Rows(3).HorizontalAlignment = xlHAlignCenter

    Dim p As Integer
    Dim q As Integer
    Dim found As Integer
    Dim uniqueBank As Integer

    Dim getTT As String

    Dim strRow As Integer
    Dim RowNumber As Integer

    RowNumber = 5
    strRow = 4

    getReport = "SELECT DISTINCT BANK_NAME FROM BANK_ACCOUNT"
    Set rs = cn.Execute(getReport)

    Do While Not rs.EOF

    Dim rs2 As New ADODB.Recordset
    Dim getReport2 As String

    getReport2 = "SELECT * FROM REPORT WHERE BANK_NAME='" & rs.Fields("bank_name") & "' ORDER BY BANK_NAME"
    Set rs2 = cn.Execute(getReport2)


    If (strRow > 4) Then

    .Cells(strRow - 1, 2).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
    .Cells(strRow - 1, 1).Value = "Bank Name"
    .Cells(strRow - 1, 3).Value = "Bank Type"
    .Cells(strRow - 1, 4).Value = "Amount $"
    .Cells(strRow - 1, 5).Value = "Deposit / Withdrawal"
    .Cells(strRow - 1, 6).Value = "Category"
    .Cells(strRow - 1, 7).Value = "Comments"
    .Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 7)).Interior.Color = RGB(180, 180, 180)

    'Header settings

    .Rows(strRow - 1).HorizontalAlignment = xlHAlignCenter ' set header in center
    .Rows(strRow - 1).Font.Bold = True ' set first row - header as font bold
    .Rows(strRow - 1).Font.ColorIndex = 5 ' set header words in blue
    .Range(.Cells(strRow - 1, 1), .Cells(strRow - 1, 1)).RowHeight = 30


    Do While Not rs2.EOF

    'set the string equal to the row number to start on
    strRow = "" & RowNumber & ""
    Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")
    Range("A" & strRow) = rs2.Fields("bank_name")
    Range("C" & strRow) = rs2.Fields("account_type")
    Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
    Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
    Range("F" & strRow) = rs2.Fields("category")
    Range("G" & strRow) = rs2.Fields("description")

    'auto fit columns
    .Range(.Cells(4, 1), .Cells(strRow, 8)).Select
    xlsApp.Selection.Columns.AutoFit

    rs2.MoveNext
    RowNumber = RowNumber + 1
    Loop

    'set 2 blank rows between new banks
    RowNumber = RowNumber + 3
    strRow = RowNumber

    Else

    .Cells(strRow, 2).Value = "Date / Time" ' cells (2, 1) means row 2 and column 1
    .Cells(strRow, 1).Value = "Bank Name"
    .Cells(strRow, 3).Value = "Bank Type"
    .Cells(strRow, 4).Value = "Amount $"
    .Cells(strRow, 5).Value = "Deposit / Withdrawal"
    .Cells(strRow, 6).Value = "Category"
    .Cells(strRow, 7).Value = "Comments"
    .Range(.Cells(strRow, 1), .Cells(strRow, 7)).Interior.Color = RGB(180, 180, 180)


    'Header settings

    .Rows(strRow).HorizontalAlignment = xlHAlignCenter ' set header in center
    .Rows(strRow).Font.Bold = True ' set first row - header as font bold
    .Rows(strRow).Font.ColorIndex = 5 ' set header words in blue
    .Range(.Cells(strRow, 1), .Cells(strRow, 1)).RowHeight = 30

    Do While Not rs2.EOF

    'set the string equal to the row number to start on
    strRow = "" & RowNumber & ""
    Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")
    Range("A" & strRow) = rs2.Fields("bank_name")
    Range("C" & strRow) = rs2.Fields("account_type")
    Range("D" & strRow) = Format(rs2.Fields("amount"), "currency")
    Range("E" & strRow) = rs2.Fields("deposit_withdrawal")
    Range("F" & strRow) = rs2.Fields("category")
    Range("G" & strRow) = rs2.Fields("description")

    'auto fit columns
    .Range(.Cells(4, 1), .Cells(strRow, 8)).Select
    xlsApp.Selection.Columns.AutoFit

    rs2.MoveNext
    RowNumber = RowNumber + 1
    Loop

    'set 2 blank rows between new banks
    RowNumber = RowNumber + 3
    strRow = RowNumber

    End If

    rs.MoveNext
    Loop

    rs2.Close
    Set rs2 = Nothing

    rs.Close
    Set rs = Nothing

    End With

    newWorkSheet.SaveAs App.Path & "\Report_Summary_ByBank.xls"

    xlsApp.Visible = True 'Show the Excel file - Report_Summary.xls

    'after the excel closed, close the excel application

    If xlsApp.Visible = False Then
    xlsApp.ActiveWindow.Close
    xlsApp.Quit
    End If

    'xlsApp.Application.ActiveWindow.Close
    Set newWorkSheet = Nothing
    Set xlsApp = Nothing

    cn.Close
    Set cn = Nothing

    End Sub
  2. #2
  3. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    726
    Hey HB - Long time no code. Listen... What line are getting the error on? Is is the first reference to xl.range?
    Fisherman

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    59
    Rep Power
    12
    Hey FM ... thanks for replying, really appreciate that The error is in the Do While loop ... error line is " Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss") " and so on for the whole ranges in the Do While loop.

    Please note, the error only occur when I close the excel file and try to click the button to display it again. Meaning, it works at the first time. Not second or etc times. I have to closed the whole application to get it work again

    =======================

    Do While Not rs2.EOF

    'set the string equal to the row number to start on
    strRow = "" & RowNumber & ""
    ---->>>> Range("B" & strRow) = Format(rs2.Fields("date"), "mm/dd/yyyy hh:mm:ss")

    ......

    Loop
  6. #4
  7. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    726
    OK... well, this time you've stumped me. The only thing I can tell you is to put a break point on the loop and watch what it's doing. Track your recordset and your counter variables. Also try doing a Cntrl + Alt+ Del and looking in the running processes to see if Excel.exe is already running. It may not be closing properly after the first run. Have you tried clicking the button again WHILE the first run's excel is still displayed? If so, what does it do?
    Fisherman

    "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    2
    Rep Power
    0

    Question Same problem for me


    Hi , did you find a solution to this problem.

    I have the exact same issue as below

    1> My application opens and xcel file runs some code and closes the file.
    2> Application then reopens the same file but for some reason throws the error at line of code:

    dtePTD = Range("T_PTD").Value
    3> This problem happens only during the second time the file is openend.. The code works fine during the first run.. And if i close the application and run again..

    Below is complete code snippet that runs twice, works fine the first time but throws error on subsequent run. :

    Set objExcel = New Excel.Application
    objExcel.EnableEvents = False
    objExcel.Workbooks.open iPath

    strStep = "Pull information from workbook"
    With objExcel.ActiveWorkbook.Sheets("Test")
    dtePTD = Range("T_PTD").Value
    strPass = Mid(.Range("C2").Value, 6, 1)
    mstrPass = strPass
    End With

    objExcel.ActiveWorkbook.Close False
    objExcel.Quit
    Set objExcel = Nothing
    Exit Function
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    2
    Rep Power
    0
    I found the following solution worked... getobject first to see if applicaiton is already open...

    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    On Error GoTo 0
    If objExcel Is Nothing Then
    Set objExcel = New Excel.Application
    objExcel.EnableEvents = False
    End If
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Long Island, NY
    Posts
    3
    Rep Power
    0

    Same error when calling a file with another open


    My case is as follows: I have a worksheet designed as a form. Users enter data and then click on an e-mail button.
    This click does the following:
    1) Opens up a numbering log file, so that a unique number is assigned to the users request.
    2) Saves and closes the log file
    3) Comes back to the open worksheet (the form) and plugs the Job Number (the unique number from the log)
    4) Saves the file as "C:\xxxxx.xls"
    5) Assembles the e-mail in Outlook.
    6) Attaches the file
    7) send the e-mail.

    The problem is in p[oint 1 above... Some users (not all) crash the application at line
    Workbooks.Open("L:\Intranet\Mfg.Ops\LogNum.xls")
    with the run-time error Method Open of Object Workbooks failed.

    Any suggestions anyone? (I am brand new in this forum, so hello to y'all)
    Chuck
  14. #8
  15. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Do the failing users all have the same drive mapped as "L:\"? Even if they have some drive mapped to L:\ your \\documentation drive might by L:\ but theirs might be Q:\ and they have \\applications mapped to L:\ instead.

    To avoid this I always use the \\proper\network\path directly and never use drive letters for network drives.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Long Island, NY
    Posts
    3
    Rep Power
    0

    Mapping is correct


    Hi Medialint,

    Mapping is correct all around... L: happens to be the designator of our Corporate Directory and all applications are referencing it.

    By the way, I omitted to say that the app is an Excel VBA macro. Does binding have anything to do with it?

    Chuck
  18. #10
  19. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Other users may have the file open. Sharing an Excel workbook is asking for trouble. Ideally you'd be using a database.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Long Island, NY
    Posts
    3
    Rep Power
    0
    Originally Posted by medialint
    Other users may have the file open. Sharing an Excel workbook is asking for trouble. Ideally you'd be using a database.
    True, but we are deep into Excel...
  22. #12
  23. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    That's too bad the process you describe is almost guaranteed to fail periodically. It would be very easy to use an access database instead to generate your unique IDs the proper way and still do the rest on a LOCAL form (not shared).
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  24. #13
  25. Not much of a contributor
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Aug 2006
    Location
    Hidden
    Posts
    1,012
    Rep Power
    1092
    Originally Posted by ChuckDrago
    True, but we are deep into Excel...
    Yeah, you are in deep trouble.

    Comments on this post

    • medialint agrees : Indeed. Bluntly stated but true.
  26. #14
  27. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    If the only reason for a share is to track unique numbers this is insane

    Other options

    use a GUID

    Or

    Keep your counter or whatever in a simple text file. Put in error handling to retry opening that file ever 500ms or so for several seconds then fail. You'll need to open the file as binary AND KEEP IT OPEN until you're done. You can't reliably do INPUT then reopen for OUTPUT because someone else may get an INPUT in between and then your scheme of tracking IDs goes to hell because you'll get a duplicate. If you need to track the action (which is a good idea) you can open a log file for APPEND and write to that your info. Make sure you open the file as late as possible and close as soon as possible when done.

    Or get a database going :-)
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    1
    Rep Power
    0

    un time error:'1004': Method 'Range' of object '_Global' failed


    Dear Forum members,
    I got the same error message. I have one wksheet with an object which by clicking, it should open the user form "mlabrpt".
    Data entered into this form is sent to another wksheet. Combo options for the form are stored in a third wksheet. The message appears after clicking the object in the first wksheet, so the form fails to open. When debugging, the line in the module showing the statement "mlabrpt.Show" is highlighted.

    Sub ShowmlabrptForm()
    mlabrpt.Show
    End Sub

    What is wrong?. Please, see below the code for the form and I will appreciate your advice.

    Private Sub cmdSave_Click()
    Dim lRow As Long
    'Dim lPart As Long
    Dim ws As Worksheet
    Set ws = Worksheets("data")

    'find first empty row in database
    lRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    'check for epid number
    If Trim(Me.epid.Value) = "" Then
    Me.epid.SetFocus
    MsgBox "Please enter an EPI ID number"
    Exit Sub
    End If

    'copy the data to the database
    With ws
    .Cells(lRow, 1).Value = Me.epid.Value
    .Cells(lRow, 2).Value = Me.labid.Value
    .Cells(lRow, 3).Value = Me.pname.Value
    .Cells(lRow, 4).Value = Me.pers_ID.Value
    .Cells(lRow, 5).Value = Me.dob.Value
    .Cells(lRow, 6).Value = Me.age.Value
    .Cells(lRow, 7).Value = Me.agein.Value
    .Cells(lRow, 8).Value = Me.sex.Value
    .Cells(lRow, 9).Value = Me.phone.Value
    .Cells(lRow, 10).Value = Me.fam_members.Value
    .Cells(lRow, 11).Value = Me.prov.Value
    .Cells(lRow, 12).Value = Me.soum_dist.Value
    .Cells(lRow, 13).Value = Me.bagh_khoro.Value
    .Cells(lRow, 14).Value = Me.home_address.Value
    .Cells(lRow, 15).Value = Me.office_address.Value
    .Cells(lRow, 16).Value = Me.dhosp_visit.Value
    .Cells(lRow, 17).Value = Me.hosp_name.Value
    .Cells(lRow, 18).Value = Me.donset.Value
    .Cells(lRow, 19).Value = Me.fever.Value
    .Cells(lRow, 20).Value = Me.mp_rash.Value
    .Cells(lRow, 21).Value = Me.cough.Value
    .Cells(lRow, 22).Value = Me.coryza.Value
    .Cells(lRow, 23).Value = Me.conjunct.Value
    .Cells(lRow, 24).Value = Me.v_rash.Value
    .Cells(lRow, 25).Value = Me.nodules.Value
    .Cells(lRow, 26).Value = Me.hospitalised.Value
    .Cells(lRow, 27).Value = Me.died.Value
    .Cells(lRow, 28).Value = Me.d_death.Value
    .Cells(lRow, 29).Value = Me.vax_rcvd.Value
    .Cells(lRow, 30).Value = Me.doses.Value
    .Cells(lRow, 31).Value = Me.dlastvac.Value
    .Cells(lRow, 32).Value = Me.fullvax.Value
    .Cells(lRow, 33).Value = Me.pregnant.Value
    .Cells(lRow, 34).Value = Me.travel.Value
    .Cells(lRow, 35).Value = Me.contact.Value
    .Cells(lRow, 36).Value = Me.similar_case.Value
    .Cells(lRow, 37).Value = Me.dnoti.Value
    .Cells(lRow, 38).Value = Me.d_invest.Value
    .Cells(lRow, 39).Value = Me.serum.Value
    .Cells(lRow, 40).Value = Me.blood.Value
    .Cells(lRow, 41).Value = Me.dbs.Value
    .Cells(lRow, 42).Value = Me.swab.Value
    .Cells(lRow, 43).Value = Me.urine.Value
    .Cells(lRow, 44).Value = Me.other.Value
    .Cells(lRow, 45).Value = Me.dscoll.Value
    .Cells(lRow, 46).Value = Me.dlabrec.Value
    .Cells(lRow, 47).Value = Me.migm.Value
    .Cells(lRow, 48).Value = Me.migm_date.Value
    .Cells(lRow, 49).Value = Me.mpcr.Value
    .Cells(lRow, 50).Value = Me.mviral.Value
    .Cells(lRow, 51).Value = Me.mgenotype.Value
    .Cells(lRow, 52).Value = Me.m_strains.Value
    .Cells(lRow, 53).Value = Me.rigm.Value
    .Cells(lRow, 54).Value = Me.rigm_date.Value
    .Cells(lRow, 55).Value = Me.rpcr.Value
    .Cells(lRow, 56).Value = Me.rviral.Value
    .Cells(lRow, 57).Value = Me.rgenotype.Value
    .Cells(lRow, 58).Value = Me.r_strains.Value
    .Cells(lRow, 59).Value = Me.measfin.Value
    .Cells(lRow, 60).Value = Me.rubellafin.Value
    .Cells(lRow, 61).Value = Me.otherfin.Value
    .Cells(lRow, 62).Value = Me.invest_by.Value

    End With

    'clear the data on the form
    Me.epid.Value = ""
    Me.labid.Value = ""
    Me.pname.Value = ""
    Me.pers_ID.Value = ""
    Me.dob.Value = ""
    Me.age.Value = ""
    Me.agein.Value = ""
    Me.sex.Value = ""
    Me.phone.Value = ""
    Me.fam_members.Value = ""
    Me.prov.Value = ""
    Me.soum_dist.Value = ""
    Me.bagh_khoro.Value = ""
    Me.home_address.Value = ""
    Me.office_address.Value = ""
    Me.dhosp_visit.Value = ""
    Me.hosp_name.Value = ""
    Me.donset.Value = ""
    Me.fever.Value = ""
    Me.mp_rash.Value = ""
    Me.cough.Value = ""
    Me.coryza.Value = ""
    Me.conjunct.Value = ""
    Me.v_rash.Value = ""
    Me.nodules.Value = ""
    Me.hospitalised.Value = ""
    Me.died.Value = ""
    Me.d_death.Value = ""
    Me.vax_rcvd.Value = ""
    Me.doses.Value = ""
    Me.dlastvac.Value = ""
    Me.fullvax.Value = ""
    Me.pregnant.Value = ""
    Me.travel.Value = ""
    Me.contact.Value = ""
    Me.similar_case.Value = ""
    Me.dnoti.Value = ""
    Me.d_invest.Value = ""
    Me.serum.Value = ""
    Me.blood.Value = ""
    Me.dbs.Value = ""
    Me.swab.Value = ""
    Me.urine.Value = ""
    Me.other.Value = ""
    Me.dscoll.Value = ""
    Me.dlabrec.Value = ""
    Me.migm.Value = ""
    Me.migm_date.Value = ""
    Me.mpcr.Value = ""
    Me.mviral.Value = ""
    Me.mgenotype.Value = ""
    Me.m_strains.Value = ""
    Me.rigm.Value = ""
    Me.rigm_date.Value = ""
    Me.rpcr.Value = ""
    Me.rviral.Value = ""
    Me.rgenotype.Value = ""
    Me.r_strains.Value = ""
    Me.measfin.Value = ""
    Me.rubellafin.Value = ""
    Me.otherfin.Value = ""
    Me.invest_by.Value = ""
    Me.epid.SetFocus

    End Sub

    Private Sub cmdClose_Click()
    Unload Me
    End Sub

    Private Sub UserForm_Initialize()
    Dim cagein As Range
    Dim csex As Range
    Dim cfever As Range
    Dim cmp_rash As Range
    Dim ccough As Range
    Dim ccoryza As Range
    Dim cconjunct As Range
    Dim cv_rash As Range
    Dim cnodules As Range
    Dim chospitalised As Range
    Dim cdied As Range
    Dim cvax_rcvd As Range
    Dim cfullvax As Range
    Dim ctravel As Range
    Dim ccontact As Range
    Dim csimilar_case As Range
    Dim cserum As Range
    Dim cblood As Range
    Dim cdbs As Range
    Dim cswab As Range
    Dim curine As Range
    Dim cmigm As Range
    Dim cmpcr As Range
    Dim crigm As Range
    Dim crpcr As Range
    Dim cmeasfin As Range
    Dim crubellafin As Range
    Dim ws As Worksheet
    Set ws = Worksheets("LookupLists")

    For Each cagein In ws.Range("ageinlist")
    With Me.agein
    .AddItem cagein.Value
    .List(.ListCount - 1, 1) = cagein.Offset(0, 1).Value
    End With
    Next cagein

    For Each csex In ws.Range("sexlist")
    With Me.sex
    .AddItem csex.Value
    .List(.ListCount - 1, 1) = csex.Offset(0, 1).Value
    End With
    Next csex

    For Each cfever In ws.Range("feverlist")
    With Me.fever
    .AddItem cfever.Value
    .List(.ListCount - 1, 1) = cfever.Offset(0, 1).Value
    End With
    Next cfever

    For Each cmp_rash In ws.Range("mp_rashlist")
    With Me.mp_rash
    .AddItem cmp_rash.Value
    .List(.ListCount - 1, 1) = cmp_rash.Offset(0, 1).Value
    End With
    Next cmp_rash

    For Each ccough In ws.Range("coughlist")
    With Me.cough
    .AddItem ccough.Value
    .List(.ListCount - 1, 1) = ccough.Offset(0, 1).Value
    End With
    Next ccough

    For Each ccoryza In ws.Range("coryzalist")
    With Me.coryza
    .AddItem ccoryza.Value
    .List(.ListCount - 1, 1) = ccoryza.Offset(0, 1).Value
    End With
    Next ccoryza

    For Each cconjunct In ws.Range("conjunctlist")
    With Me.conjunct
    .AddItem cconjunct.Value
    .List(.ListCount - 1, 1) = cconjunct.Offset(0, 1).Value
    End With
    Next cconjunct

    For Each cv_rash In ws.Range("v_rashlist")
    With Me.v_rash
    .AddItem cv_rash.Value
    .List(.ListCount - 1, 1) = cv_rash.Offset(0, 1).Value
    End With
    Next cv_rash

    For Each cnodules In ws.Range("noduleslist")
    With Me.nodules
    .AddItem cnodules.Value
    .List(.ListCount - 1, 1) = cnodules.Offset(0, 1).Value
    End With
    Next cnodules

    For Each chospitalised In ws.Range("hospitalisedlist")
    With Me.hospitalised
    .AddItem chospitalised.Value
    .List(.ListCount - 1, 1) = chospitalised.Offset(0, 1).Value
    End With
    Next chospitalised

    For Each cdied In ws.Range("diedlist")
    With Me.died
    .AddItem cdied.Value
    .List(.ListCount - 1, 1) = cdied.Offset(0, 1).Value
    End With
    Next cdied

    For Each cvax_rcvd In ws.Range("vax_rcvdlist")
    With Me.vax_rcvd
    .AddItem cvax_rcvd.Value
    .List(.ListCount - 1, 1) = cvax_rcvd.Offset(0, 1).Value
    End With
    Next cvax_rcvd

    For Each cfullvax In ws.Range("fullvaxlist")
    With Me.fullvax
    .AddItem cfullvax.Value
    .List(.ListCount - 1, 1) = cfullvax.Offset(0, 1).Value
    End With
    Next cfullvax

    For Each cpregnant In ws.Range("pregnantlist")
    With Me.pregnant
    .AddItem cpregnant.Value
    .List(.ListCount - 1, 1) = cpregnant.Offset(0, 1).Value
    End With
    Next cpregnant

    For Each ctravel In ws.Range("travellist")
    With Me.travel
    .AddItem ctravel.Value
    .List(.ListCount - 1, 1) = ctravel.Offset(0, 1).Value
    End With
    Next ctravel

    For Each ccontact In ws.Range("contactlist")
    With Me.contact
    .AddItem ccontact.Value
    .List(.ListCount - 1, 1) = ccontact.Offset(0, 1).Value
    End With
    Next ccontact

    For Each csimilar_case In ws.Range("similar_caselist")
    With Me.similar_case
    .AddItem csimilar_case.Value
    .List(.ListCount - 1, 1) = csimilar_case.Offset(0, 1).Value
    End With
    Next csimilar_case

    For Each cserum In ws.Range("Serumlist")
    With Me.serum
    .AddItem cserum.Value
    .List(.ListCount - 1, 1) = cserum.Offset(0, 1).Value
    End With
    Next cserum

    For Each cblood In ws.Range("bloodlist")
    With Me.blood
    .AddItem cblood.Value
    .List(.ListCount - 1, 1) = cblood.Offset(0, 1).Value
    End With
    Next cblood

    For Each cdbs In ws.Range("dbslist")
    With Me.dbs
    .AddItem cdbs.Value
    .List(.ListCount - 1, 1) = cdbs.Offset(0, 1).Value
    End With
    Next cdbs

    For Each cswab In ws.Range("swablist")
    With Me.swab
    .AddItem cswab.Value
    .List(.ListCount - 1, 1) = cswab.Offset(0, 1).Value
    End With
    Next cswab

    For Each curine In ws.Range("urinelist")
    With Me.urine
    .AddItem curine.Value
    .List(.ListCount - 1, 1) = curine.Offset(0, 1).Value
    End With
    Next curine


    For Each cmigm In ws.Range("migmlist")
    With Me.migm
    .AddItem cmigm.Value
    .List(.ListCount - 1, 1) = cmigm.Offset(0, 1).Value
    End With
    Next cmigm

    For Each cmpcr In ws.Range("mpcrlist")
    With Me.mpcr
    .AddItem cmpcr.Value
    .List(.ListCount - 1, 1) = cmpcr.Offset(0, 1).Value
    End With
    Next cmpcr

    For Each crigm In ws.Range("rigmlist")
    With Me.rigm
    .AddItem crigm.Value
    .List(.ListCount - 1, 1) = crigm.Offset(0, 1).Value
    End With
    Next crigm

    For Each crpcr In ws.Range("rpcrlist")
    With Me.rpcr
    .AddItem crpcr.Value
    .List(.ListCount - 1, 1) = crpcr.Offset(0, 1).Value
    End With
    Next crpcr

    For Each cmeasfin In ws.Range("measfinlist")
    With Me.measfin
    .AddItem cmeasfin.Value
    .List(.ListCount - 1, 1) = cmeasfin.Offset(0, 1).Value
    End With
    Next cmeasfin

    For Each crubellafin In ws.Range("rubellafinlist")
    With Me.rubellafin
    .AddItem crubellafin.Value
    .List(.ListCount - 1, 1) = crubellafin.Offset(0, 1).Value
    End With
    Next crubellafin


    Me.epid.SetFocus

    End Sub

IMN logo majestic logo threadwatch logo seochat tools logo