logic test output to seperate cell

[ronniewb]

I am developing a sheet to do some debt analysis. I have an input cell for loan value (B1), interest rate (F1), term (months)(H1), and payment(J1). I also have a set of cells that will calculate each of these values individually, given the other 3 values (J4-7 respectively). What I am trying to accomplish is to be able to manually input the values in the input cells, or to have a logic test determine if one input cell is empty, and use the other 3 to calculate it and insert the calculated value. I can not put a formula directly in the input cell because a manual entry will overwrite the formula for future use (and I plan to protect the cells with formulas).

Ideas?

 

[p45cal]

A starting point for you. This set up:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Input row	1				2		333		45
2
3
4	Answers	111				222		333		444
Sheet

with this code in the code module of the same sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If [=COUNT(B1,F1,H1,J1)] = 3 Then
  For Each cll In Range("B1,F1,H1,J1")
    If IsEmpty(cll) Then cll.Value = cll.Offset(3).Value
  Next cll
End If
End Sub

 

[ronniewb]

Thank your for your reply. I have never worked with macros or VB, and may have bitten off more than I can chew. My sheet looks very similar to yours (in layout) except that my substitution values are in the row directly below the original inputs, and they are formulas, not text. Also I am trying to call the macro unknownvalues, and I'm sure that is messing something up.

 

[p45cal]

Thank your for your reply. I have never worked with macros or VB, and may have bitten off more than I can chew. My sheet looks very similar to yours (in layout) except that my substitution values are in the row directly below the original inputs,

A very small change to the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If [=COUNT(B1,F1,H1,J1)] = 3 Then
  For Each cll In Range("B1,F1,H1,J1")
    If IsEmpty(cll) Then cll.Value = cll.Offset([B][COLOR=Red]1[/COLOR][/B]).Value
  Next cll
End If
End Sub

You should place this as follows: on the sheet's tab, right-click, choose View Code..., paste the above where there's a flashing cursor. Close that newly appeared window, if it works then saving the workbook will save the macro.

and they are formulas, not text.

It matters not

Also I am trying to call the macro unknownvalues, and I'm sure that is messing something up.

What is your code?

 

[ronniewb]

OK, that's getting really close. Problem now is it won't let you leave a space blank while you input the other three, and its hard to tell which variable you are trying to manipulate. Is there a way to add a "calculate" button that will run the macro through one cycle, then stop?
WOW though, thanks.

 

[p45cal]

So delete more than one together first, then input. It will only trigger when there's a single blank left.
Otherwise:
A button assigned to the following macro:

Sub CalculateWhatsLeft()
If [=COUNT(B1,F1,H1,J1)] = 3 Then
  For Each cll In Range("B1,F1,H1,J1")
    If IsEmpty(cll) Then cll.Value = cll.Offset(1).Value
  Next cll
End If
End Sub

 

[ronniewb]

That worked, but with protection enabled you had to control to multi select. But I was able to add a form control button that did a wonderful job. Thank you so much for your help.