help with excel programing
 

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/

help with excel programing
 

Option Explicit

Sub Macro1()
Dim x As Long
Dim y As Double
Dim s As Double
Dim z As Double
Dim rw As Long
Dim sh As Worksheet
Dim sh1 As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary").Delete
Application.DisplayAlerts = True
On Error GoTo 0
rw = 2
Worksheets.Add
Set sh1 = ActiveSheet
sh1.Name = "Summary"
sh1.Cells(1, 1).Resize(1, 4).Value = _
Array("Name", "y", "z", "s")
For Each sh In ThisWorkbook.Worksheets
If sh.Name < sh1.Name Then
With sh
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
z = .Cells(Rows.Count, 1).End(xlUp).Value
s = y * 100 + z
End With
With sh1
.Cells(rw, 1).Value = sh.Name
.Cells(rw, 2).Value = y * 100
.Cells(rw, 3).Value = z
.Cells(rw, 4).Value = s
rw = rw + 1
End With
End If
Next sh
sh1.Range("A1").CurrentRegion.Sort key1:=sh1.Cells(1, 4), _
order1:=xlDescending
End Sub


--
Regards,
Tom Ogilvy


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/