Thread: macro to search for & select ranges to sum

Hi, Here is my dillemma (I'm a VBA newbie):

I have created a macro using VBA that takes raw data from an external excel
spreadsheet in the following form

Mark 28
Mark 32
Jim 13
Jim 57
Jim 84
Elliot 15
Jason 48
Jason 48
Jason 90
Jason 85

and transforms it into the following format into a new spreadsheet

Mark 28
Mark 32


Jim 13
Jim 57
Jim 84


Elliot 15


Jason 48
Jason 48
Jason 90
Jason 85

What I want the macro to then do is to go through and differentiate each
range from each other, then sum the values in each range in the cells
directly below each range. I need to use a macro because size and number of
ranges will vary. How do I program a macro so that it will go to the first
empty cell below each range and sum the values for that range?

Any help is appreciated,

Jason

I used the following programming to get the raw data into the format above:

Sub Import_Job_Log()
Application.DisplayAlerts = False
Application.Goto reference:="job_log_loc"
Selection.CurrentRegion.Select
Selection.Clear
Application.Goto reference:="job_log_loc"
mywindow = ActiveWorkbook.Name
myfile = Application.GetOpenFilename("microsoft excel files,*.xls")
Workbooks.Open Filename:=myfile
Selection.CurrentRegion.Select
Selection.Copy
myworkbook = ActiveWorkbook.Name
Windows(mywindow).Activate
ActiveSheet.Paste
Windows(myworkbook).Activate
ActiveWindow.Close
Application.Goto reference:="format"
Selection.EntireRow.Hidden = False
Selection.End(xlUp).Select
Range("E3").Select
Do Until ActiveCell = "stop"
If ActiveCell = ActiveCell.Offset(1, 0) = False Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
End If
ActiveCell.Offset(1, 0).Select
Loop

Jason,
It looks like to me that you could sort your original list
and then use the Subtotals feature from the Data
menu to accomplish what you want.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Jason Head"
<jason.head@cbre.com>
wrote in message
Hi, Here is my dillemma (I'm a VBA newbie):
I have created a macro using VBA that takes raw data from an
external excel spreadsheet in the following form

Mark 28
Mark 32
Jim 13
Jim 57
Jim 84
Elliot 15
Jason 48
Jason 48
Jason 90
Jason 85

and transforms it into the following format into a new spreadsheet

Mark 28
Mark 32


Jim 13
Jim 57
Jim 84


Elliot 15


Jason 48
Jason 48
Jason 90
Jason 85

What I want the macro to then do is to go through and differentiate each
range from each other, then sum the values in each range in the cells
directly below each range. I need to use a macro because size and number of
ranges will vary. How do I program a macro so that it will go to the first
empty cell below each range and sum the values for that range?

Any help is appreciated,

Jason

I used the following programming to get the raw data into the format above:

Sub Import_Job_Log()
Application.DisplayAlerts = False
Application.Goto reference:="job_log_loc"
Selection.CurrentRegion.Select
Selection.Clear
Application.Goto reference:="job_log_loc"
mywindow = ActiveWorkbook.Name
myfile = Application.GetOpenFilename("microsoft excel files,*.xls")
Workbooks.Open Filename:=myfile
Selection.CurrentRegion.Select
Selection.Copy
myworkbook = ActiveWorkbook.Name
Windows(mywindow).Activate
ActiveSheet.Paste
Windows(myworkbook).Activate
ActiveWindow.Close
Application.Goto reference:="format"
Selection.EntireRow.Hidden = False
Selection.End(xlUp).Select
Range("E3").Select
Do Until ActiveCell = "stop"
If ActiveCell = ActiveCell.Offset(1, 0) = False Then
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
End If
ActiveCell.Offset(1, 0).Select
Loop