VBA for auto update cell

Lenissa · Apr 24th 2018

Hi
Am a beginner for VBA, hence pls help on below simple case.

I have an excel shared file which is running with the VBA code. I want an VBA (not formula) for below case.

I want to auto update the 'Sheet1 Raw AM8 to AM3000' based on the cell value of Sheet4 G8 to G3000 & H8 to H3000. Following way i would want to update the cell...

If the Sheet4 G8 Columns value is more than 0, then the Sheet1 AM needs to update as Y'.
If the Sheet4 H8 Columns value is more than 0, then the Sheet1 AM needs to update as Rejected.
If both Sheet4 G8 & H8 Columns value is more than 0, then the Sheet1 AM needs to update as Rejected.
If both Sheet4 G8 & H8 Columns value is 0, then the Sheet1 AM needs to update as Approved.

Appreciate your soonest kind reply on this case... Thank you...

Lenissa · Apr 24th 2018

Attaching the file for reference...

**Trebor76** · Apr 24th 2018

Hi Lenissa,

Welcome to Ozgrid!!

Could you please check if the following formula works as expected by putting it into cell AM8 of Sheet1 and copying it down to cell AM31:

=IF(OR(Sheet4!H8>0,SUM(Sheet4!G8:H8)>0),"Rejected",IF(Sheet4!G8>0,"Y",IF(SUM(Sheet4!G8:H8)=0,"Approved","")))

If it does I'll convert it to a macro for you. If not please amend it so it's correct and I'll convert that formula.

Thanks,

Robert

Lenissa · Apr 24th 2018

Hi Robert
Thanks for your advise...

I've amended the code as the Y' result was not coming. Below is the corrected formula & pls help to convert it to the VBA. Kindly note that file which am going to add,already has an VBA, So please help to advise how i will add this to the same file, thanks...

=IF(OR(Sheet4!H8>0),"Rejected",IF(Sheet4!G8>0,"Y",IF(SUM(Sheet4!G8:H8)=0,"Approved","")))

Lenissa · Apr 24th 2018

Pls see the corrected new formula in below, as I've added one more criteria, thanks...

=IF(OR(Formula!H8>0),"Rejected",IF(Formula!I8>0,"Not Required",IF(Formula!G8>0,"Y'",IF(SUM(Formula!G8:H8)=0,"Approved",""))))

**Trebor76** · Apr 25th 2018

Hi Lenissa,

Here's some code putting your last formula into Sheet1:

Code

Option Explicit
Sub Macro1()


    Dim lngLastRow As Long
    
    Application.ScreenUpdating = False
    
    lngLastRow = Sheets("Formula").Range("G:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Sheets("Sheet1").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
    
    Application.ScreenUpdating = True


End Sub

Display More

I see you've also changed the name of Sheet4 to Formula. Note you will have to change the macro if you change the sheet names going forward.

Regards,

Robert

Lenissa · Apr 25th 2018

HI Robert, Thanks for your help.

I've added this code to the workbook, but it is not running automatically. But when i click the run button its working perfectly. Really appreciate if you can help me on this, thanks...

**Trebor76** · Apr 25th 2018

What trigger do you want for the macro to run? Perhaps when Sheet1 is selected?

Lenissa · Apr 26th 2018

Hi Robert,

I want to Mauro update the Sheet1 AM Cell whenever I change the numbers in formula sheet in G,H,I cells.
When i use this VBA code it s not running automatically. But its is running perfectly when i click the running button in VBA page.
Appreciate your help here...

**Trebor76** · Apr 26th 2018

Will the numbers in columns G and H be formula driven or manually keyed in? Why do you want to include changes in column I as well?

I think the best way is for Sheet1 to automatically updated each time it's selected.

Lenissa · Apr 26th 2018

HI Robert
the columns G& H are driven by formula. Am sorry, no need to include column I, thanks...

**Trebor76** · Apr 26th 2018

OK, automatically populating Col. AM of Sheet1 when it's selected (activated) is definitely the way to go then. Put this worksheet event macro* onto Sheet1:

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

Display More

Regards,

Robert

*Note to install this macro follow these four steps:

Copy my code to the clipboard (Ctrl + C)
Right click on Sheet1 and click View Code from the shortcut menu
Paste (Ctrl + V) my code from step 1 into the Visual Basic Editor (VBE)
From the File menu select Close and Return to Microsoft Excel

Lenissa · Apr 26th 2018

Hi Robert
Am really sorry to bother you a lot..But the sheet one is already running with the another VBA code, I've inserted this code as new module only. But its not updating automatically. Pls correct me if am wrong, thanks...

**Trebor76** · Apr 26th 2018

Quote

I've inserted this code as new module only.

No, it has to go onto Sheet1 - refer my four points above :confused:

Have a look at the attached where when you open the file Col. AM is blank. Then click any other tab in the workbook and then click Sheet1 again. You then see the Col. AM is populated. This will always happen whenever you click back into Sheet1 from another sheet.

Thanks,

Robert

Lenissa · Apr 26th 2018

HI Robert, The file you sent is working perfectly...Thais what am looking for too..

Pls note the below code is currently running in my original file Sheet1. Can you please help to advise me how can i insert the new code in the same sheet? thanks...

Code

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("S:S"), Target)
xOffsetColumn = 21


Dim WorkRng1 As Range
Dim Rng1 As Range
Dim xOffsetColumn1 As Integer
Set WorkRng1 = Intersect(Application.ActiveSheet.Range("AM:AM"), Target)
xOffsetColumn1 = 1


Dim WorkRng19 As Range
Dim Rng19 As Range
Dim xOffsetColumn19 As Integer
Set WorkRng19 = Intersect(Application.ActiveSheet.Range("K:K,O:O,AO:AO,AS:AS,AW:AW,BA:BA,BE:BE,BI:BI"), Target)
xOffsetColumn19 = 3


Dim WorkRng20 As Range
Dim Rng20 As Range
Dim xOffsetColumn20 As Integer
Set WorkRng20 = Intersect(Application.ActiveSheet.Range("K:K,O:O,AO:AO,AS:AS,AW:AW,BA:BA,BE:BE,BI:BI"), Target)
xOffsetColumn20 = 2


If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If


If Not WorkRng1 Is Nothing Then
Application.EnableEvents = False
For Each Rng1 In WorkRng1
If Not VBA.IsEmpty(Rng1.Value) Then
Rng1.Offset(0, xOffsetColumn1).Value = Now
Rng1.Offset(0, xOffsetColumn1).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng1.Offset(0, xOffsetColumn1).ClearContents
End If
Next
Application.EnableEvents = True
End If


If Not WorkRng19 Is Nothing Then
Application.EnableEvents = False
For Each Rng19 In WorkRng19
If Not VBA.IsEmpty(Rng19.Value) Then
Rng19.Offset(0, xOffsetColumn19).Value = Now
Rng19.Offset(0, xOffsetColumn19).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng19.Offset(0, xOffsetColumn19).ClearContents
End If
Next
Application.EnableEvents = True
End If


If Not WorkRng20 Is Nothing Then
Application.EnableEvents = False
For Each Rng20 In WorkRng20
If Not VBA.IsEmpty(Rng20.Value) Then
Rng20.Offset(0, xOffsetColumn20).Value = Environ("username") & "/" & Application.UserName


Else
Rng20.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If


End Sub

Display More

Please use the appropriate tags when posting code as I have done for you this time. Thanks.

Quote

Can you please help to advise me how can i insert the new code in the same sheet?

Just copy and paste my code above immediately beneath this code (except the line that says "Option Explicit" as that needs to be the very first line).

Thanks,

Robert

Lenissa · Apr 26th 2018

Hi Robert.

Finally I managed to correct it. The code was perfectly fine & the error is just bcz of the incorrect inserts from my side only. Thanks a lot for your help on this case.

Only one fine tune I required from your side,as I may needs to add some additional information in the same Sheet1 AM Cells. But with current method, Its not accepting any other texts on that cell. Appreciate if you find any way for this too, thanks...

**Trebor76** · Apr 26th 2018

Hi Lenissa,

Not too sure what you mean as yes, each time Sheet1 is selected Col. AM is cleared and reset with with data from the Formula tab so any existing in that column will be cleared.

Sorry but I'm signing out of this post as I feel your original request has been met. If not I'm sure another Ozgrid member maybe able to solve your last request.

Regards,

Robert

VBA for auto update cell

Participate now!

Share