How To Make Cell Value Equal Negative Of Cell Value On Different Tab

Sep 30, 2013

I have a monthly forecast document that is updated by an operator monthly. At the end of the month, the operator presses a "Copy Accrual" macro that copies accruals for the month and stores them on a seperate tab. After sending the document back to the Finance team (necessary step due to software complications), Finance will send the operator back an updated file at beginning of next month. The operator than presses a "Paste Reversal" macro that is supposed to input the negative of the accrual stored in the seperate tab. The problem is that it is posting as the same value (not negative of value). I tried recording a Macro to fix this, but it did not work. An example of the code that came up with is:

Worksheets("Weekly Forecast").Select
Range("B9").FormulaR1C1 = "=-Accruals!RC[-4]"
Range("H9").Select
Selection.Copy
Range("H10:H33").Select
ActiveSheet.Paste

Thoughts on what to do? I think the two things that are throwing me off is the negative, and moving between the different sheets.

View 2 Replies


ADVERTISEMENT

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.

View 1 Replies View Related

Make Textbox In Userform Equal Cell Value?

Jan 27, 2014

I am trying to make my textbox on the userform equal a cell value in a column. I need the userform to look at the last cell that has a value in a column and display that value into the textbox on the userform.

Ex. I am starting my data from Cell "O8" and the last possible row would be "O51" Starting with O8 I need my textbox in the userform to read this cell value. After each entry the next row in Column O will have a value, so I would need my userform textbox to read cell "O9" value the next time the user form opens. Here is what I got thus far:

[Code].....

It gives me a Compile Error: Invalid or unqualified reference.

View 3 Replies View Related

Make Cell Equal To Another Cell

Jul 5, 2009

I am looking for a way for a cell to equal another cell but the cell that it looks at is controlled via another cell.

So, for example, cell A1 I type in E5 and this tells cell B1 to display the contents in cell E5.

View 3 Replies View Related

Formula To Make Product Of Two Negative Numbers Negative

May 12, 2009

I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.

View 5 Replies View Related

If Cell In Column T Is Empty Use Column U (but Make It Negative)

Mar 13, 2014

Formula for this? If a cell in column T is empty, use column U (but make it negative)??

View 2 Replies View Related

How To Make Arrange Colour Equal Cell Colour

May 23, 2014

A column of cells can be 1o different shades of green according to their value (achieved by using conditional formatting.) I want to arrange that at a certain time all the cells of a part of the row of a formatted cell are the same colour.

View 4 Replies View Related

Make Now () = 06/29/2009 (make To Cells With Dates Equal Each Other)

Jun 29, 2009

I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09

View 2 Replies View Related

Count If A Cell Is Equal To Cell In Different Spreadsheet And Is Less Or Equal Zero

Apr 17, 2014

How to correct my formula because it does not work?

Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")

I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.

View 5 Replies View Related

Positive To Negative If Cell On Left Negative

Sep 1, 2007

I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?

View 7 Replies View Related

Change A Cell To Be Positive Or Negative Based On Name From List On Another Cell

Dec 8, 2013

My goal from image is to enter a number for example in cell F4 and have in appear in L6 so simply L6=F4 but I want it to be negative if cell M2 reads "Outgo" and positive if cell M2 reads "Intake" as selected from the list. Is this possible?

View 5 Replies View Related

A+B = C, But If A Is A Negative Number, Then Make It 0.

Oct 27, 2009

Is there a way to a formula that would show the following? A plus B = C but if A is below 0, stop the value at 0 then add column B:

Columns:
A B C
-2 5 5
1 5 6
0 5 5

View 2 Replies View Related

Delete Rows Where Cell Doesn't Equal Adjacent Cell

Aug 16, 2007

I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).

View 3 Replies View Related

VBA / How To Make Array Equal To One Value

Mar 23, 2012

I am not sure if I am using the correct terminology when I see "array". What I want to do in VBA is what I know how to do in non-vba excel functions (cntrl + shift + enter). For example, I have a range of cells that need to meet one condition ( ""):

Code:

If Sheet8.Range("C" & rw) "" Or Sheet8.Range("D" & rw) "" Or Sheet8.Range("E" & rw) ""...etc Then

How do I bundle this in one package. What you see below is obviously incorrect, but I am trying to accomplish this with proper syntax.

Code:
If Or(Sheet8.Range("C" & rw & ":Z" & rw) "")

View 9 Replies View Related

Using Macro To Make Data Negative?

May 21, 2013

I run a MTD sales report from Access. If the "SOP Type" in Column Q, is a "4", I have to manually change the Qty and Price to negative values as that "4" denotes a Credit. My question is this...

Can I write a Macro that would be able to read the value in Column Q and, where necessary, make the values negative in the appropriate columns?

View 1 Replies View Related

Restrictions On Drop Down List If Cell Is 'a' Then Other Cell Must Equal 'b'

Jul 15, 2009

I have two cells, both of which have drop down lists for options 'a' & 'b'. When one equals 'a' the other should be restricted into only saying 'b' and vice versa. I would also like the user to be reminded that he can only select 'a' once from the two menus. Ideally I would like to do this in a formula rather than VBA?

View 2 Replies View Related

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.

View 1 Replies View Related

VBA - Delete Column If Cell Value Of Range Is Equal To Another Cell

Nov 5, 2013

I would like to create a VBA code where it will delete the entire column if the cell value is equal to value in D2

For example:

Sub Delete_Columns()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("G2:S43"), ActiveSheet.UsedRange)

For Each cell In rng
If (cell.Value) = D2 _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireColumn.Delete
End Sub

======

But I think this line is wrong but I am not sure how to fix it - If (cell.Value) = D2 _

View 5 Replies View Related

Making 1 Cell Equal Another Cell Including Its Comment?

May 18, 2014

I have a spreadsheet with some cells which return values using INDEX and MATCH to bring back the entire details of a person based on there membership number. However I require the comments to be attached with the returned values. The comments have there image in, rather than just text.

I have found some answers with vba, which do the process, however you had to manually enter the cell, from which the comment was to be copied from, into a pop up box. This involved finding the box myself to then select the box to take the comment from. In doing this I had found and read the comment, which is the is the process I'm trying to bypass.

View 8 Replies View Related

Last Occupied Cell To The Left And Equal To Or Above Current Cell

Nov 22, 2009

I've been pounding away at a project for two days trying to determine a way to identify a solution, to no avail.

Here is some experimental code. Under the assumption that in Column "A", every fifth line contains some text (a, b, c, etc) I want to place a function in column B that would identify the closest occupied cell above and to the left of the cell containing the function. The sub achieves this, but the function has a problem because of the Activecell line. Is there a way to identify the location of the cell containing the function to act as a reference point for the range?

Sub find_last_heading()
Application.ScreenUpdating = False
x = ActiveCell.Address
ActiveCell.Offset(0, -1).Select
last_heading = ActiveCell.Text
Do Until last_heading ""
ActiveCell.Offset(-1, 0).Select
last_heading = ActiveCell.Text
Loop
MsgBox last_heading
Range(x).Select
End Sub
Function cathead(Optional reference)

Dim cell

View 9 Replies View Related

Identify Rows Where A Given Cell Is Equal To The Contents Of The Cell Below

Apr 26, 2006

I need to identify rows where the contents of a given cell are equivalent to the cell below. I have managed to get this working based on iterating through the cells and selecting them both for cutting.

However it doesn't work effectively: It seems to be leaving blank lines in the original sheet and further fails after the first execution. I would be grateful for any suggestions on a good method to perform the following operations:
(1)to compare the cell with the cell below
I have used

If cell = cell.Offset(1, 0) Then

(2)to act on the rows for the two connected rows

Sub Test4()

' Loop through rows

Dim TheExtract As String
Dim cell As Range

'Application. ScreenUpdating = False
For Each cell In Worksheets("Sheet 1"). _
Range("A1", Worksheets("Sheet 1").Range("A65536").End(xlUp))
TheExtract = cell
If cell = cell.Offset(1, 0) Then
cell.Rows("1:2").EntireRow.Select
Selection.Cut
Sheets("Test").Select
ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End If
Next cell
'Application.ScreenUpdating = True

End Sub

View 3 Replies View Related

How To Make Profit And Savings Equal

Dec 18, 2012

How do I come up with a price that will make my profit and my customers savings equal.

Example:
Retail price $178.99
My cost $113.46

If I charge $142.23 then my profit is $28.76

Customer pays $142.23 plus $8.00 shipping ($150.23)
$178.99 Retail minus $150.23 = $28.76 savings

View 9 Replies View Related

How To Make Conditional Formatting For Negative Numbers

Feb 19, 2014

I need the conditional formatting to make all numbers that are zero clear (i.e. no fill).

I need it to make all negative numbers to be red, however it doesn't seem to recognize "-1" as a number, and ends up highlight everything red when I say "highlight values < -1 red".

How would I do this?

View 2 Replies View Related

Make Certain Textbox Negative Value Based On Checkbox

Mar 20, 2014

I have a part of code but would need to see if there is a simple way to combine all my textbox to become negative value If my Combobox is checked.

In my Module, This will Calculate my Textbox11 each time I call for my Sub

This will call my Sub updateTextBoxes() I have set up from Textbox2 to textbox10

In my Userform I have a CheckBox1. If this CheckBox1 is Selected then I need to change the entry of all Textboxes(2-10) to be negative Value.

Works well right now but I need to identify all Textbox separately how could I bundle this up... even if a module is called...

So IF checkbox1 = True then textbox2 to textbox10 will = a negative value... If user adds 2 to textbox2 when the checkbox1 is = True then 2 will turn to -2

View 3 Replies View Related

Automatically Decrease Value To Make Total Equal 100

Mar 30, 2013

I receive a large file every month with 30K+ rows of data that need to be manipulated manually before I can send it off. Within the data are "chunks" of data that are anywhere from 1 to 90 rows each. Each chunk needs to total 100 or less, and while they do for the most part, some end up over 100 due to rounding (usually 100.02 or so). The rows above and below each chunk are blank, and I currently have a calc to add each chunk, and flag it if it's over 100. Then, I have to go through each of the flagged chunks and manually decrease one of the lines to make the total 100.

Is there any way to automate any or all of this procedure?

I have attached a sample file with two chunks of data. The actual files contains 14 more columns to the left that aren't shown.

Sample.xlsx

View 5 Replies View Related

Make Positive Entered Numbers Appear As Negative For Budget

Mar 28, 2007

I am trying to create a budget in excel and want to type my numbers (in certain cells only) without a - sign but I want them to appear, and calculate, as negative numbers. Basically, I am lazy and don't want to have to remember to use a - for all of my expenses when I am inputting the data.

View 5 Replies View Related

Find Cell Location With Equal Value Of Another Cell

Jul 11, 2012

code that will go to a cell in Sheet1 column A that has the same or closest value to cell B11 in another workbook. The number values in the column are to three decimal places and the run in order down the column from lowest value to hightest.

View 6 Replies View Related

Cell To Equal Another Cell If CheckBox Checked

Sep 25, 2007

I need to be able to select a checkbox on my form and when selected, fill data from other cells on the form to other cells. Here is what I have so far

Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Cell(F13).Value = Cell(B13).Value
Else If CheckBox1.Value = False Then
'do nothing
End If
End Sub

But it does not work I get a compile error on the: Cell(F13).Value = Cell(B13).Value

View 2 Replies View Related

Make Variable Equal To Active Range In Sheet

Apr 2, 2014

I would like to run a calculation based on selected active range in sheet. I tried several methods and cannot make it work.

View 2 Replies View Related

VBA To Set Cell Equal To Another Cell On Different Worksheet

Apr 25, 2014

What I have is a sheet that checks for the current date when it opens, if the day is a Thursday (payday) then it does stuff. What I am having an issue with is setting a cell equal to another cell by formula, but rather than using the cell reference itself, sheets.cell, I have that cell in a variable Var6. When I run the code I am getting an application or object defined error.

The variable set is as follows:

Var6 = "Budget!AI26"

Formula ....

View 5 Replies View Related







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