Using Excel to copy data from a "form" into a database.

BrisAdrian

New Member
Joined
Sep 5, 2015
Messages
22
I'm not sure how to word the question accurately so I have included some Pictures to aid me.

Using Excel 2010 I am trying to copy data from one sheet, the "Form" to a database called "Master" but the data needs to go into a Row that corresponds with employee's pay numbers.

This is the Form: (it was there in preview and then it disappeared when I posted)
http://imageshack/i/f0MWWm2Zp
f0MWWm2Zp




I only wish to enter Data in G5, G15,G17 and S15. All of that works. Where I am stuck is trying to then automatically copy the data from G15, G17 and S15 into the "Master" sheet and matching it up to the row that corresponds to G5. (I've noticed that if I copy the Cell while trying to record a Macro it will have 4 or 5 spaces AFTER the number, which means I cant find it in the Master sheet using CTRL+F)

I have this bit of code to get to the first empty column of the 'Master' sheet:

Code:
'find last used cell on the row to the right    ActiveCell.End(xlToRight).Select


'move one cell to the right from the last used cell
    ActiveCell.Offset(0, 1).Select
[code]

With the intent being that I run that code to insert the "Test Date" to the first empty column (X in my example), this should mean that the same code run after the first instance will place the "Test Result" into the next available column (Y in my example).

The data from S15 is easier because that will always be copied to the 'S' column in the 'Master' Sheet.

Here is an example of the "Master" sheet. (I think the picture is too long and it wont let me link it, however this is the same layout)

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]U[/TD]
[TD="align: center"]V[/TD]
[TD="align: center"]W[/TD]
[/TR]
[TR]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[TD]Emp Type[/TD]
[TD]Frequency[/TD]
[TD]Test Date[/TD]
[TD]Result[/TD]
[TD]Test Date[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]McClaine, John[/TD]
[TD]Full Time[/TD]
[TD]12[/TD]
[TD]2/02/2015[/TD]
[TD]4[/TD]
[TD]5/05/2015[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]








I am sorry to come out and completely ask someone to solve my problem like this.

But I've been trying to teach myself Excel and VBA (no luck really) since about 5pm on Friday when this got dumped on me and I have not stopped all weekend. Yep, working the weekend to fix something that someone who left 5 weeks ago left in absolute shambles.

For some insight, when I got the "Database" handed to me, 4/5 cells were highlighted with Yellow, Red, Blue, Green or Purple. There was no key so I never figured out what the colors meant. But it really didn't matter because the Pay Numbers and Employee details didn't match up 1/3 of the time AND all the data was out by at least 2 months. - sorry to vent... but nobody is in the office to see me pulling my hair out.

Thank you for even reading this and an enormous THANK YOU! for any help at all.

I hope that one day, after I cracked this nut I can post a guide to it on here and others will avoid a soul crushing 20 hour exercise in failure as I have.
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try the code below, change the sheet names to suit.

Rich (BB code):
Sub TransferIt()
    Dim strSearch As String, xrow As Long, lcol As Long

    strSearch = Sheets("Form").Range("G5")

    With Sheets("Master")

        With .Columns("A:A")
            .Replace " ", "", xlPart
            .Replace Chr(160), "", xlPart

            xrow = .Find(What:=strSearch, After:=Sheets("Master").Cells(1, 1), LookIn:=xlValues, _
                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
        End With

        lcol = .Cells(xrow, Columns.Count).End(xlToLeft).Column

        .Cells(xrow, "S").Value = Sheets("Form").Range("S15").Value
        Sheets("Form").Range("G15").Copy .Cells(xrow, lcol + 1)
        .Cells(xrow, lcol + 2).Value = Sheets("Form").Range("G17").Value

    End With
End Sub


Just a few things about posting info from your workbook...

Have a look at my signature block for some ways of posting usable screenshots (by usable I mean screenshots we can copy and paste directly into an Excel sheet)

If you really must take it off the board then use www.box.com or www.dropbox.com then post the actual workbook and not an image.

Basically what you will find is a lot of posters will ignore threads if they have to waste time retyping the data to test.
 
Upvote 0
Hi,
not tested but see if this solution helps:

Rich (BB code):
Sub UpdateRecord()
    Dim DataRange As Range, Item As Range, FoundRecord As Range
    Dim wsMaster As Worksheet
    Dim c As Integer


    With ThisWorkbook
        With .Sheets("Form")
            Search = .Range("G5").Value
            Set DataRange = .Range("S15,G15,G17")
        End With
        Set wsMaster = .Worksheets("Master")
    End With


    Set FoundRecord = wsMaster.Columns(1).Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    If Not FoundRecord Is Nothing Then
        c = 19
        For Each Item In DataRange
            wsMaster.Cells(FoundRecord.Row, c).Value = Item.Value
            c = c + 1
            If c = 20 Then c = 22
        Next Item


        'clear entry if required
        'DataRange.ClearContents


        MsgBox Search & Chr(10) & "Record Updated", 48, "Record Updated"


    Else


        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"


    End If
    
End Sub

Adjust sheet names shown in RED as required.

Dave
 
Upvote 0
Thank you guys so much!

I tweaked the code around a bit and it works a treat.

I've also learned more by seeing the and understanding what it is supposed to/does do. You've truly educated me.

Immensely grateful.
-Adrian
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top