Add Formula To Cell To Equal Product Of 2 Other Cells Via Macro Code

Apr 18, 2008

I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors

Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"

When I record a macro it gives me this, but again, I need the last cell in terms of "T"

ActiveCell.FormulaR1C1 = "=Calcs!RC[-7]*Calcs!RC[-7]"

Macro Code To Delete Entire Row If Cell In Column Is Equal To Value

May 7, 2014

I need an easy code that searches all of column A and deletes the entire row if the cell has the value "-". It needs to find the last row of data using something like LastRow = Range("A" & Rows.Count).End(xlUp).Row

VB Code For An Array That Is The Product Of Another Macro

Feb 5, 2009

I've gotten were Cell formulas.

I need this for VB code, for an array that is the product of another macro hence it's dynamic.

B 3
E 2
E 8
D 2
B 1
B 7
How do I subtotal Col A,B according to Col A?
Total 3 B entries with a subtotal of 11
Total 2 E entries with a subtotal of 10
Total 1 D entry with a subtotal of 2

Conditional PRODUCT Function Formula (return The Product Of A List Of Numbers In A Column)

Sep 30, 2009

I am trying to use a PRODUCT formula to return the product of a list of numbers in a column, between 2 specified dates. The spreadsheet is arranged as follows:

Column BA list of dates
Column C & DNumerical data not used in any calculations
Column EThe numbers to multiply together
Cell A1Start date to be used in the PRODUCTformula
Cell A2End date to be used in the PRODUCT formula

The formula I have to multiply the data in column E, subject to the start and end dates in Column A is:

{=PRODUCT(IF(B15:B1000>A1,IF(B15:B1000<=A2,INDEX(B15:E1000,0,4))))}

This returns zero though works appropriately if I replace �PRODUCT� with a �SUM�.

Macro To Print Number Of Copies Equal To Output Of Formula In Specific Cell?

Apr 21, 2014

I can assign a number of copies to print by adjusting ActiveWindow.SelectedSheets.PrintOut Copies:= , but what I want it to have the number of copies auto adjust to the value of a cell that contains a formula. I was hoping it would something as simple as Copies:=cell but no luck...

Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

Cell Referencing: Enter A Product Name In One Cell And Have The Price For That Product Automatically Appear In Another Cell

Feb 3, 2009

I'm trying to create a supply order form. Is it possible to enter a product name in one cell and have the price for that product automatically appear in another cell? Is there a formula that I can use to make this happen?

Excel 2007 :: VBA Code Determine Where To Paste Based Upon 2 Cells In Different Workbooks Being Equal

Feb 24, 2014

Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.

Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).

Windows("Raw Data.xls").Activate
ActiveWindow.DisplayWorkbookTabs = True
Sheets("Calls").Select
Range("C43").Select
Selection.Copy
Windows("Volume Tracking.xlsm").Activate
Sheets("Offline").Select
Range("AE33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".

The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).

In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.

Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?

If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........

Excel 2007

Run Macro Only If 2 Cells Are Equal To Each Other

Apr 10, 2009

I have a macro to insert a row and I would like it to be triggered by conditions in a IF statement or whatever is best? Macro has a ctrl+shortcut.

eg =IFa10=a12
do nothing
or run macro

VBA Code - Cell In Header Equal Total Amount

Mar 5, 2014

I have a report that I want the total amount (in the bottom right corner, but can vary in row number) to appear in a particular cell in the header. In the macro, I selected the cell & entered "=CTRL+End". It works in reality, but not in a macro. It returns a visual of 0.00, but "=XFB1048565" in the formular bar. How can I get the header cell to return the correct total amount?

Convert Formula To Macro Code (combining Data From Two Cells)

Jul 28, 2009

I received assistance from NBVC for combining data from two cells. Post http://www.excelforum.com/showthread.php?p=2135144. I need to convert the formula into macro code. I thought I would be able to convert it on my own, but running into some troubles with run time errors. Here is the Excel formula, which is working fine.

Macro To Check Values On Two Cells Should Be Equal

May 2, 2014

I have cells that they're values should be equal B6=B11, B7=B12, B8=B13, E6=B14, E7=B15, E8=B16, I want a macro that if the values are not equal then the background should be RED and a pop up message "The values should be equal".

Macro Code To Replace Part Of Formula With Value Of Certain Cell

Jun 18, 2014

Here is the code I'm working on:

I want this code to replace the value of s with the value of a cell.

sub Macro2()
Dim s As String

s= value of (cell F2)
Range("I8").Select
ActiveCell.FormulaR1C1 = "='[(s).xlsm]Payroll Computation '!R8C11"

End Sub

If A Certain Cell Does Not Equal Zero Then Run Macro

Dec 27, 2006

if a certain cell does not equal zero i want a macro to run. But i'm having trouble getting the logic right. here's what i have so far

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("u82").Select
If Active.Selection 0 Then

ElseIf Active.Select = 0 Then

end sub

Importing Formula / Code From One Cell To Another To Affect Multiple Cells?

Mar 5, 2014

The idea is to select an item i one cell, which in return will affect the following cells with the nutritional values. So far so good. I would then like, if a given amount is entered, it calculates the values.

A food item is typed in Cell A. The following cells will Index Match a table (on a different sheet), importing calculation formulas based on the food items nutritional value (Cells C - G). If an amount is typed in Cell B (i.e. 100 grams) the following cells will then calculate the correct nutritional values.

i learned to make an INDEX MATCH function, so that when i type "Gulerod" or something like that, the following cells import information from the Table tab... When the table contains fixed values, this works just fine. However - i tried to make the table (the cells) with calculation formulas (i.e. =SUM(4*B3))... this means the values in the tables are 0 untill a value is typed in B3 (in this example).

... however when i do the Index Match in my main tab, only the "0"'s are imported, and no matter what value i enter in B3 in the main tab, the following cells remains with the fixed value 0.

... so my question is - is it even possible to have the Index Match function import the calculation formula from the table and have it work in the main tab as well? Or is there any other way to make this work in Excel? Basically - to have the main sheet find the relevant data to use for calculation on a different tab/sheet, and then do the actually calculations on the main tab/sheet.

Shift Cells Up If Cell Equal 0

Dec 27, 2008

In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.

Here is the code I tried, but nothing happened:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub

Create Macro Code That Auto Fill Cell With Formula?

May 23, 2014

When I enter data in the cells A1 and B1, C1 needs to calculate the result. (not copy and paste)

For examle: I have formula C1=(A1+B1)/2

C column for formula, but shows result only when there is a data in A and B

Check & Change Cell After Adding Formula Via Macro Code

Apr 17, 2008

The loop is putting the correct formula into the cell - but THEN... - I wish to (within the loop and before it moves on) check if the value is equal to OR greater than 1 - if it is, make it a value of 1 (this '1' is then used at the bottom of the column of data to give a total). I need to do this before it moves onto the next cell. What am I doing wrong? Is it because once it puts formula in the cell, it then moves down - do I need to select the cell again first? As I had originally posted on this site - link: url]http://www.excelforum.com/showthread.php?t=641970[/url]

Insert Relative R1C1 SUM Formula Into Cell Via Macro Code

Apr 24, 2008

Hello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.

I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.

Public Sub SubColumn(CritStr As String) ...

Add Nest Formula/Function To Cell With Variables Via Macro Code

May 22, 2008

I would like to use a varible Cell for the following forumula:

ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F\$2,F\$3,F\$4,F\$5,0,)/3600,1)"

I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).

Macro Code To Add Sum Formula/Function, With Variable Rows, To Cell

May 29, 2008

I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column

e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17

As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.

I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17

Excel Formula - Cell To Count Another Cell Is Equal To Certain Value

Dec 18, 2012

Simply need a cell to count another cell is equal to certain value.

For example if B3 has a value of 6 then I need J3 to equal 1 or if B3 is equal to 3 than I need G3 to equal one. I tried the the COUNTIF but I am sure i am using it incorrectly.

Macro To Make Bottom Cell In A Column Equal Top Cell

Feb 5, 2014

I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.

Hide Cell If Certain Cells Equal Certain Criteria?

Mar 22, 2014

The invoice itself simply equals cells from a drop down in the columns to the right hand side. (highlight yellow) What im looking to do is if any of the cells in column I equals - - i would like the cells b16:f16 to hide within the same row from the invoice and if there is something in column I that isn't - - i would like them to reappear.

Cell V7 To Tell Me If The Number In U7 Is Greater Than Or Equal To The Individual #'s In Cells U7-U40

May 4, 2009

I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?

Join 2 Cells & Show Equal Sign In Cell

Jan 17, 2008

For Exempel

A1=B
A2=1
A3="="&A1&A2

I have even tried with concernate and indirect in A3 but no difference there...

I want A3 to return =B1 but it only returns it in text but i want it in form of a formula. Is there a formula that returns text directly into a formula or an easy way to do this?

VBA Code To Check Month And Give New Product

Jan 14, 2008

I hv following data in the excel.

I hope some one can help me to create code to do the following :-

1) if have last purchase date , but no last issue date = New Product.
2) if no purchase date , also no last issue date = None stock issue.
3) if have last purchase date , also have last issue date = cal the date between and put the month value .

For example :-

if last purchase date = Oct 07
if last monment date = Nov 07
then the stock duration = 1

Select The Result In Terms Of The Objectives Of The Product Code?

Feb 6, 2014

this function I manage to select the result in terms of the objectives of the product code. and I need to search for a piece of the product code. The results will be the same in accordance with fragments but not the exact code.

How To Count Every 5th Occurrence Of Product Code In Inconsistent List

Aug 31, 2012

I need to identify every 5th occurence of a product code (In column B)

the list won't be consistent i.e. Code 80100811 may appear in row 2, 17, 35, 47, 51

So I would need to identify the one in Row 51 in the above example,

WorksOrderNumber
Product Code
DateEntered
Column1

56
80100811
01/08/2012 00:00

[code]......

To Create A Userform With A Textbox To Enter A Product Code

Jun 11, 2009

i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel ,