Gary, how many people know what (,0) really means. It doesn't cost anything
to add the columnAbsolute:=False and it makes the code easier to maintain. I
guess you never took any real courses in programming.
"Gary Keramidas" wrote:
> just another way:
>
> LastdataCell.Address(,0)
>
> --
>
>
> Gary
>
>
> "Joel" <(E-Mail Removed)> wrote in message
> news:87F8C060-0F16-4FA8-B803-(E-Mail Removed)...
> > The line below is corrected. Using Address returns $A$1. adding
> > columnAbosolute returns A$1.
> >
> > TotalCell.Formula = "=SUM(" & _
> > FirstdataCell.Address(columnAbsolute:=False) & ":" & _
> > LastdataCell.Address(columnAbsolute:=False) & ")"
> >
> > "Rick" wrote:
> >
> >> Joel,
> >>
> >> I have just inserted this into the macro and it worked great except when it
> >> copies the formula across the columns they all have the first column's
> >> reference rather than the column they are in.
> >>
> >> How do I fix this?
> >>
> >> Thanks
> >> Rick
> >>
> >>
> >> "Joel" wrote:
> >>
> >> > I based this code on your original code that was using the ACTIVECELL.
> >> > There
> >> > are better way of doing this if you have multiple sections of code on the
> >> > same worksheet.
> >> >
> >> > It seem you have header rows seperated by one blank row and the rows of
> >> > data
> >> > followed by at least one blank row. If there is some pattern to your
> >> > worksheet of a clear way of identifying the heders rows one macro can be
> >> > writen to transverse the entire worksheet.
> >> >
> >> > You could have something as simple as this
> >> >
> >> > Header Row
> >> > one blank row
> >> > Data cells
> >> > one or more blank rows
> >> >
> >> > Header Row
> >> > one blank row
> >> > Data cells
> >> > one or more blank rows
> >> >
> >> > repeat down the worksheet
> >> >
> >> > "Rick" wrote:
> >> >
> >> > > Thanks Joel.
> >> > >
> >> > > So for each section total do I just put this code where each section is
> >> > > created?
> >> > >
> >> > > Thanks again in advance
> >> > >
> >> > > "Joel" wrote:
> >> > >
> >> > > > It is always better to use intermediate variables that are descriptive
> >> > > > so you
> >> > > > can document the code easily.
> >> > > >
> >> > > > Set HeaderCell = ActiveCell
> >> > > > Set FirstdataCell = HeaderCell.Offset(2, 0)
> >> > > > Set LastDataCell = FirstdataCell.End(xlDown)
> >> > > > LastDataCell.Resize(10, 14).ClearContents
> >> > > > LastDataCell.Resize(7, 14).Delete Shift:=xlUp
> >> > > >
> >> > > > 'reset Lastdatacell since it was deleted
> >> > > > Set LastDataCell = FirstdataCell.End(xlDown)
> >> > > > Set TotalCell = LastDataCell.Offset(2, 0)
> >> > > > TotalCell.Formula = "=SUM(" & FirstdataCell.Address & ":" & _
> >> > > > LastDataCell.Address & ")"
> >> > > > 'copy formula across row
> >> > > > TotalCell.Copy _
> >> > > > Destination:=Range(TotalCell, TotalCell.Offset(0, 14))
> >> > > >
> >> > > > "Rick" wrote:
> >> > > >
> >> > > > > I download a report into Excel and I have created a macro to reformat
> >> > > > > it so
> >> > > > > it is easy to read.
> >> > > > >
> >> > > > > I want to add totals at the bottom of each section break. The problem
> >> > > > > I have
> >> > > > > is that I don't know how big each section is as it changes from month
> >> > > > > to
> >> > > > > month. when I add the section breaks I use;
> >> > > > >
> >> > > > > ActiveCell.Offset(2, 0).End(xlDown).Resize(10, 14).Select
> >> > > > > Selection.ClearContents
> >> > > > > ActiveCell.Resize(7, 14).Select
> >> > > > > Selection.Delete Shift:=xlUp
> >> > > > >
> >> > > > > Which takes the cells I don't want and deletes and clears them out
> >> > > > > leaving 3
> >> > > > > blank lines.
> >> > > > >
> >> > > > > There are several of these sections of various lengths and I want to
> >> > > > > add
> >> > > > > total to the bottom of each of them, there are 6 columns in each
> >> > > > > section.
> >> > > > >
> >> > > > > How do I get the formula to add all cells above up to the first blank
> >> > > > > cell?
> >> > > > >
> >> > > > > Thanks in advance
> >> > > > > Rick
> >> > > > >
>
>
>
|
Similar Threads
