Solved

Code to SAve DAta

Posted on 2011-02-15
8
178 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 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

27 Experts available now in Live!

Get 1:1 Help Now