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

[BrisAdrian]

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

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:

'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.

 

[BrisAdrian]

I don't know what the go is with those pictures. I'm really sorry.

It might be because image hosting sites are blocked by the work network.

Here are the links if they work: https://imageshack.com/i/f0MWWm2Zp and https://imageshack.com/i/ipIaoJKhp that last bit may be iplaoJKhp as I'm trying to read it off my phone.

I'm really sorry this whole thing is such a mess.

Thank you again,
-Adrian

 

[MARK858]

Try the code below, change the sheet names to suit.

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.

 

[dmt32]

Hi,
not tested but see if this solution helps:

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

 

[BrisAdrian]

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