Link to home
Start Free TrialLog in
Avatar of cssc1
cssc1Flag for United States of America

asked on

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
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image

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
Avatar of cssc1

ASKER

I don't know how to code at all. I clicked the SAVE button and nothing happens?
Please help
Thanks
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 ?
Avatar of cssc1

ASKER

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
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




Avatar of cssc1

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Arno Koster
Arno Koster
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cssc1

ASKER

Thanks!