Auto fill data based on cell value

  • HI


    Can anyone please help me on below? please....


    I've the below code running in working file to auto update the 'Sheet1 AM' raw based on the cell value of 'Formula G&H'. The problem is, I may need to insert some comments in AM cells, but with this code the AM cells are not accepting to insert any texts. Appreciate if somebody can help me on this case, thank you..


    Code
    Option Explicit Private Sub Worksheet_Activate()      Dim lngLastRow As Long          With Application         .ScreenUpdating = False         .EnableEvents = False     End With      'Clear existing range     lngLastRow = Cells(Rows.Count, "AM").End(xlUp).Row     If lngLastRow >= 8 Then         Range("AM8:AM" & lngLastRow).ClearContents     End If          'Populate new range if applicabale     lngLastRow = Sheets("Formula").Range("G:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row     If lngLastRow >= 8 Then         With Range("AM8:AM" & lngLastRow) 'As both start from Row 8 the 'lngLastRow' variable is the same for both sheets             .Formula = "=IF(OR(Formula!H8>0),""Rejected"",IF(Formula!I8>0,""Not Required"",IF(Formula!G8>0,""Y"",IF(SUM(Formula!G8:H8)=0,""Approved"",""""))))"             .Value = .Value 'Convert formula to values.  Comment out if you want the formulas to remain.         End With     End If          With Application         .EnableEvents = True         .ScreenUpdating = True     End With  End Sub



    Regards,
    Lennissa


  • Please help.......................

  • Try this worksheet event macro on Sheet1:


  • What's not working?? It is for me :confused:


    Follow these four steps and let me know how you go:


    1. Clear any data in Col AM of Sheet1 from cell AM8 down
    2. Put some random data in Col. AM
    3. Click any other tab and click back into Sheet1
    4. Note how only blank cells will be populated not any you filled in from step 2


    HTH


    Robert

  • HI Robert, iam really sorry that I did not explain you clearly.


    I mean, when i change the data in 'Formula G&H', the Sheet1 AM raw is not updating automatically. It is only updating when I clear the data's in AM, then change the tabs.


    Regards,
    Lenissa

  • Quote

    I mean, when i change the data in 'Formula G&H', the Sheet1 AM raw is not updating automatically. It is only updating when I clear the data's in AM, then change the tabs.


    Ah now I think I see what you mean. Try this (notice the new variable varMyArray which needs to contain every possible outcome the formula can produce):


Participate now!

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