Adding a value from text box to the last used cell

  • Hello again,


    Could you test following patch :


    Code
    Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim LastRow As Long
    LastRow = Application.WorksheetFunction.Max(ActiveSheet.Range("E15").End(xlUp).Row, 4)
    ActiveSheet.Cells(LastRow, 5) = TextBox4.Value
    End Sub

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

  • hi, sorry for the delay....was out for some work..
    well once all items of a client are scanned the "OK" button either on the worksheet or the frmsaleScan userform transfers the sale record down the sheet to the "sale Registry" from here on the sale in bottles of all items is transfered to column "AS' And "AU" in the inventory section for calculating closing stock and preparing the daily sale report....
    Thanks once again....ill test the above code as soon as i reach my office.....just an hour or two..
    Regards

  • Hello again,


    From what my understanding is of your CommandButton1 ( ' OK ' Button ) ... it does copy your ranges in Columns B, E and F to specific locations ...


    BUT these ranges have already been filled ... no ... ???

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

  • Yes you are right......but as you and when a sale is completed and "OK" pressed it stores under the last used row in their respective columns B, D and E...and it keeps adding up[


    Please note after this procedure at the end the ranges B E and F in the worksheet are cleared and ready for another scan.

  • Thanks for your confirmation ...


    But that is exactly the problem ...!!!


    To answer my question about HOW is your Scanner selecting the ROW where the data gets stored ...


    your answer was : it is the 'OK' button ... And since now ... you agree it is NOT this macro ...


    This question is still not answered ... :wink:

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

  • Hi, First....I tried your code....now its getting the value in the right column "E" but it comes one row below the existing quantity "1" whereas it should overwrite this value "1" with the text box value.


    Your question....When a bar code is scanned the bar code information (13 digit number) is sent to Range B4:B14....for each barcode number in this range, Column C4:C14 pulls up the respective data using VLOOKUP from range M1:Q6000 ie the Item Name.......and similarly the price Column D4:D14.


    Your question where the scanned data is getting stored....as i explained earlier......initially the data is not getting stored anywhere and is just displayed in the billing section i.e the scanning page Range B4:E14, After all purchased items are scanned with their respective quantity and confirmed.....then the "OK" button confirms the transaction or the "Cancel" button deletes the transactions.

    The rest on confirming "OK" the data is copied to range B25, D25 and E25 down.


    and this repeats for each billing cycle. These codes are working perfectly and tried and tested on field.,

  • Hello again,


    If the offset you have tested is consistently the same ...
    following modification should allow for the correction :


    Code
    Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim LastRow As Long
    LastRow = Application.WorksheetFunction.Max(ActiveSheet.Range("E15").End(xlUp).Row, 5)
    ActiveSheet.Cells(LastRow-1, 5) = TextBox4.Value
    End Sub


    Hope this will help

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

  • yes Carim I already tried this....works fine for the first scanned entry. But say the "quantity" for the second scanned item has to be changed.......It doesn't work....it changes the quantity of the first scanned item.
    I also put this code in the TextBox "Change Event" .....which gives instant change in value without KeyDown...which is again more preferable.

  • You have the downloaded workbook why don't you try out practically...so you'll know where the problem is......As far as Barcodes are concerned , change a few barcode digits in Column "M" to any convenient 4-5 digit number.....so you can manually feed the bar codes in the scanning area and see the outcome of the code

  • Hi Carim......Just an observation, my own work.....don't know if it is possible....
    Say we go around this problem...just forget the xlUp and xlDown
    In the main form ie the frmSaleScan userform there is a list box where all the items scanned are displayed ...can't we just set focus on the item we want the quantity changed and use the setfocus property to change the corresponding "quantity" value via the TextBox4.
    This way all items can be scanned and if at all the quantity is to be changed....just set focus on the item and change the value in TextBox4
    Sorry if i am ignorant....just a guess
    Regards

  • Sorry cannot help you any further ... :dead:


    Indeed, have tried to open your workbook ... but on my computer ...Excel is crashing ..


    Certainly another contributor will help you out ...

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

Participate now!

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