Solved

Code to SAve DAta

Posted on 2011-02-15
8
184 Views
Last Modified: 2012-05-11
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
Comment
Question by:cssc1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34898612
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
 

Author Comment

by:cssc1
ID: 34898719
I don't know how to code at all. I clicked the SAVE button and nothing happens?
Please help
Thanks
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34916344
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cssc1
ID: 34938858
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34944112
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
 

Author Comment

by:cssc1
ID: 34947167
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
 
LVL 19

Accepted Solution

by:
Arno Koster earned 500 total points
ID: 34969276
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
 

Author Closing Comment

by:cssc1
ID: 34969541
Thanks!
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question