help with Excel macro

[petezh193]

I am designing a “basic” database in work and having trouble with some macros

The database is a record of production. Information is added at pre and post production. Adding the pre-production information I have sorted. A form is filled in, submit button hit and a new row is created with the information in. Along this row there are gaps where post production information is entered, but trying to get this information to paste into the correct row I cant do.

I have a form to fill in the information (one each for pre and post production) which I then want adding to the database against the reference number. Converting the information from the input form into the database I can do for pre production, but getting the post-production data entered into the database along the correct row I don’t know how to get there.

I believe I want a search function that takes the database reference number (on the post-production form) and finds this in column A. once this ref number is found, the cell is active and I can to paste the information from the post production form into the gaps on the correct row

Anyone have a clue?

[soybean]

Can you clarify a point here?  Your title is "help with Excel macro".  Excel is a spreadsheet program, yet you refer to a database over and over and do not once mention a spreadsheet or other terms, such as Workbook or worksheet, which imply Excel.  Macrosoft Access a database and is part of the Pro version of Microsoft Office.  So, are you using Access?  If not, I'm curious why you keep referring to database when you are using Excel, a spreadsheet program.  Or, are you using both for that application you are working with? 

[petezh193]

i am using an excel spreadsheet as a database sheet,

hence wanting help with a macro in excel for my "database" (a spreadsheet of the information detailing the production history)

i have a worksheet with a pre production form
a worksheet with a post production form
and a a worksheet with a database of the information.

i have a macro that will take the information from the pre-production and drop it into the database worksheet, but trying to get the post-production information from that worksheet to the correct row in the database worksheet i cannot do

i hope this clarifies my point and answers your questions?

[soybean]

trying to get the post-production information from that worksheet to the correct row in the database worksheet i cannot do

Does that "correct row" vary each time you update the database?  In other words, it could be row 500 one time and some other row the next time?  Did you create the macro using the VB editor built-in to Excel?  Can you post the code in the macro here?

[petezh193]

yes that row changes each time, ie the first production run record would have reference 1, the second submit of production data would have ref 2 etc

the below was created in VBA. the fist part finds the first empty cell and pastes the data from pre-production into the database spreadsheet
for ease i have removed 95% of the coding to give chance to read all on one screen!

i am looking at doing exactly the same function with the post-production details, without finding the next empty cell, instead pasting to a specific row as determined by the operator

(ie post production form has "enter DB ref No here" cell which i would like the macro to select, find that value in column A, activate that cell, then take the values from post-production worksheet and paste using similiar coding below (offset) to paste into the correct row

Sub test4temp()
Application.ScreenUpdating = False
' test4temp Macro
'
'selects the last cell in column A and moves to the next cell
Sheets("Database MkI").Select
    Range("A1").End(xlDown).Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    'adds +1 to the previous cells figure
        ActiveCell.FormulaR1C1 = "=R[-1]C+1"
'select, copy and pastes the Objective/Customer details
Sheets("job card").Select
    Range("B5:e5").Select
  Selection.Copy
   Sheets("Database MkI").Select
     ActiveCell.Offset(0, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'select, copy and pastes the Project Number details
    Sheets("job card").Select
    Range("B3").Select
  Selection.Copy
   Sheets("Database MkI").Select
     ActiveCell.Offset(0, 1).Select
     ActiveSheet.Paste
'select, copy and pastes the details of who submitted the job
Sheets("job card").Select
    Range("B30").Select
  Selection.Copy
   Sheets("Database MkI").Select
     ActiveCell.Offset(0, 1).Select
     ActiveSheet.Paste

......etc for many more variables
       
End Sub

[soybean]

I'm not sure I'll be able to help you resolve this.  Let's see whether someone else has some suggestions.  And, I'm going to suggest you register http://www.excelforum.com/ and post your question there.

[petezh193]

ok thank you for your time 

[alexK]

Tooooo complex for MS Excel, tooooo problematic.

You should start over using MS Access, there is where your project belongs.
 

[soybean]

You should start over using MS Access, there is where your project belongs.
 

Good point.  I totally agree. 

i have a worksheet with a pre production form
a worksheet with a post production form
and a a worksheet with a database of the information.

With Access, pre production data could be one table and post production data another table.  The third part could be another table or query derived from the first two tables.

[alexK]

Soybean, Right you are with those tables!

Having worked with Mainframe IMS Data Bases (heavy duty stuff) for decades, MS Access has a short learning curve.
MS Access has surprisingly infinite possibilities to solve complex problems.

Ms Excel has limited scope, and is sometimes frustrating. It is not really designed to do that kind of stuff.

Learn from me!
 

[petezh193]

thanks but i am simply following the instructions from my line manager......will consider it but no one in this team has any experience with access so not a favoured option