VBA to clear contents or get data from another cell

[Kevin_W]

I've done some searching and can find ways to clear data from a cell but I need another variant.

As an example:

If Sheet1 A1 is 0 or blank, clear cell contents on Sheet2 A5.
If Sheet1 A1 is greater than 0 then get cell contents from Sheet1 A2 and put it in Sheet2 A5.

I can manually enter cell pointers into sheet2 A5 but I'd rather have something more automated. Using a IF statement works if the A1 cell is greater than 0, but when it is 0 or blank the "" statement messed up other formulas. If that makes sense.

Any help would be much appreciated.

 

[Joe4]

Alternative 1:
Yes, something could be automated, via VBA.
But we would need to know what the "triggering" event for this would be.
Do you want it to run when Sheet1 A1 is updated?
How is this cell being updated (manually or by a formula)?

Alternative 2:
Continue to use an IF statement, by modify your "other" formulas that are messed up by "" (it may be a simple adjustment, if we can see that those "other" formulas look like).

 

[Kevin_W]

Cell A1 is a manual input. Cell A2 is a formula. Everything on Sheet2 is pretty much protected and will not be unprotected by the file owner. I have just created a separate Sheet1 that feeds into Sheet2 to help get to my final solution. The if statement I think is out of the question. It's a file that is used to calculate performance of fiber optic links. There are quite a few inputs that I care for in Sheet1 that feed into sheet2 and they all work fine. It's just this one cell that is giving me fits. Sure I can manually deal with it but there have been a few time that I have overlooked the cell and it causes issues with the end result.

Was just looking for a cleaner solution.

 

[Joe4]

This can be done automatically with a a little VBA.

Go to Sheet1, and right-click on the Sheet tab name on the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
'   See if updated cell falls in watched range
    Set rng = Intersect(Target, Range("A1"))
    
'   Exit if A1 is not updated cell
    If rng Is Nothing Then Exit Sub

    If Range("A1") = "" Or Range("A1") = 0 Then
        Sheets("Sheet2").Range("A5").ClearContents
    Else
        If Range("A1") > 0 Then
            Sheets("Sheet2").Range("A5") = Range("A2")
        End If
    End If
            
End Sub

Now try it out, changing the values of A1 on Sheet1 and watch what happens to cell A5 on Sheet2.
It should do what you want.

 

[Kevin_W]

Thank You very much. This does work with one caveat (always right?) To try and provide some additional details, my initial cell references provided relate to a 2nd amplifier gain and input settings which is past an initial amplifier. The first initial amplifier is generally always used in a design. The second amplifier may or may not be needed. My A5 reference is for the amplifier 2 input. If it doesn't exist I need to clear the input number from the second sheet. So the VBA code you provided works as advertised. The caveat is that when you change the gain setting of the first amplifier which in turn changes the input to the second amplifier, that A5 cell on the sheet2 tab does not update unless I manually enter a new gain number for the second amplifier.

I'm going to see what I can come up with on my own but I wanted to respond with my appreciation for your solution you gave me!

 

[Joe4]

If you can lay out all the rules as it applies to cells on the sheet, I can probably update the code to do what you want (not really sure where all these "amplifier inputs" exist).
Probably the best way to do that is to cover all the different situations.

For example:
If cell A1 on Sheet1 changes, and cell S2 on Sheet1 is blank, then update cell A5 on Sheet2 to the value from cell A1 on Sheet1.

That is just an example. But if you can do something like that for all the different scenarios that you want handle differently, then I will know what I need to code to.

 

[Kevin_W]

So I think I found a solution with the following addition marked in red below. Seems to work!

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
'   See if updated cell falls in watched range
    Set rng = Intersect(Target, Range("Q18,[COLOR=#ff0000][B]M18[/B][/COLOR]"))
    
'   Exit if Q18 is not updated cell
    If rng Is Nothing Then Exit Sub

    If Range("Q18") = "" Or Range("Q18") = 0 Then
        Sheets("Sheet2").Range("C27").ClearContents
    Else
        If Range("Q18") > 0 Then
            Sheets("Sheet2").Range("C27") = Range("B39")
        End If
    End If
            
End Sub