Capture of Variable results

segran

Active Member
Joined
Aug 20, 2004
Messages
335
Hi All
Book6
ABCD
1a102
2b3.5
3c0.36
4d6
5
6LOM17.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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
Code:
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

Active Member
Joined
Aug 20, 2004
Messages
335
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

Well-known Member
Joined
Dec 15, 2005
Messages
818

ADVERTISEMENT

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

Active Member
Joined
Aug 20, 2004
Messages
335
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

Well-known Member
Joined
Dec 15, 2005
Messages
818

ADVERTISEMENT

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

Active Member
Joined
Aug 20, 2004
Messages
335
Hi

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

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top