Hi,

I am trying to figure out how to do the following:

Assume you have a formula like sum (A1:A5) in cell A6.

What I need to do is add a sumproduct formula to the existing formula using vba.

For example, the formula in cell A6 will be updated to sum(A1:A5)/SUMPRODUCT((C4:C8=B1)*(D4:D8=B2))

Any ideas or suggestions.

Thanks,

Steve

I am trying to figure out how to do the following:

Assume you have a formula like sum (A1:A5) in cell A6.

What I need to do is add a sumproduct formula to the existing formula using vba.

For example, the formula in cell A6 will be updated to sum(A1:A5)/SUMPRODUCT((C4:C8=B1)*(D4:D8=B2))

Any ideas or suggestions.

Thanks,

Steve

- How to edit/change a formula in a cell using Excel VBA
- How do you make a formula in a cell using vba?
- Add number directly to existing number in a cell?
- Syntax to insert an array formula in a cell
- Apply Name to Existing Formula in a different Tab
- How to insert a complex formula in a cell with VBA
- How to insert a complex formula in a cell with VBA
- Insert formula in a cell with VBA with a changing range
- Run Macro From An "if" Formula In A Cell
- IF Function to test formula in a cell
- IF Function to test formula in a cell
- How to hide formula in a cell
- linking existing text in a cell to the web?
- How to continue the formula in a cell if row is inserted
- Showing and copying a formula in a cell
- Trying to 'write' a formula in a cell with a variable reference
- Showing and copying a formula in a cell
- Change formulas in a cell depending on value entered in a different cell
- Insert formula into cell using VBA
- Add a Range to an existing Range in a loop?
- Add new value input to existing value in cell
- Check an entered value in a cell in real time, and if the value already exists
- Refer to an array contained in a cell
- Multiple formulas in a cell?

Would like to ask how to edit a formula in a cell using vba code.

For example, the cell A1 in sheet "sheet1" contains formula "=sum(Sheet2!B1+Sheet3!B1)" and I would like to use VBA to edit/change it to "=sum(Sheet2!B1+Sheet3!B1+Sheet4!B1+Sheet5!B1)" and so on

Would anyone help ?

thanks in advance

However, the second " cuts off the first one. Does anyone know how to get around this?

First time here! Thank you for having this Forum!

I wanted to get your help in adding a number directly to an existing number in a cell. For example, in cell A1 I have the number 10. I want to add the number 5 to the existing 10 by typing it in the same cell (A1) without having to write a formula every time. So the cell A1 will now display 15.

I know I can add it in my head and then just type in the new number in the cell, but because the numbers will be large (for example, 3894 plus 5698), I wanted to know if it's possible to do this without having to type in the new numbers in another column or cell. Can this be done??? Thanks!

SheetData.Range("F4") = "=SUM(F7:F70)" inserts the formula.

However, the following does not work,

SheetData.Range("E4") = "{=SUM(--SUBSTITUTE(0 & E7:E70, " - ",""))}"

Any ideas. Thanks for all your help. mikeburg

Define Name > Apply Names > OK, pops up a window saying "Microsoft Excel cannot find any references to replace". How can I apply names to an existing formula in a different worksheet tab?

Thanks for your expertise,

pinebush

example : if formula is "=if(a3=2;a3;a2)"

i used

cells(2,3).Formula="=if(a3=2;a3;a2)"

bu i receive an error

Application object ....

if i used "=s4"

everything is ok.

example : if formula is "=if(a3=2;a3;a2)"

i used

cells(2,3).Formula="=if(a3=2;a3;a2)"

bu i receive an error

Application object ....

if i used "=s4"

everything is ok.

Could someone help me with the below script. The range is MyRange, however I cant seem to get the script to enter the formula in the cell using the range.

ActiveCell.Formula = "=Countif(" & MyRange & ",""=Above"")"

This is the problem line.

I get a type mismatch error at this point. The reason I'm using MyRange is because the range changes in size.

Here is a Code snipit: Thanks for helping, Mike

Sub Add_Percentage() Dim MyRange As Range Dim Lastrow As Long On Error GoTo 0 Lastrow = Range("I2").End(xlDown).Row Set MyRange = Range("I2:I" & Lastrow&) Stop Range("E2").End(xlDown).Offset(7, 0).Select ActiveCell.Formula = "=Countif(" & MyRange & ",""=Above"")"

eg.

VB:J14,"[COLOR="Green"]frmPanic.show vbmodeless[/COLOR]",0)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

there any way to get the IF() function (or some other function) to

test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact

true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when

a formula has been modified by some spreadsheet editing process and I

don't want the formula to change. Even "absolute" references seem to

change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1

Reference Style (because the formulae I'm interested in are literally,

character for character, identical in R1C1 reference style, and it's

easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a

question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes

there any way to get the IF() function (or some other function) to

test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact

true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when

a formula has been modified by some spreadsheet editing process and I

don't want the formula to change. Even "absolute" references seem to

change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1

Reference Style (because the formulae I'm interested in are literally,

character for character, identical in R1C1 reference style, and it's

easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a

question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes

The purpose is to hide the formula in a specific cell to prevent how to get and arrive the value

It is possible to hide the formula in a cell but still have a result value

I realize that excel might not be the correct program to complete this database on, if it cannot allow such links (which is understandable, it probably wasn't intended as such). If it is not possible, can anyone recommend a similar program which will do the task?

Thanks so much in advance.

formulae in different cells to calculate values.

I have created a new sheet which includes a description of what the

cell does, but would like to include a sample of the formula.

Is there anyway to get the formulae in a cell instead of the value, and

then be able to copy that formula across as text into another sheet?

Thanks for your help.

Love

Amy xx

--

AmyTaylor

------------------------------------------------------------------------

AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970

View this thread: http://www.excelforum.com/showthread...hreadid=517743

I'm trying (and failing!) to write a formula into a cell using vba which has a variable address;

So far I have tried all sorts of variants on the theme;

Worksheets("subs").Range("u" & insertrows + 13).Formula = "=t" & 12 + insertrows & "/ "l" & 11 + insertrows" (just a snippet of code...)

ie if insertrows = 10, i need to get the following formula into cell U23

=t22 / l21

any help appreciated before I go completely bald!!

Thanks.

I have created a new sheet which includes a description of what the cell does, but would like to include a sample of the formula.

Is there anyway to get the formulae in a cell instead of the value, and then be able to copy that formula across as text into another sheet?

Thanks for your help.

Love

Amy xx

I need some help, hoping some of you can help me.

I am trying to figure out how to have the formula in a cell change to a different

formula based on another cell.

For example, if b2=9, I want to have AK5=SUM(I5+J5)/SUM(B5+C5)

if b2=10, AK5=SUM(I5+J5+K5)/SUM(B5+C5+D5)

b2 can = any number from 1-31, so I will have 31 possible formulas for cell AK5

I know there has to be a way to do this, and I've been trying to figure it out for hours, I'm ready to tear my hair out!!

=IF(A5="N/A",,A5)+IF("A6"=N/A,,A6)

I know how to place a formula in a cell using vba, but this formula doesn't work because there are quotation marks indicating text. Here's my attempt at vba code:

VB:Anyone have any ideas?Worksheets("sheet1").Cells(1, 1) = "=IF(A5="N/A,,A5)+IF(A6="N/A",,A6)"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

this in a loop?

I want to search for cells containing a specific string, then append all of

the cells in those rows to a Range object, the select the resulting Range.

Sub FindAndSelect()

Dim foundRows As Range

Dim cell As Range

Dim lnLastRow As Long

Dim lnLastCol As Long

Dim target As String

Dim lastCellAddr As String

' Get the address of the last cell containing data

Range("A1").Select

On Error Resume Next

lnLastRow = cells.Find("*", Range("A1"), xlFormulas, , xlByRows,

xlPrevious).Row

lnLastCol = cells.Find("*", Range("A1"), xlFormulas, , xlByColumns,

xlPrevious).Column

lastCellAddr = cells(lnLastRow, lnLastCol).Address

' Get the string to search for

target = InputBox("Enter search string")

If target = "" Then

Exit Sub

End If

' Find all rows containing the search string and add them to the selection

With Worksheets(1).Range("a1:" + lastCellAddr)

Set cell = .Find(target, LookIn:=xlValues, Lookat:=xlPart)

If Not cell Is Nothing Then

firstAddress = cell.Address

Do

' <<< THIS IS WHERE I NEED HELP >>>

' Append all of the cells in cell.EntireRow to the foundRows Range

' foundRows = foundRows + cell.EntireRow

Set foundRows = Union(foundRows, cell.EntireRow)

Set cell = .FindNext(cell)

Loop While cell.Address <> firstAddress

End If

End With

' Select all of the rows that contained the search string

foundRows.Select

End Sub

I want to add a new value input to an existing value in a cell, or range of cells. Example A1 contains the value 5, when select that cell and enter 6 I want the new value to be 11.

The sheet I'm using requires this single input for a range say A1:A8. The input would cell by cell, but whatever the input is, is added to the existing value

Ive searched the forum for an answer as I thought this would have been asked before, but couldnt find anything.

Ideas anybody

I have a workbook with multiple worksheets that hold customer data. Each list is rather large, so i need a way to control data entry with a view to avoiding data duplication.

Is there a way (perhaps via VB scripting) that I can have Excel check an entered value in a cell in real time, and if the value already exists elsewhere in the workbook, a message is displayed?

Million thanks for any feedback.

de049

Cells B1 through B5 contain numbers 1,2,3,4,5

I can use a formula directly and its works--cell D2: Match(3, offset(B1,0,0,5,1))

it gives me the correct answer which is 3.

However if I create an array first so that the formula are less complicated: Cell D1: {=Offset(B1,0,0,5,1)} and then in D2: Match(3, D1) it gives me 1.

Could you tell why?

Thanks

=C6*'MONTHLY RATE MEDICAL PLANS'!C7*'# EE''S BY OFFICE-PLAN-CLASS'!E8

where c6 will equal an inputted number (a percent) and c7 is an inputted number as well. E8 is an inputted number as well.

So for example the sheet will go like this. They want 15% of a number and it multiplies by 200 which gives a number 30. I need to make this so that they can enter in either 15% or they can enter in a number say 15.

If they enter in 30 it will divide by the number that was inputted in c7 which say is 200. Then the number in the cell with the equation will give 15%. How can i use the backward engineering to get the equation to take a number say:

If (entered a %) it would multiply c7 and multiply by e8, or if they (entered a number) it would divide by c7 and give a % as an answer.