Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Add to an Existing formula in a Cell using VBA

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


Post your answer or comment

comments powered by Disqus
Hi,

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

How do you put the above formula into a cell using vba? Below is what I've come up with.

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

Hello,

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!

What would be the proper syntax to have VBA to insert an array formula in a cell? For example:

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

I have received an Excel workbook that contains dozens of worksheet tabs, including thousands of formulas. Sheet1 is the 'master' price list, with hundreds of items, along with the corresponding cost of each item. Sheet2, Sheet3 (and so on) contain formulas which reference back to the prices in Sheet1. However, the creator of this workbook did not use named ranges, and I need to name the individual cells (in Sheet1) which contain the prices. I have named the cells in Sheet1, using the 'Workbook' scope, but am unsuccessful at applying the names to other sheets.
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

How to insert a formula in a cell with VBA
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.

How to insert a formula in a cell with VBA
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.

Hi,

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"")"


Is it possible to call a Userform from an "IF" statement in a cell?
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


The IF() function is generally used to test the value in a cell. Is
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 IF() function is generally used to test the value in a cell. Is
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

I have a very confidential data that I cannot share to other members

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 am looking for a way to take existing text in a cell and create a hyperlink with it. I have created a large spreadsheet with donors names and I now would like to go back and highlight the existing text and have those names then link to the corresponding online profiles. This seems simple but it just doesn't seem possible. I have tried left clicking, insert, and all the other obvious ways you should be able to do it, but to no avail.

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.

I have a form that accepts new employee and automatically insert it in a particular sheet (meaning a new row is added in the middle or in the last part of the data). My problem is whenever a new row is inserted the formula in a cell is broken or the formula is not appearing in the new inserted row. How can i solve this. thanks

Hi everyone, I have a spreadsheet which includes a number of complex
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

Hi,

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.

Hi everyone, I have a spreadsheet which includes a number of complex 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

Hello,
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!!

I am trying to use a macro to enter in the following formula into a cell:

=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:
	
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
Anyone have any ideas?

How can I add a range of cells (a whole row) to an existing range, and do
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

Hi
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

Hi all,

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

I have been trying to refer to an array in a cell, best illustrate in an example.

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

I have an excel spreadsheet i am working on. I have multiple sheets with multiple things on them. I have a formula in a cell that is
=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.


No luck finding an answer? You could always try Google.