Capture of Variable results

[segran]

Hi All

Book6
	A	B	C	D
1	a	102
2	b	3.5
3	c	0.36
4	d	6
5
6	LOM	17.21
Sheet1

For an illustrative example above...

I wish to know, if its possible of capturing results, when input variable values are changed.

For instance, if I vary "a" from 0 to 200, and "b" from 0 to 10 separately, and i wish to see the result "LOM". I want to capture the resulting "LOM" onto another spreadsheet. Its difficult, to keep on copying and pasting "LOM" results.

Can you assist.

 

[lozzablake]

You could write a macro that runs every time one of the four values is changed and the value could then be written to a specific sheet. Do you know VBA?

 

[lozzablake]

This is an example. This code needs to go into the module associated with the sheet where the data is:
Press ALT+F11 to open the VBE
Double click on the sheet name in the explorer window
Paste the code in there.

Hope this does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IntLast As Integer
    If Not Application.Intersect(Target, Range("B1:B4")) Is Nothing Then
        With Sheets("results") 'replace "results" for your sheet name
            IntLast = .Cells(65536, 1).End(xlUp).Row + 1
            .Cells(IntLast, 1) = Range("B6")
        End With
    End If
End Sub

 

[segran]

Hi

I did exactly, what you said I should. However, I am encountering a problem. It asks me to save it as a micro, which I do. But then it opens a sub() form.

What must I do?
Thank you

 

[lozzablake]

I am not sure what you mean. You should not get a dialog box at all. what do you mean by a sub-form?

There should be no modules in the VBE.

 

[segran]

When I double click on sheet1, a window opens with "GENERAL" and "Declarations" on the top.
I copy and paste the code, change "results to "sheet2".
When I click to run, it changes to "Worksheet" and "change " at the top.
Must I save it as a macro?

 

[lozzablake]

you do not need to click to run it. This is an event macro. It runs automatically whenever you change one of the cells in B1:B4. There is no need to do anything else.

You test it by changing one of the values in the B1:B4 range. The macro will be saved when you save the workbook in the normal way.

 

[segran]

Hi

What changes must be made to above code, if I wish to record another result plus the initial one?

 

[lozzablake]

Add an extra line with the appropriate code above the End With