Moving date from 1 column to another based on a value and changing the sing.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

banderson@nwws.biz - 04 Apr 2007 22:19 GMT

I was wondering if anyone could help me. I have seen a few examples
that are fairly close to what I'm looking for.
I'm wanting to look at a range of data in a column and find all the
cells that have a value not equal to 0, then move it one column to the
left and change the sign to negative.
Below is the columns I have setup:

Column A B C D
E
Cash 15,343.00 0 147.34 0
Petty Cash 0 150.00 300.00 0
name 0 20,000.00 20,000.00 0
wachovia 0 1,569.95 0
1,569.95

I'll explain the columns to you Column B = Debits, Column C = Credits
So column C is the column being checked here.
If it does not have a 0 in it move the value to the corresponding cell
in Column B and make it negative.

Thanks

Reply to this Message

banderson@nwws.biz - 04 Apr 2007 22:31 GMT

The following Code is what I have but it will only allow me to use =
with the inputbox and I'm not sure how to change this to make it be
not equal "<>"
I thought this was the symbol but I guess in a inputbox it doesn't
like that much.Also this code does not allow me to make the data being
moved negative.
Thank you

Sub Move_Stuff()
Dim topCel As Range
Dim bottomCel As Range
whatval = InputBox("Enter a Value")

Set topCel = Range("A1")
Set bottomCel = Cells((800), topCel.Column).End(xlUp)
If bottomCel.Row >= topCel.Row Then
With Range(topCel, bottomCel)
Set c = .Find(whatval, LookIn:=xlValues, LookAt:=xlPart)
If Not c Is Nothing Then
Do
c.Cut Destination:=c.Offset(0, -1)
Set c = .FindNext
Loop While Not c Is Nothing
End If
End With
End If

End Sub

Reply to this Message

Rate this thread: