Adjust Cell Value To Get Constant Value For Another Cell

Jul 31, 2014

I have cell C5 value set as 5*B5+3. Now, using vba,I would like to adjust the value of B5 (basically find it), so that the value of C5 is let's say 8.

In this easy example, one can find rapidly the correct value of B5 to be 1.

What I've tried is the following code:

Private Sub CommandButton1_Click()

Range("B5").Value = -5
Do
Range("B5").Value = Range("B5").Value + 1
Loop Until Range("C5").Value = 8
End Sub

So, I am setting the initial guess for cell B5 and then increasing it by 1. However, if I set the increment to 0.5 instead of 1, program goes crazy and loop doesn't close.

What changes should I introduce to this code, so that even if the increment is 0.001 everything will work.

View 3 Replies


ADVERTISEMENT

Subtracting Various Cells From One Constant Cell?

May 15, 2014

Say I have the number 325 in A1. Each time I type a number in A2, B2, C2, D2, E2, A3, B3, etc... I would like the number (325) in A1 to be reduced.

Also, how do I create a pull down list?

View 6 Replies View Related

Array Constant From Cell Values

Aug 26, 2008

I need to convert a cell that has a list of values A1 = 1,2,3. To a range expression {1;2;3}. I want to do this with a formula and not VBA. Example: Cell D1 has the formula = SUMPRODUCT(COUNTIF(C30:C31,A30:A32),B30:B32). I want to put the list range from column C into one cell.

ABCD
110140
2203
3305
450
560

View 4 Replies View Related

Detect If Cell Contains A Formula Or Constant

Dec 4, 2007

I need an equation that lets me determine whether a cell contains a static constant value (either a string or a number), or an equation that generates a value.

Problem: I have a cell that contains a default equation that generates a default value, depending on a few conditions. The user can overwrite this cell with a constant. In another cell I need to know whether that cell is still the default equation or a new constant value.

I cannot simply test the value of the contents to see if they match my default, because the user may choose to enter that value.

View 9 Replies View Related

Forcing Round Cell To Be Constant Number?

Dec 3, 2013

the way my spreadsheet's set up so far is that each employee should have a goal of, say, 100 for how many people they need to sign up. But they each have specific geographic breakdowns in their turf, some of which might be bigger or smaller than others, but at the end should all add up to 100 for each employee.

It's not super hard to do since I just take a goal of 100, in this example, and multiply it by the percent of population of the area in their overall assignment and give them a goal for that area based on that. So if Philadelphia has 70% of their total population, Upper Darby has 20% of their total population, and Phoenixville has 10% of their total population -- the goals would come out like this:

Philadelphia, Goal: 70
Upper Darby, Goal: 20
Phoenixville, Goal: 10
Ryan, Total Goal: 100

For some of the employees, that works out perfectly -- but based on the population sizes, the rounding sometimes gets a little off and it could end up like this:

Philadelphia, Goal: 71
Upper Darby, Goal: 19
Phoenixville, Goal: 11
Ryan, Total Goal: 101

For most employees, it ends up being dead-on 100 -- but there are some whose totals are at 98 or 99 or 101, and I was wondering if there was a way to force it to shave a point off or add a point on somewhere, pending on how close the decimal was when it rounded to make sure that it always ends up exactly on 100. (I'm using 'Data >> Subtotals' to get what's being represented above as "Ryan, Total Goal.")

View 10 Replies View Related

Constant In Formula To Change Based On Cell Value

Jan 3, 2008

I am creating on the fly an array (2 columns) in which the one row cell is mathematically derived from its neighbor (A left-hand cell). However, when the mathematically derived product exceeds a value, the row neighbor changes to another constant until its neighbor's product exceeds another value, etc. Can I create such an array without resorting to VBA?

View 9 Replies View Related

Use Cell Value As Constant (Const) In Macro Code

Jun 25, 2008

I'm trying to set named range as a constant in VBA, to allow me to recall the same range as an output target in different subs, without having to 'set' it in each module. I have tried to declare it at the top of a module like this...

Public Const tpnb_range = Worksheets(" SQL").Range("tpnbs")

and get a "Compile Error : Constant expression required"

View 3 Replies View Related

Determine Whether Cell Is Formula Or Direct Input (constant)

Jan 28, 2013

I have a template with formulas calculating a default value, but still allowing the user to override the cells with direct input.

I want to use conditional formatting to highlight any cells that have been overwritten, but can't find a way for Excel to differentiate between a cell with a formula or an inputted constant.

I realize there is a VBA "isFormula" function, but I don't want to have to use VBA for this.

View 7 Replies View Related

Using A Value To Adjust A Cell Reference

Oct 18, 2008

I have a spreadsheet set up for my son to roll up D&D characters. There's a combo box to select Race and another for Class of character. What I now need to do is automatically insert the correct set of racial and class bonuses into the relevant squares.

The Combo box points to a list of Races on Sheet 0, in cells A3 to A12. The cell reference for this returns a value between 1 and 10 depending on race selected, i.e. 'Dragonborn' yields a 1, and 'Tiefling' yields a 10. Cells H3 to H12 contain the Strength modifier for the different races.

When I select for example a Dragonborn (1) in the combo box, I want the Strength Modifier cell to pick up the value from cell H3, and if i change the selection to Tiefling (10) I want the strength modifier cell to pick up from (obviously) cell H12

How do I use a variable in the cell reference to adjust the cell data is retrieved from?

View 4 Replies View Related

Adjust Macro Tocompare To Cell Above

Jul 22, 2008

I have been the code below in many a different form since royUK first helped me find a certain value and delete the entire row. I've used it to find values, text, multiple conditions and so on and so forth, but I cannot figure out how to compare the cell to the cell directly above.

View 10 Replies View Related

Adjust Cell Height To Fit Text

Jun 1, 2007

way to get Excel to automatically expand the cell height when the text becomes to long to display. Is there something I am missing that will accomplish this, or do I need to write some VBA that will make the cell larger once the character limit that will fit on one row is exceeded?

View 7 Replies View Related

Constant Variables - Declare Name In Separate UserForm As Constant

May 8, 2012

I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.

Code for Form2: Const myName As String = Form1.txtName.Value

View 5 Replies View Related

Copy And Paste Without Having To Adjust Cell Size

Apr 16, 2013

I frequently have to copy information from one excel sheet and paste it into another. But when I do this the formatting doesn't carry over. So I have to go to each individual cell that I pasted into and adjust the size of it. How do I copy and paste so that the formatting/cell size is preserved?

View 4 Replies View Related

Formula To Adjust Based On Target Cell

Mar 11, 2007

I have a spreadsheet at work . I am tracking the quality target for the department and I need to create a formula that adjust accordingly to the target set for month end which is 90%.

I have build in the foreacasted numbers for the whole month but I need the formula to indicate the minimum error points needed to achieve the target of 90% for month end when I replace the forecasted numbers on a daily basis with actual numbers.

I have attached the spreadsheet which will be clearer.

View 9 Replies View Related

Formula To Determine If A Cell Has A Formula Or Constant

Nov 17, 2006

In cell A1 exists data. In cell A2, I would like to test A1 to determine if A1 is a formula or a constant.

Examples:
=A9+3 would result "formula"
="1+3 would result "formula"
3 would result "constant"
'Blahblah would result "constant".

View 9 Replies View Related

Adjust Code From Cell Range Trigger To Button Trigger

Jul 1, 2014

I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.

I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.

Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.

View 2 Replies View Related

Concatenate Two Text Fields BUT Left Adjust First Field And Right Adjust Second Field

Jun 22, 2012

I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.

A1 = "John Williams", A2= "Single"

A3 = "John Williams Single"

View 1 Replies View Related

Constant Array In Vba?

Jul 24, 2009

Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:

View 2 Replies View Related

If And Constant Logical

Jun 17, 2008

i have problem with a long code.

So I divided that code in 4 parts.

So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()

I have declared


Option Explicit

Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer

all my sub have the following structure:

View 9 Replies View Related

Declare Constant

May 15, 2007

what does the symbol # means in VBA? (but I couldn't put the # in the subject of my message )

I'm trying to understand someone's code... at some point he wrote:
sum_LU_Area = 0#

I thought that the # was used to declare a constant but I'm not too sure because in his code earlier he declared
Public sum_LU_Area As Double

Beside, sum_LU_Area is calculated somewhere further in the program.

View 7 Replies View Related

Excel 2003 :: How To Create / Run Cell A Equals Cell B Put Cell C Info In Cell D

Jan 26, 2006

I need to match data in cell A to cell B and then if they equal I need to copy the adjacent cell C to cell X . How do I set up a macro to do this automatically? I have over 5000 cells to compare and match up.. I have Office 2003.

View 7 Replies View Related

Increment One Column But Keep The Other Constant

Jun 28, 2009

Here is what I try to do:
At
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
....
An = Power(Cn, B1)

How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?

View 2 Replies View Related

Copy Formula With 1 Constant Value?

Oct 6, 2011

If I have a formula that I want to copy throughout a column, but I want to keep one of the parts of the formula constant, is there a short way to do this?

For example:

First cell is A1*O24
Second Cell is A2*O24
Third Cell is A3*O24

Right now when I try to copy the formulas, the second cell always reverts to A2*O25, which is not correct.

View 2 Replies View Related

Using Variable Instead Of Constant In VBA Formula

Jan 8, 2012

I try to adjust chart range using variable value instead of constant value.

My current code :
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = "='Check_2G'!$Z$2:$Z$32"

I need to make range ( "='Check_2G'!$Z$2:$Z$32" ) adjestable with varible like m instead of 32 in previous code.

View 2 Replies View Related

Macro Is Counting A Constant Twice?

Apr 25, 2012

For a single column of values, the following macro counts the first value twice, how can i fix this? for example: A1 thru A5 are a,a,b,c,d the result is

a 2
a 2
b 1
c 1
d 1

and should be
a 2
b 1
c 1
d 1

Macro:

Sub CountOfEachItem()
Dim ListRange As Range
Dim NewList As Range

[Code].....

View 1 Replies View Related

Excel VBA - Using Constant For A Set Of Values

Oct 19, 2012

Code:
Const Books1 = Array("5638", "5639", "10984")
Const Books2 = Array("3140", "7509", "3050", "7549", "7508", "7032", "6770", "6773", "4388", "6460")

I want to set an array as above for multiple use in my code (Used for auto-filtering various data with the same criteria).

The above is not allowed it seems, is there an alternative method so that if my Constant changes I only need to amend this once.

View 6 Replies View Related

Extend A Constant Array

Jul 30, 2008

I've never had to venture into Arrays as there was no need.

I'm now faced with Arrays.

Generally I'm able to figure things out, in this case I'm stumped.

What I have is a Constant Array, that output the amount of Rows the Array displayed in.

I know the information is there, what I don't know is how to extend the rows.

ie it Outputs 5 Rows right now, with a top row header.

What I want is 10 rows with a header.

View 9 Replies View Related

Create A Constant Variable

Apr 14, 2009

I want to basically set a varible that can be called back into a multiple of documents I will try and explain this as best as I can but it is a fortnightly pay date... i.e. 22/04/2009 Wednesday... as the payroll is fortnightly I want to be able to use the current date or todays date in a future proof macro.

i.e. if todays date is the 14/04/09 then the next pay date has to be the 22/04/09, if this was run in two weeks it would be 06/05/2009.

I was thinking a long the lines of lock in one start date... 14/01/09, then adding increments of 14 days until todays date - variable + 14 * r = "-"

View 9 Replies View Related

One Constant To Multiple Cells

Jun 4, 2009

I am just wondering if this can be possible:

this is an table with a lot of cells

12 36 89 56 97 78 98 36
36 89 56 97 78 98 36 12
97 78 98 36 56 97 78 98
12 36 89 56 97 etc ..
etc...

to become like this ...

View 9 Replies View Related

Refer To Named Constant In VBA

Oct 1, 2007

I created a name in Excel 2002, Name = "NameToUse", Refers to = "Bill".
How do I get the value in VBA code?

Range("NameToUse").value ' doesnt work
NameToUse ' doesn't work

View 3 Replies View Related







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