Posts by phiyen8902

Hi Mumps,
[FONT="Times New Roman"]I try to update the information (col L,M,N) from Data wb into SHIP VIET wb (col K,L,M) base on invoice (col E). Because I want the macro only run in some lines what I choose in col E so that I use “For Each cell In Selection” and the macro can run well. However, when user choose the wrong col (other cols instead of col E), this code will remove the data in other cols because it will add the formula in col 6,7,8 count from col E. So that I want to reduce this problem by limited the col which was choosed by user, the macro only run if they choose right col E, otherwise the macro wont be run. Thanks your support. P/S: Pls see the attached file.[/FONT]

I would like to limit the range in selection which only select within column (5) but I don’t know how to fix the code. My purpose is the macro only run if user choose range within column(5) because if they choose the range in other columns, the macro cant run well, otherwise the macro will be stopped or show the message to user that “pls choose in range column(5) only”. Pls help me to solve this problem. Thanks in advance.

Public Sub Update_Data()
    Dim cell As Excel.Range
    For Each cell In Selection
        If (cell.Value = "") Then
          GoTo Continue
        End If
            cell.Offset(, 6).FormulaR1C1 = "=VLOOKUP(RC[-6],'[DAILY IMPORT STATUS 2019.- REVISEDxlsx.xlsx]Sheet1'!C6:C14,7,FALSE)"
            cell.Offset(, 6).Value2 = cell.Offset(, 6).Value2
            cell.Offset(, 7).FormulaR1C1 = "=VLOOKUP(RC[-7],'[DAILY IMPORT STATUS 2019.- REVISEDxlsx.xlsx]Sheet1'!C6:C14,8,FALSE)"
            cell.Offset(, 7).Value2 = cell.Offset(, 7).Value2
            cell.Offset(, 8).FormulaR1C1 = "=VLOOKUP(RC[-8],'[DAILY IMPORT STATUS 2019.- REVISEDxlsx.xlsx]Sheet1'!C6:C14,9,FALSE)"
            cell.Offset(, 8).Value2 = cell.Offset(, 8).Value2


Continue:
    Next cell
End Sub

Hi everyone,

Could you please help me solve this problem. Thanks so much.

Hi everyone,

Could you please help me solve this problem. Thanks so much.

The purpose of this macro is that coping informations from Discrepancy Report into Replacement chart. And I want to add more row Prepared by: Name into col W (issued by) of Replacement chart, but the code is not working. Pls run the macro and help me. Thanks in advance.

If Cells(DisReport_row, 1).Value = "Prepared By :" Then SumReport_col = Cells.Offset(DisReport_row, 2).Value

Hi Carim,

The macro runs very smoothly and it help me save a lot of time in working. Thanks so much for your help.

Hi Carim,

Thanks for your generous help. Whether have any solution if I want to paste full list at one time instead of row by row?

Hi Carim,
It looks like a search box and I will check the status of goods. The customers will send me the list which only has PO_NO (col A) and DESCRIPTION (col B). When I copy col A and B from List sheet into SearchnPO sheet , It will search in DATA sheet, and then it fills col C,D,E,F,G,H,I,J,K. The real DATA sheet is over100000 rows.

Ex: In current macro: I paste information in col A only (yellow highlight – row 2 – SearchnPO) => the SearchnPO sheet will fill in all other col from B to K. It worked well if the PO_NO in DATA sheet is not duplicate. So in furture macro I wish I can paste information in col A and B (yellow highlight – row 3 – SearchnPO) and the SearchnPO sheet will fill in all other col from col C to K. Pls refer re-attached file which add List sheet. Thanks for your help.

Hi Carim,

Sorry about that, I re-attached the file.

Hi everyone,
I want to vlookup informations when I input the code number in column A. However, the problem is that the data has same code number but different the description (col B). So that the result always return the first row information if it has 2 rows which is same the code number. Pls help me fix the macro which can combine 2 conditions. Ex: input the number code (col A) and 7 first letters of description (col B - yellow highlight in sheet SearchnPO). Thanks in advance.

Hi Carim,

I got what you mean. Thank you so much for your helping me.

Hi Carim,

Let me make clear my problem. when I input data in B6, the macro will fill in the information automaticly, however, if I input data from range B6:B10 at the same time, the macro didnt fill in the information. Hope that you can help me, thanks in advance.

Hi Carim,

It's me again. i used this code for some days and It worked well if I type data one by one in the chart, however when I copy more than 1 row, it's not work until I double click on each cells. Could you advice me how to fix it?

Hi Carim,

When i run this macro, all my excel chart cant use "ctrl + Z" to undo my wrong typing. Could you instruct me how to change from Private Sub Worksheet_Change(ByVal Target As Range) into a normal Sub that I can use a button to run it. Thanks so much!

Hi Carim,

It worked well, thanks for your help.

So how can i fix it because i'm a newer. Thanks!

I enter data in col B and E, then I insert some formular to fill in informations from another workbooks. However, when enter more data and run the code, it changed all my data in col E and removed informations in other cols. Besides, I tried to hide the formular but It doesn’t work. Pls help me. Thanks in advance.

Hi Kjbox,

Thanks for your help, it worked well

Hi everyone,
I would to remove all dash in a range which selected but it only works for one cell in selection eventhough many cells in selected. EX: from AZ015283MST-4 to AZ015283MST without "-4". If you have free time pls help me. Thanks in advance!