User Form To Add Data In Columns

    Hi there all


    Hopefully just a quick fix, but i Cant seem to get the code right. I need to use the user form to create the fields in the next free column in the parts data sheet. At the moment, it just writes over the firsst part i create. Have tried countless methods but am new to this vba business. To take it a step further, i need to concatenate the three fields and copy this into the parts data entry, Again i have tried (see code) but cant get it to work. Finally, If possible, I would like to check the parts already created, so duplicates cannot be made. Please see exmaple attached.


    Many thanks in advance for your help.


    Boynsy

    Re: User Form To Add Parts Into Columns


    Boynsy,


    Take a look at these two statements and see if they do what you want.


    Code
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
iCol = ws.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Column


    Jim

    Re: User Form To Add Parts Into Columns


    I've always been a fan of the short, sweet "Do Until you find an empty cell" loop:

    Code
    Do
        
                            If IsEmpty(ActiveCell) = False Then
                        
                                ActiveCell.Offset(1, 0).Select
                        
                            End If
                        
                        Loop Until IsEmpty(ActiveCell) = True


    I've used it numerous times in various projects to find empty rows and columns. To make columns, though, you have to change the middle line, the Activecell.Offset to read

    Code
    ActiveCell.Offset(0, 1).Select


    I do have to agree with the point Norie raises. Is there a reason you're organizing it by rows instead of columns? I find that data like this flows a little better (and is easier to read/code/think about) if you're looking at it as Row line items.

    Many thanks, :music:
    --epynephrin

    Re: User Form To Add Parts Into Columns


    Hey Guys


    Thanks very much for the help, I will try both solutions tomorrow morning. As for organising this data in cloumns, the reason being is that I think at the very very most I will have around 100 parts and only up to arund 50 in use at any one time. running down the worksheet will be date (every day ongoing) as this is intended on being used as a daily stock and order programming workbook. By utilising several sheets, I can have input daily loses on one, starting stock on another etc. to give a stock position on all lines on one sheet. I saw this as easiest way of going about the task. If anyone knows of a better idea im open to suggestions!!!


    Anyways thanks for the help, ill let you know how i get on.
    Cheers

    Re: User Form To Add Data In Columns


    Hi Guys.


    Thanks those codes worked perfectly. Out of curiousity, which is the most efficient and why?


    Secondly, i managed to get the concatenate code right and enter it below in (5, icol), however, I have no idea how I can get it to check that the part has not been entered previously and prevent this from happening? Does anyone have any ideas on this?


    Thanks again.


    Boynsy

    Re: User Form To Add Data In Columns


    Hi Dave (et al)


    I am trying to implement the find method but am a little stuck because i dont know the correct sytax or code. Basically, what I was doing in was to find the value of the formula pasted, but this will always return a positive find (obviously!). I need to know how i can concatenate the 4 selections in the form and search the rows, if there is that part found, message box to say part already exits, end sub, before pasting to the parts databse.


    Hope this makes sense.


    Any help would be much appreciated, I hate not being able to do this myself....

    Re: User Form To Add Data In Columns


    Boynsy


    I really think you should rethink how you are structuring this data.


    The way you have it now isn't conducive to either analysis or to any of the other things you want to do. eg check for duplicates

    Boo!:yikes:

    Re: User Form To Add Data In Columns


    Hover over the keyword Find and follow the link to the Find Method. Also, consider the use of COUNTIF in your code. E.g


    Code
    IF WorksheetFunction.CountIf(Columns(1),ValueToCount)>0 Then


    However, as Norie states, a good foundation is fundamental

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login