macro- keep value in cell and add value for adjacent cell

K

kgallo

I am trying to write a simple macro to an essence do the following:
A B C
1 =(564*12) =216 =((564*12)-216)
2
3

I want a macro to do column C. I have hundreds of rows where I need this and
would rather not type them all out.
Any help would be much appreciated!
Thanks.
 
J

JLGWhiz

In column C2 type

=a2 - b2

Then drag it down the entire column for as many rows as you have data in
columns A and B. To drag it down, select the cell with the formula in column
C and put the mounse pointer on the node at the lower right corner of the
selection outline until it forms a plus sign. Press and hold the left mouse
button and drag down. It will copy your formula and assign the appropriate
row numbers as it does so.
 
K

kgallo

But I don't want the cell address in column C. I want the actual formulas in
column A and column B. I just want to concatenate them.
ie: not a2-b2, but instead ((562*12)-100).
Do you know how to do that?
thanks.
 
K

kgallo

No, but yes. So for example:
A B C
1 =(564*12) =216 =((564*12)-216)
2 =(200*4) =45 =((200*4)-45)
3 =(568*48) =58 =((568*48)-58)

I have hundreds of row of the above (not the same numbers - all different).
Do you know how to write a macro to do this?
Thanks!
 
K

kgallo

I am wondering if there is an easier way to do this other than manually
copying each cell and subtracting the value in the next cell. Yes I
understand that is an option. But after a few hundred rows and then a few
more spreadsheets of a few hundred rows, this can get old and tiresome and
may contribute to human error.

Hence, I am wondering if someone knows of a macro that will take the actual
formula in some highlighted cells and get the outcome I'm looking for.
Thanks!
 
J

JLGWhiz

What I see is that you are subtracting the value in column b from the product
of the formula in column a. That is what the formula in column C does by
your illustration below. That is if you try to concatenate the values of the
two columns, you will have to change the data types to string, then you could
use

=CONCATNATE(a2, b2)

in cell C2 then copy down. That would yield =(564*12)=216

and I don't believe that is what you want. What I originally gave you does
what the formula that you show for column C does, it just does not display
the formula in that format. It does give the same resulting value in column
C.
 
K

kgallo

yes but when i do that I have the formula with concatenate in it.
what i want is the formula with the values in the cell.
I understand how to get the amount to show up. For example:
What I want when you click on a cell you see the following:
A B C
1 =(564*12) =216 =((564*12)-216)
2 =(200*4) =45 =((200*4)-45)
3 =(568*48) =58 =((568*48)-58)

What I don't want:
A B C
1 =(564*12) =216 =A1-B1
2 =(200*4) =45 =A2-B2
3 =(568*48) =58 =A3-B3
OR
A B C
1 =(564*12) =216 =concatenate(A1, B1)
2 =(200*4) =45 =concatenate(A2, B2)
3 =(568*48) =58 =concatenate(A3, B3)

Do you know how to yield the outcome I'm looking for?
Thanks!
I'm pretty sure it has to be done with a macro, I just don't know the syntax.
 
M

Mike

Sub duh()
Set rng = ActiveSheet.Range(Cells(1, "A"), _
Cells(Rows.Count, "A").End(xlUp))
With rng
For i = 1 To .Rows.Count
.Cells(i).Offset(0, 2).Value = _
.Cells(i).Formula & "-" & _
.Cells(i).Offset(0, 1).Value
Next i
End With
End Sub
 
M

Mike

Must have worked for you

kgallo said:
yes but when i do that I have the formula with concatenate in it.
what i want is the formula with the values in the cell.
I understand how to get the amount to show up. For example:
What I want when you click on a cell you see the following:
A B C
1 =(564*12) =216 =((564*12)-216)
2 =(200*4) =45 =((200*4)-45)
3 =(568*48) =58 =((568*48)-58)

What I don't want:
A B C
1 =(564*12) =216 =A1-B1
2 =(200*4) =45 =A2-B2
3 =(568*48) =58 =A3-B3
OR
A B C
1 =(564*12) =216 =concatenate(A1, B1)
2 =(200*4) =45 =concatenate(A2, B2)
3 =(568*48) =58 =concatenate(A3, B3)

Do you know how to yield the outcome I'm looking for?
Thanks!
I'm pretty sure it has to be done with a macro, I just don't know the syntax.
Click to expand...
 
J

JLGWhiz

Although it makes now sense to me what you are trying to do. This will do
what you want.

Sub stantial()
Dim lr As Long, i As Long
Dim sh As Worksheet
Set sh = ActiveSheet
Columns("A:C").NumberFormat = "@"
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lr
Range("C" & i) = Range("A" & i).Value & "-" & _
Right(Range("B" & i).Value, Len(Range("B" & i)) - 1)
Next
End Sub

I suggest you copy and paste rather than trying to retype it.
 
Top