allrihgt i have possibly a simple question and possibly not.

I've never programmed in visual basic but it seems quite simple. I'm
very fimilliar with c/c++ and i think i can get the hang of it quickly.
With that being said...

I have a program that saves about 10,000 pieces of data in cell b
starting from 1-n and it saves it as a csv file or soemthing that excel
can open. My question is how would i go about writing a script i can
run on the open sheet that will

1. Convert all numbers in the b cells to positive numbers

2. Add all of the numbers together

3. Display the numbers in another field or a script box or whatever u
would use.

Simply put, i just need to quickly compute seperate spread sheets with
the previous criteria. I just cant figure out how to make something i
can run on seperate sheets as opposed to the script being built into
the sheet.

Any ideas would be greatly appreciated,
~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Use a macro.

Sub Macro1()
x = 1
Do While Range("B" & x).Value < ""
Range("B" & x) = Abs(Range("B" & x))
x = x + 1
Loop
End Sub

A macro can be executed from another spreadsheet. You will need both sheets
open. The one with the macro and the one with the data.



--

"drummerboy827" wrote in message
...
allrihgt i have possibly a simple question and possibly not.

I've never programmed in visual basic but it seems quite simple. I'm
very fimilliar with c/c++ and i think i can get the hang of it quickly.
With that being said...

I have a program that saves about 10,000 pieces of data in cell b
starting from 1-n and it saves it as a csv file or soemthing that excel
can open. My question is how would i go about writing a script i can
run on the open sheet that will

1. Convert all numbers in the b cells to positive numbers

2. Add all of the numbers together

3. Display the numbers in another field or a script box or whatever u
would use.

Simply put, i just need to quickly compute seperate spread sheets with
the previous criteria. I just cant figure out how to make something i
can run on seperate sheets as opposed to the script being built into
the sheet.

Any ideas would be greatly appreciated,
~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

thanks...that works great in changing negative to positive and it looks
like it is adding them as it goes as well...my question is now how to
display the x value in a text box or soemthing on the macro page.

~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

thy this

Sub Macro1()
x = 1
Do While Range("B" & x).Value < ""
' Range("B" & x) = Abs(Range("B" & x))
x = x + 1
y = y + Range("B" & x).Value
Loop
Range("C1") = y
MsgBox ("Count = " & x & " Total = " & y)
End Sub



"drummerboy827" wrote in message
...
thanks...that works great in changing negative to positive and it looks
like it is adding them as it goes as well...my question is now how to
display the x value in a text box or soemthing on the macro page.

~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Sub Macro1()
Dim x as Long
dim y as double
x = 1
Do While Range("B" & x).Value < ""
if isnumeric(Range("B" & x)) then
Range("B" & x) = Abs(Range("B" & x))
y = y + Range("B"& x).Value
end if
x = x + 1
Loop
msgbox "Cells Checked: " & y
End Sub

Another approach

Sub Macro2()
Dim rng as Range
Dim rng1 as Range
Dim cell as Range
set rng = Range.Cells(1,2),Cells(rows.count,2).End(xlup))
On Error Resume Next
set rng1 = rng.SpecialCells(xlConstants,xlNumbers)
On Error GoTo 0
if not rng1 is nothing then
for each cell in rng1
cell.Value = abs(cell.Value)
next
msgbox Application.Sum(rng1)
End if
End Sub

--
Regards,
Tom Ogilvy



"drummerboy827" wrote in message
...
thanks...that works great in changing negative to positive and it looks
like it is adding them as it goes as well...my question is now how to
display the x value in a text box or soemthing on the macro page.

~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

U guys are great thanks for all of the help, im starting to get the hang
of this visual basic stuff. Microsoft's editor makes it pretty easy to
do. before my questions here is the code im using now...

Sub Macro1()
Dim x As Long
Dim y As Double
x = 1
Do While Range("B" & x).Value < ""
If IsNumeric(Range("B" & x)) Then
Range("B" & x) = Abs(Range("B" & x))
y = y + Range("B" & x).Value
End If
x = x + 1
Loop
MsgBox "Sco " & y * 100
Range("d1") = "Sco"

Range("e1") = y * 100
End Sub

ok...i have a couple of additional questions tho...

1. in the a colomn there is another number also about 10,000 entrys
long. I need the macro to go to the last entry, store it in z or
something, then add it to y before it displays it. i guess y will have
to change to something else...s seems logical to me being its going to
be a score if u didnt know from the code.

2. this is kind of a complicated question...ok..say i run this macro on
20 sheets and i have all of the sheets open. I would like to run
another macro that takes the file name, puts it in colomn a (of the new
sheet), takes the y value, puts it in b, takes the z value, puts it in
c and takes the s value and puts it in d. then...i want to sort all of
the rows in decending order by the d colomn (the s value)

Once again, any help would be appreciated..you guys are saving me so
much time rather then me going to find someone to go do this for me and
charge me money...

~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Just to give you another option without changing the values...

Sub Demo()
[D1:E1].FormulaArray = Array("Sco", "=100*Sum(Abs(B1:B65535))")
MsgBox "Score was: " & FormatNumber([E1], , , , vbTrue)
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"drummerboy827" wrote in message
...
U guys are great thanks for all of the help, im starting to get the hang
of this visual basic stuff. Microsoft's editor makes it pretty easy to
do. before my questions here is the code im using now...

Sub Macro1()
Dim x As Long
Dim y As Double
x = 1
Do While Range("B" & x).Value < ""
If IsNumeric(Range("B" & x)) Then
Range("B" & x) = Abs(Range("B" & x))
y = y + Range("B" & x).Value
End If
x = x + 1
Loop
MsgBox "Sco " & y * 100
Range("d1") = "Sco"

Range("e1") = y * 100
End Sub

ok...i have a couple of additional questions tho...

1. in the a colomn there is another number also about 10,000 entrys
long. I need the macro to go to the last entry, store it in z or
something, then add it to y before it displays it. i guess y will have
to change to something else...s seems logical to me being its going to
be a score if u didnt know from the code.

2. this is kind of a complicated question...ok..say i run this macro on
20 sheets and i have all of the sheets open. I would like to run
another macro that takes the file name, puts it in colomn a (of the new
sheet), takes the y value, puts it in b, takes the z value, puts it in
c and takes the s value and puts it in d. then...i want to sort all of
the rows in decending order by the d colomn (the s value)

Once again, any help would be appreciated..you guys are saving me so
much time rather then me going to find someone to go do this for me and
charge me money...

~chris culp



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/