Hello again,
Could you test following patch :
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 ... ???
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[
'Command Button "OK"
Private Sub CommandButton1_Click()
Dim lRow As Long
Dim mRow As Long
Dim nRow As Long
ThisWorkbook.Activate
lRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Row
lRow = lRow + 1
ActiveSheet.Range("B4:B14").Copy
ActiveSheet.Range("B" & lRow).PasteSpecial Paste:=xlPasteValues
mRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 4).End(xlUp).Row
mRow = mRow + 1
ActiveSheet.Range("E4:E14").Copy
ActiveSheet.Range("D" & mRow).PasteSpecial Paste:=xlPasteValues
nRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp).Row
nRow = nRow + 1
ActiveSheet.Range("F15:F16").Copy
ActiveSheet.Range("E" & nRow).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("B4:B14,E4:E14,F19:F20").ClearContents
Range("C2").Select
End SubPlease 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:
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 :
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 SubHope this will help
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 ...
Ok Carim, Thanks for the help so far.....Anyways if you get a solution in the near future please let me know....Ill wait ....meanwhile ill complete the remaining part of the workbook
Don’t have an account yet? Register yourself now and be a part of our community!