help with automatically negating values

[CROY1985]

Hello

I have imported some data from our accounts programme into excel using MS Query.

Column A contains a unique reference. Column B, the name for that particular account, and columns C through N contain monthly figures.

What i need to do is negate all the figures from column C through N, where the unique reference falls between 100000 - 199999 inclusive.

Thanks

 

[markmzz]

If I understand correctly what you want, so this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Ref</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Month01</td><td style="font-weight: bold;;">Month01</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"> 9908799 </td><td style=";">Name54</td><td style="text-align: right;;">$610.00 </td><td style="text-align: right;;">$610.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"> 7,273,078 </td><td style=";">Name54</td><td style="text-align: right;;">$459.00 </td><td style="text-align: right;;">$459.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"> 1,716,266 </td><td style=";">Name29</td><td style="text-align: right;;">$807.00 </td><td style="text-align: right;;">($807.00)</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"> 3,874,225 </td><td style=";">Name91</td><td style="text-align: right;;">$664.00 </td><td style="text-align: right;;">$664.00 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"> 6,529,328 </td><td style=";">Name64</td><td style="text-align: right;;">($186.00)</td><td style="text-align: right;;">($186.00)</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"> 7,951,165 </td><td style=";">Name76</td><td style="text-align: right;;">$279.00 </td><td style="text-align: right;;">$279.00 </td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"> 1373341 </td><td style=";">Name40</td><td style="text-align: right;;">$159.00 </td><td style="text-align: right;;">($159.00)</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"> 8,706,688 </td><td style=";">Name15</td><td style="text-align: right;;">$675.00 </td><td style="text-align: right;;">$675.00 </td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"> 62,133 </td><td style=";">Name18</td><td style="text-align: right;;">$317.00 </td><td style="text-align: right;;">$317.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">=IF(<font color="Blue">(<font color="Red">1*$A2<100000</font>)+(<font color="Red">1*$A2>1999999</font>),C2,-ABS(<font color="Red">C2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Trevor G]

So do you want to just highlight the others, delete the rows where these numbers appear. Or remove them from the cells?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> clearcells()<br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rng <SPAN style="color:#00007F">In</SPAN> Range("C1:N500")<br><SPAN style="color:#00007F">If</SPAN> rng.Value >= 100000 <SPAN style="color:#00007F">Or</SPAN> rng.Value <= 99999 <SPAN style="color:#00007F">Then</SPAN><br>rng.ClearContents<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[CROY1985]

Hi Mark thanks for your quick response. Let me clarify a little further.

The references i talk about in column A are in numerical order. And i want to multiply all the cells by -1 (so negative become positive, and positives become negative).

However, i cant add more columns. So doing a quick =C2*-1 in D2 wont work.

 

[CROY1985]

I think your very close trevor. Where the cells in column a are greater than 100000 and less than 199999, then i want to multiply figures in Columns C:N by -1.

 

[markmzz]

This formula do the job, but need others columns:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>O</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">$610.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">O2</th><td style="text-align:left">=IF(<font color="Blue">(<font color="Red">$A2<100000</font>)+(<font color="Red">$A2>199999</font>),C2,-C2</font>)</td></tr></tbody></table></td></tr></table><br />
I will do anothers tests and post here if I found the formula.

Markmzz

 

[Trevor G]

Can you show some data and also state where you want the calculation to be shown, as you have stated that you can't add more columns? Do you want the formula to go into column O ?

Look at Markmzz formula does that work for you...

 

[CROY1985]

Im not sure how to show my sheet as you have above, But lets use the following as an example:

Column A
row1:100000
row2:150010
row3:200000
row4:300000

Column B
Income
Other Income
Expenses
Overheads

Column C
-500
100
200
100

Column D
-600
-200
400
100

Now because A1 and A2 fall between 100000 and 199999 i want the sheet to multiply C1:D2 by -1, making column C look like:

500
-100
200
100

And column D:
600
200
400
100


If i was doing this manually, i would type -1 in an empty cell (say O2) copy it, highlight C1:D2, paste special, values and multiply. And then delete the -1 in cell O2.

Hope this helps.

Thanks

 

[markmzz]

I think that this code for Trevor G (with my small modification) do the job:

Sub clearcells()
    Dim rng As Range
    For Each rng In Range("C2:N10")
        If Cells(rng.Row, 1).Value >= 100000 And Cells(rng.Row, 1).Value <= 199999 Then
            rng.Value = -rng.Value
        End If
    Next
End Sub

Markmzz

 

[CROY1985]

Yes that works a treat. Thank you very much guys!