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!R[3]C[-7]*Calcs!R[14]C[-7]"
View 3 Replies
ADVERTISEMENT
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
View 4 Replies
View Related
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
View 9 Replies
View Related
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”.
View 2 Replies
View Related
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...
View 2 Replies
View Related
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.
View 4 Replies
View Related
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?
View 2 Replies
View Related
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
View 9 Replies
View Related
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
View 9 Replies
View Related
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?
View 3 Replies
View Related
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.
View 5 Replies
View Related
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".
View 9 Replies
View Related
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
View 5 Replies
View Related
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
Call unhide_addABBR
ElseIf Active.Select = 0 Then
Call hide_addABBR
end sub
View 9 Replies
View Related
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.
View 5 Replies
View Related
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
View 9 Replies
View Related
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
View 7 Replies
View Related
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]
View 2 Replies
View Related
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) ...
View 8 Replies
View Related
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).
View 6 Replies
View Related
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
View 3 Replies
View Related
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.
View 1 Replies
View Related
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.
View 1 Replies
View Related
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.
View 6 Replies
View Related
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?
View 3 Replies
View Related
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?
View 3 Replies
View Related
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
View 9 Replies
View Related
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.
View 5 Replies
View Related
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]......
View 5 Replies
View Related
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 ,
View 9 Replies
View Related
