Form for tracking samples, copy paste from form based on index row

  • I'm trying to create a form for a lab to help sample tracking. They are doing tracking now by filling out a form by hand and then transcribing back into a spreadsheet.


    The first iteration I tried works but the index or "RD#" was matched to the row. Right now I'm trying to match the value in column "A" in the sample log worksheet with the RD# in the Form as the criteria to copy over values into the sample log worksheet instead of using the row number. I don't know what happened but I can't even get the lastRow portion of the code to give me the correct value. I've gotten several type mismatch errors.


    Any help is greatly appreciated.


    Here is the code that's giving me trouble:

  • Hello and Welcome to the Forum :)


    You could test following



    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • That code is giving me similar results. The lastrow value is 4, which isn't true. It should be 6. The other thing is that the RDiRow=0, so maybe that's what's leading to the type mismatch error here:


    RDiRow = Application.Match(RDi, Range("A1:A" & lastRow), 0)


    Not sure... but thanks for the assistance.

  • Hello,


    Most probably a point is missing in the instruction, just before the word Range


    RDiRow = Application.Match(RDi, .Range("A1:A" & lastRow), 0)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Carmin,


    Thanks for the welcome- didn't see all of your message before. I'm still getting use to the forum.


    That helped, but I'm still having some issues. One thing that I noticed is that the lastrow was reporting 3 when it should have been a larger number. I think that was because some later calculated rows stopped at row 3, So I tried to change lastrow code to only look in the A column:


    lastRow = .Cells.Find(What:="*", _

    After:=.Range("A1:A1048576"), _


    But I got another type mismatch error where the lastrow code is. I did get the code to work when I extended that calculated range to match the row and it worked but only if I got the rows to match. I thought the code above would limit the search to only the A column.

  • Hello again,


    You can simplify your last row instruction :


    Code
    1. lastRow = .Cells(Rows.Count, 1).End(xlUp).Row

    and your code would look like following :

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I think that should do the trick. I tried that before but I think there was a couple of other things wrong that was leading to the errors. Either way, the previous code was working and I can just use another sheet for calculated values that was giving the lastrow method issues. It was getting kind of cumbersome to look at all that data on one sheet anyway.


    Thank you so much for the help. I'm glad that I wasn't too far off with what I had to start with. I don't use VBA often.