Column Content

[sdckapr]

You could add this code to the Order's worksheet object.
WARNING. This code will disable UNDO. If you go this route to save worksheet space, the undo will be disabled on the orders worksheet.

When something in col B (and the row is >4) is entered the formulas will be created in Col F,G, H of those rows.

Code: Select all

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rCell As Range
  Dim rInt As Range
  Set rInt = Intersect(Target, Range("B:B"))
  If Not rInt Is Nothing Then
    Application.EnableEvents = False
    For Each rCell In rInt
      If rCell.Row > 4 Then
        rCell.Offset(0, 4).FormulaR1C1 = _
          "=IF(ISERROR(VLOOKUP(RC[-4]&"" ""&RC[-3],Previous!R18C5:R37C15,9,0))" & _
          ", """", VLOOKUP(RC[-4]&"" ""&RC[-3],Previous!R18C5:R37C15,9,0))"
        rCell.Offset(0, 5).FormulaR1C1 = _
          "=IF(ISERROR(VLOOKUP(RC[-5]&"" ""&RC[-4],Previous!R18C5:R37C15,9,0))" & _
          ", """", VLOOKUP(RC[-5]&"" ""&RC[-4],Previous!R18C5:R37C15,10,0))"
        rCell.Offset(0, 6).FormulaR1C1 = _
          "=IF(ISERROR(VLOOKUP(RC[-6]&"" ""&RC[-5],Previous!R18C5:R37C15,9,0))" & _
          ", """", VLOOKUP(RC[-6]&"" ""&RC[-5],Previous!R18C5:R37C15,11,0))"
      End If
    Next
  End If
  Application.EnableEvents = True
  Set rCell = Nothing
  Set rInt = Nothing
End Sub

Steve

[adam]

Thanks for the code Steve.

The data to the sheet Orders are copied from the sheet "New".

With the application of the VBA code to the sheet Orders, the columns where the Vlook up formula is applied gets cleared.

Is there a way I could Avoid this. Meaning keep the formula and column contents F,G, H?

Any help on this would be kindly appreciated.

[sdckapr]

The data to the sheet Orders are copied from the sheet "New".

I don't see how this would affect the macro since I saw no sheet named "new". But copying data to cols A-E should not affect now the macro works.

With the application of the VBA code to the sheet Orders, the columns where the Vlook up formula is applied gets cleared.

I am confused. The columns should get the formula you desired. The formula results could look like they are cleared (are they really cleard or do they display the null string?) if the lookup can not return a value (this is the reason for the iserror test in the formula).

Perhaps you should psot the current sample and explain exactly what you do and what results you get and what results you want to get...

Steve

[adam]

Yes Steve; the previous version of the workbook does not contain the sheet “New”.

But the attached version has.

Suppose, if you copy either one or two rows from the “New” sheet and paste them in the “Orders” sheet. The formula gets inserted to columns F, G & H (of the “Orders sheet”), but this clears the column contents from F, G & H of the same sheet as the data gets copied to the column B.

My intention is how to change the worksheet event code so that it does not clear the contents of columns F, G & H from the sheet “Orders” but keep the formulas in columns when data is present in the column B of the same sheet.

Note: I’m aware that the formula is designed in a manner that it will not show anything if the column M, N & O of the previous sheet is empty.

I hope this clears my question.

[sdckapr]

If I copy items from NEW to orders it does as I expect it to. The values do not get cleared, they turn into the formulas desired. As I mention before they appear to be cleared since you mask the fact that they find no match with a null string.

Could you explain what you want the code to do? If columns F, G, H in orders have something in the cells when the code is run, do you want the code to NOT replace it with a formula? If so you can change the lines of code to:

Code: Select all

    For Each rCell In rInt
      If rCell.Row > 4 Then
       If IsEmpty(rCell.Offset(0, 4)) Then
          rCell.Offset(0, 4).FormulaR1C1 = _
            "=IF(ISERROR(VLOOKUP(RC[-4]&"" ""&RC[-3],Previous!R18C5:R37C15,9,0))" & _
            ", """", VLOOKUP(RC[-4]&"" ""&RC[-3],Previous!R18C5:R37C15,9,0))"
        End If
        If IsEmpty(rCell.Offset(0, 5)) Then
          rCell.Offset(0, 5).FormulaR1C1 = _
            "=IF(ISERROR(VLOOKUP(RC[-5]&"" ""&RC[-4],Previous!R18C5:R37C15,9,0))" & _
            ", """", VLOOKUP(RC[-5]&"" ""&RC[-4],Previous!R18C5:R37C15,10,0))"
        End If
        If IsEmpty(rCell.Offset(0, 6)) Then
          rCell.Offset(0, 6).FormulaR1C1 = _
            "=IF(ISERROR(VLOOKUP(RC[-6]&"" ""&RC[-5],Previous!R18C5:R37C15,9,0))" & _
            ", """", VLOOKUP(RC[-6]&"" ""&RC[-5],Previous!R18C5:R37C15,11,0))"
        End If
      End If
    Next

But this defeats the purpose of keeping it updated when new values are added in Previous (which I thought was the reason you wanted the formulas in Orders.

Please detail exactly what you want to happen.

Steve

[adam]

Thanks for the help Steve. To detail my query I've attached a sample workbook with four sheets.
The sheet "previous" acts a template to edit previous orders.

For example when you type the serial number 1122 in cell O6, the code embedded in the worksheet module fills the columns F & N by data taken from the sheet "Orders".

When the worksheet module code fills the above mentioned columns; the columns H,K & M gets filled by the vLookup formula with data taken from the "Products" sheet.

The "Orders" sheet acts as a database sheet. Meaning the user copies the rows from the "New" sheet and keeps pasting on the "Orders" sheet. Sometimes one row or two or more rows simultaneously.

If the user wants to edit the order 1122 he will write the serial number in cell O6, with this the appropriate columns gets filled with data from the above mentioned sheets. And when he wants to edit either the quantity of lets say for example Orange from 1 to 50.

He will then type 50 and saves the workbook. With this the 1 from the sheet "Orders" also have to get changed to 50 with the help of either vLook up formula or code.

And with the rest of the columns and rows.

I hope this makes my query clear.

[sdckapr]

I am completely lost. If you change the value of Previous!O9 and all the values in Previous Col D change all the formulas you created will in Orders will no longer work since the serial number will no longer match. And you still haven't answered about the code I wrote which would not create formulas if there were pre-existing content in Orders col F,G, H. If that is not the case, please explain when you want formulas in FGH and when to leave the values alone. I also don't understand the purpose of the duplicated entries: Previous seems to use lookups for the products, but not the quantity and in Orders the products are stagnant but the other items are lookups.

Now that your example has 4 sheets, please state the purpose for each sheet. Where is the user going to enter data, and where is the data to be calculated? How is the spreadsheet supposed to function?

I have no idea of what you want. It just seems to me that you keep changing the question...

Steve