Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

Code to SAve DAta

I am trying to :
Save the daily inspection data that is entered on the Main Menu form to the fields on the Archive form. The data, for example, that is entered for 2/11/2011 must be put in the 2/11/2011 section on the Archive form.

I would like to do this with a macro or some code that is attached to a button on the Main Menu.
Copy-of-P-214-Safety-Inspection-.xls
0
cssc1
Asked:
cssc1
  • 4
  • 4
1 Solution
 
Arno KosterCommented:
this should get you started :

find this code in the updated attached file and change it to your liking


Public Sub save_data()
Dim pos As Double

    '-- data from main page
    inspection_date = Range("C8")
    project_number = Range("C10")
    project_name = Range("C12")
    
    '-- find corresponing archive entry
    Set result = Sheets("Archive").Range("A:A").Find(what:=inspection_date, LookIn:=xlValues)
    If Not result Is Nothing Then
        pos = result.Row
    Else
        '-- archive date not found, add it
        pos = Sheets("Archive").UsedRange.Rows.Count + 1
        Sheets("Archive").Range("A" & pos) = inspection_date
    End If
    
    '-- save data from main page
    Sheets("Archive").Range("F" & pos) = "Project [" & project_number & "]: " & project_name

End Sub

Open in new window


update-Copy-of-P-214-Safety-Insp.xls
0
 
cssc1Author Commented:
I don't know how to code at all. I clicked the SAVE button and nothing happens?
Please help
Thanks
0
 
Arno KosterCommented:
Before clicking on the save button, archive row 43 is empty. After clicking on the save button, row 43 of the archive worksheet is filled with data from the main menu form, as you requested ?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cssc1Author Commented:
Akoster,
  Can you give me an example code on how to add thre following to the archive sheet:

1. Recommend Description
2. No
3. OSHA Regulation
4. Corrective Action
Comments

Thanks
0
 
Arno KosterCommented:
sure !

you can use this code :
Public Sub save_all_data()
Dim pos As Double
Dim result As Range
Dim detail_col As Integer
Dim detail_row As Integer
Dim inspection As Integer
Dim inspection_job As String
Dim inspection_date As String
Dim inspection_description As String
Dim inspection_no As String
Dim inspection_regulation As String
Dim inspection_action As String
Dim inspection_comments  As String
          
    '-- process data from Insp_Form sheet
    For inspection = 1 To 7
        '-- gather data
        inspection_job = Sheets("Insp_Form").Cells(8, 3 + (inspection - 1) * 15)
        inspection_date = Sheets("Insp_Form").Cells(9, 3 + (inspection - 1) * 15)
        inspection_description = Sheets("Insp_Form").Cells(43, 2 + (inspection - 1) * 15)
        inspection_no = Sheets("Insp_Form").Cells(43, 5 + (inspection - 1) * 15)
        inspection_regulation = Sheets("Insp_Form").Cells(43, 6 + (inspection - 1) * 15)
        inspection_action = Sheets("Insp_Form").Cells(43, 10 + (inspection - 1) * 15)
        inspection_comments = ""
        For detail_col = 60 To 63
            For detail_row = 2 To 13
                inspection_comments = inspection_comments & Sheets("Insp_Form").Cells(detail_col, detail_row + (inspection - 1) * 15)
            Next detail_row
            inspection_comments = inspection_comments & vbLf
        Next detail_col
        
        '-- find corresponding archive entry
        Set result = Sheets("Archive").Range("A:A").Find(what:=inspection_date, LookIn:=xlValues)
        If Not result Is Nothing Then
            pos = result.Row
        Else
            '-- archive date not found, add it
            pos = Sheets("Archive").UsedRange.Rows.Count + 1
            Sheets("Archive").Range("A" & pos) = inspection_date
        End If
        
        '-- save data
        Sheets("Archive").Range("B" & pos) = inspection_description
        Sheets("Archive").Range("C" & pos) = inspection_no
        Sheets("Archive").Range("D" & pos) = inspection_regulation
        Sheets("Archive").Range("E" & pos) = inspection_action
        Sheets("Archive").Range("F" & pos) = inspection_comments
              
    Next inspection
    
    '-- finished !
    Set result = Nothing
    
End Sub

Open in new window




0
 
cssc1Author Commented:
akoster:
   Could you take a look at where i put the code.
I am not sure it is in the correct place since it does not work
Thanks
update-Copy-of-P-214-Safety-Insp.zip
0
 
Arno KosterCommented:
The code pas placed in the right location, the 'problem' was that the "save" button was still linked to the old macro code.

I updated the button to link to the new macro code. update-Copy-of-P-214-Safety-Insp.xls
0
 
cssc1Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now