Possible To Control Cell Range With Another Cell As Variable - Then Use Solver?

Dec 17, 2013

I have a HUGE dataset (more than 1 million rows). I'm trying to find the optimum cell range size - 10 rows, 100 rows, 682 rows, whatever - for a SUM formula. (The formula would be in every row, looking down at the next n rows).

Is it possible to control the SUM cell range specified in a formula in cell (X) with a value in cell (Y)? In other words, the SUM formula in cell (X) would somehow refer to cell (Y) to determine the SUM cell range.

Ideally, I would like to then use Solver to find the optimal value for cell (Y).

Is this possible with just formulas? Or is VBA required?

View 5 Replies


ADVERTISEMENT

Set Range Variable To RefEdit Control

Dec 3, 2007

I'm trying to set up a userform with a refEdit box that allows the user to select a cell range. I then need to assign that cell range to a range variable so the macro can use it. I'm having a problem reconciling the assigned string variable that refEdit returns and the range variable that I need.

So what I'm trying to say is that I can assign the range fine through the Userform, and I can see through the debugging screen that mySampleVariable (dim as a variant) holds a range value of 'Access Dump'!$A$8 (the correct range), but when I try to

Dim myRange As Range
Set myRange = mySampleVariable

The code bugs out because it apparently can't assign a string value to a range variable.

View 4 Replies View Related

Control The Sum Range With A Cell

Sep 12, 2007

I would like to define the range of cells in the sum with a number in another cell. Say I want to sum the past 10 years, I would like the sum definition to be controlled by another cell that includes the number 10.

If I want B1 to be the sum of A1:A10, B2 to be the sum of A2:A11, etc., I would like to be able to control the range of the sum with say, C1, which in this case would contain the number 10.

This would allow me to easily change the range of data I am summing: if I changed C2 to 15, B2 would give me the sum of A1:A15 for example.

View 9 Replies View Related

Select Range In Worksheet Where Last Cell In Range Is Variable?

Jan 27, 2012

I am trying to write code to select a range in a worksheet where the last cell in the range is variable.

Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range

[Code].....

View 8 Replies View Related

Select Next Control / Cell On Worksheet After Enter In Control

Jan 9, 2008

Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?

View 9 Replies View Related

Solver In VBA Without Cell References?

Aug 4, 2009

My macro uses the solver 100 times. Everything is working well, but it's a little bit slow because of all the cell references.

I was wondering if it's possible to use the solver with variables without cell references?

View 4 Replies View Related

Set Range Variable To Last Used Cell In Column

May 9, 2008

how (if possible) to activate the link between the Excel spreadsheet and the VBA editor, so when I cut/paste row, columns and cells in my spreadsheet, my VBA code is automatically updated.

I have a rather large excel tool combined with a significant amount of VB code, and each time I insert/remove a line, I have to re-type the cell references,

View 6 Replies View Related

Solver Change Cell Precision

May 17, 2008

I want to have a restriction that the cell that is to be changed (not the target cell) must have a certain precision eg 0.1 or 0.01 cause i wouldnt want a solution where it changes to 73,67,I would rather it changed to 73,70

View 9 Replies View Related

Select Row Between Cell Range Row Determined By Variable

Jan 28, 2014

I have a row stored in the variable Found_Row3 and I want to set the variable ACRow to all colomns from A to Z of that row. Below is what I have so far...not working

[Code] .....

View 7 Replies View Related

Set Variable To Named Range Or Single Cell

Nov 30, 2008

The problem exists with the lists I use. I have a "settings" sheet, where users can choose from preset values to run in the program. E.g. "Urgent", "Semi Urgent" and "Non Urgent" (there are a lot more values, but those will do for now." and the idea is to run them 1 at a time.

However, I've now determined that 99% of the time, they're going to be run 1 after another, that is to say run the program once for "Urgent", then again for "Semi Urgent" and then again for "Non Urgent". So I'd like to loop through these. Again, not a problem, you'd think.

So I added a new value to the list; "All" which users can select to invoke a list. Again, not a problem, I already have the list as a named range, "DmgList" and I can cycle through it. I ran the following test code to see if it would work in theory...

View 4 Replies View Related

Define A Cell Range From Variable Values?

Jul 21, 2009

I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?

View 5 Replies View Related

Copy Range In Macro Using Variable Cell

Oct 12, 2011

I wish to copy a range which has the variable adderss in a cell.

Example the address is located in D2 and the address in that cell is B4:R113

I want to select the information in D2 for copying.

View 4 Replies View Related

Variable Range Formula Based On Cell

Oct 8, 2007

Here is what I would like to do: Have a formula like Max(B12:B14) change to Max(B12:B18) when I change the value in different cell from 3 to 7. I would like to stay away from the macro world and keep in formula world if that is possible.

View 6 Replies View Related

Create A Range Variable From Cell Text

Oct 26, 2007

Let's say I have cell A1 on Sheet 1 of Book 1. I need this cell to be a range variable for a macro in Book Two, but cell A1 varies from time to time (A1 this time, A7 next time).

In Book Two, I write "A1" as the contents of cell C3, for example. How do I turn that string into a range variable for use, not the containing cell? Since the cell needs to manually changed each time I run the macro, having it as a text field somewhere on my Book 2 Sheet seems the only simple way to identify it but IS there another way? Selecting it as the active cell and using another subroutine defeats the use of the macro.

View 4 Replies View Related

Clear Cell In Variable Dynamic Range

Feb 21, 2008

In my macro, NextRow is defined as the first empty row in a set of data (NextRow = Range("A1000").End(xlUp).Row + 1). How would I clear the contents of Columns A:H in NextRow in VBA, keeping in mind that NextRow is always different for each worksheet I'm working with?

View 2 Replies View Related

Multiple Cell Values Into A Variable - Then Place In Another Range?

May 17, 2014

how to set a cells value into a variable, using .value, then set another cells value equal to that variable without using copy/paste

What I can't figure out is how to see the value of multiple cells to a variable and place them into another range of the same size using .value. It would be nice to free up the clipboard.

View 2 Replies View Related

Select Range Based On Variable Cell Content

Apr 30, 2009

Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.

View 2 Replies View Related

Copy Single Cell Into Variable Range Of Cells

Dec 3, 2013

I would like to copy a cell in v2 to from v3 to end of column v where there are values in column u.

View 4 Replies View Related

AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS

Mar 23, 2009

I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE

Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub

View 3 Replies View Related

VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints

Mar 18, 2009

I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.

Here is the ....

View 3 Replies View Related

Solver Without Range As Input

Dec 30, 2006

i have some data in array format and want to use that data in the solver. However, as per my understanding solver takes in only ranges. How can i use arrays directly? this is wht i am trying, but not working

entities = 1
spread = 3 + entities
ans = 5
SolverOk SetCell:=spread, MaxMinVal:="3", ValueOf:=ans, ByChange:=entities

View 5 Replies View Related

How To Give Range In Constrain Solver

Sep 3, 2009

FromToTriggerrange26Stop-lossrange25Combination triggerlevelie: 4Combination stop levelie: 5Profit $10

How can I make "Solver" run a check on which combination of trigger and stoploss that gives the highest profit? The range is in two different cells, but I can't get it working. I guess that it's my lack of skills, and not "Solver" that isn't complex enough.

View 9 Replies View Related

Multiple Solver Constraints In Solver

Sep 10, 2006

I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.

What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.

I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?

View 9 Replies View Related

Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents

Oct 15, 2008

I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.

Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.

View 5 Replies View Related

Invalid Next Control Variable Reference?

May 9, 2014

Why this doesn't work?

I get the error message: Invalid Next control variable reference

And it highlights the i in 'Next i'

[Code] .....

View 3 Replies View Related

Value For Variable Based Off Control Checkbox.

Jan 26, 2007

I have several checkboxs in an excel sheet, that if one is checked i would like it to make a change to a variable in a macro that will run when saving a file.

for an example:

if checked:
Checkbox1 = NCE1
Checkbox2 = NCE2
Checkbox3 = NCE3
Checkbox4 = NCE4
Checkbox5 = NCE5

I have a bit of code that is like this....

ActiveWorkbook.SaveAs ("\marketing2PartageNonconforms" & sF1 & " - CO" & sF2 & " - FC" & sF3 & ".xls")
I would like sF1 to change depending on which checkbox is checked. so if checkbox1 is checked, than sF1 = NCE1 .

View 9 Replies View Related

Set Variable To Forms Control & Get Value Property

Mar 22, 2008

How do I reference a spin button from a variable that I set.

e.g

Dim objSpn As Object
Dim y As Integer

Set objSpn = ActiveSheet.Shapes("spnWCDate")

y = objSpn.Value

This doesn't work, but hope it explains what I want. It's so I dont have to write the whole object name each time I reference it.

ADDED: I've just noticed that this doesn't work even i do reference the object with the full name. I copied this from a forms spin control that I was using, why can't i store the value of the spin button in a variable?

View 4 Replies View Related

Finding A Cell In A Variable Location Based On The Contents Of The Cell

May 2, 2012

I am creating a macro to automate data analysis for work, but I've become stuck.

I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.

View 1 Replies View Related

Center Image In The Middle Of Cell Where Cell Size Is Variable

Apr 12, 2013

The idea is to center an image in the middle of a cell where the cell's size is variable. This shall be done for a column of images if a certain cell in the same row contains content different from 0. If not the image shall be invisible.

Sub Center()Dim Position As Integer
Dim Picture As Integer
Picture = 6
For Position = 7 To 320If Sheets("List of Measures").Cells(Position, 2).Value

[Code] ......

Run-time error 1004: Application-defined or object-defined error?

View 7 Replies View Related

Access A Cell's Contents Via A Variable Containing Cell Address??

Mar 16, 2009

How can you access a cell's value via a vb variable that contains the cell's address. I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this?

e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress??? Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved