Solved

Code to SAve DAta

Posted on 2011-02-15
8
179 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
  • 4
  • 4
8 Comments
 
LVL 19

Expert Comment

by:akoster
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:akoster
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 19

Expert Comment

by:akoster
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:
akoster 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now