Macro to Sum Two Adjacent Columns as per Text in previous Column

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Reha » Thu, 12 Aug 2010 21:02:27


Hello All,
I found this macro from the Groups which sums Columns according to the
Value of 'x'.

Sub test() ' Macro to Sum Columns
Dim lrow As Long, x As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")
lrow = ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
' sum the columns - change x according to your needs
For x = 1 To 2
lrow = ws.Cells(Rows.Count, x).End(xlUp).Offset(1).Row
If Application.Sum(Range(ws.Cells(2, x),
ws.Cells(lrow, x))) > 0 Then
With ws.Cells(lrow, x)
.Formula = "=Sum(" & Range(ws.Cells(2,
x), ws.Cells(lrow - 1, x)).Address(0, 0) & ")"
.NumberFormat = "#,###"
End With
End If
Next
End Sub

My requirement is to search for a text appearing in Sheet1 and Sum
adjacent Columns.
No. of Rows would be varying.

For eg: lookup the text 'Grade' and Sum adjacent two columns.
If the text 'Grade' appears in
Column B then Sum Col C and D...
if in F then Sum Columns G and H...
if in T then Sum Columns U and V

and so on...

Any help would be greatly appreciate.
Thanks and regards
Reha
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Don Guille » Thu, 12 Aug 2010 21:46:33


> ' sum the columns - change x according to your needs> > For x = > To 2
> lrow = ws.Cells(Rows.Count, x).End(xlUp).O>fset(1).Row
> If Application.Sum(>ange(ws.Cells(2, x), >> ws.Cell>(lrow, x))) > 0 Then
> gt;With ws.Cells(lrow, x)
> > .Formula = "=Sum(" & Range(ws.Cells(2,
> > .Nu>berFormat = ">,###"
> gt;> End With
> End If
> > Next
> End Sub
> >> My requirement is to search fo> a>text appearing in Sheet1 and Sum
> adjacent Columns.
> No. of >ows would be varying.
>
> For e>: lookup the text 'Grade' and Sum a>jacent two columns.
> If the text 'Gr>de' appears in
> Column B then Sum>Co> C and D...
> >f >n F then Sum Columns G and H...
> if in>T then Sum Columns U >nd V
>
> and so on...
>
> Any help would be greatly appreciate.
> Thanks and regards
> Reha

Try this to find the last row, find the text in row 1 and sum columns
Sub SumColumnsSAS()
'SalesAid Software XXXX@XXXXX.COM
Dim lr As Long
Dim i
Dim mc As Long
lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
For i = 1 To 2
mc = Rows(1).Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False).Column - 1
'MsgBox mc
Cells(lr, mc + i) = _
Application.Sum(Range(Cells(1, mc + i), Cells(lr, mc + i)))
Next i
End Sub

 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Reha » Fri, 13 Aug 2010 01:23:12

On Aug 11, 4:46m, Don Guillett Excel MVP < XXXX@XXXXX.COM >


> > ' sum the columns - change x according to your needs> > > For x = > >o 2
> > lrow = ws.Cells(Rows.Count, x).End(xlUp).O>f>et(1).Row
> > If Application.Sum(>a>ge(ws.Cells(2, x),
>>> ws.Cell>(>row, x))) > 0 Then
> > gt;gt;With ws.Cells(lrow, x)
> > > > .Formula = "=Sum(" & Range(ws.Cells(2,
> > >), ws.Cells(lrow - 1, x)).Address(0, 0) &>">"
> > > > .Number>o>mat = "#,###"> > > gt;> > End With
> > End If
> > Ne>t> > > End Sub
>
> >>M> requirement is to search for a >ex> >ppearing in Sheet1 and Sum
> > adjacent Columns.
> > No. of Ro>s>would be varying.
>
> > For eg:>l>okup the text 'Grade' and Sum adjac>n> two columns.
> > If the text 'Grade'>a>pears in
> > Column B then Sum Col>C >n> D...
> > if i> F>t>en Sum Columns G and H...
> > if in T t>e> Sum Columns U and V >>> > > an> s> on...
>
> > Any help would be greatly appreciate.
> > Thanks and reg>rds
> > Reha
>
> Tr> this to find the last row, find the text>in row 1 and sum >olumns
> 'SalesAid Software XXXX@XXXXX.COM
> Dim lr As >ong
> Dim i
> Dim mc As Long
> >r = Cells.Fin>("*", Cells(Rows.>ount, Columns.Count) _
> , , , xlByRows, xlPrevious).R>w
> 'MsgBox lr
> For i = 1 To 2
> mc = Rows>1).Find(What:="Grade", LookIn:=xlValues> _
> LookAt:=xlPart, SearchOrder:=xlByColu>ns, _
> Sear>hDirection:=xlNext, Matc>Case _
> :=False, SearchFormat:=False).Column - 1
> 'MsgBox >c
> Cell>(lr, mc + i) = _
> Applicatio>.S>m(Range(Cells(1, mc + i), Cells(lr, mc + i)))
> Next i
> End Sub- Hide quoted text -
>
> - Show quoted text -

Hello,
My requirement is to look up for 'Grade' in any Row and put the sum
formula in the next two columns.
Let me p<<<up an example

Primary Class 1
1 2
1 2
1 3
1 2
1 2
Grade 5 11 <<< The macro puts up value here...I need sum
formula in both the columns.

As you can see above ...Grade can appear in any Row..

Hope I am clear now
Thanks
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Don Guille » Fri, 13 Aug 2010 04:04:07

n Aug 11, 11:23m, Reha < XXXX@XXXXX.COM > wrote:
> > > lrow = ws.Cells(Rows.Count, x).End(xlUp).O>f>e>(1).Row
> > > If Application.Sum(>a>g>(ws.Cells(2, x),
> >>> ws.Cell>(>r>w, x))) > 0 Then
> > > gt;gt;gt;With ws.Cells(lrow, x)
> > > > > > .Formula = "=Sum(" & Range(ws.Cells(2,
> > > >)> ws.Cells(lrow - 1, x)).Address(0, 0) & ">"> > > > > > gt;> > > End With
> > > End If
> > > Next
>
> > > M> >e>uirement is to search for a text>ap>e>r>ng in Sheet1 and Sum
> > > adjacent Columns.
> > > No. of Rows>w>u>d be varying.
>
> > > For eg: l>o>u> the text 'Grade' and Sum adjacent >w> >olumns.
> > > If the text 'Grade' app>a>s>in
> > > Column B then Sum Col C a>d >.>.> > > > if in F >he> >u> Columns G and H...
> > > if in T then >u> >olumns U and V
>
> > > >nd so o>..> >>
> > > Any help would be greatly appreciate.
> > > Thanks and regards> >
> > Try >h>s to find the last row, find the text in >o> 1 and sum column> >> > Sub >u>ColumnsSAS()
> >>'>alesAid Software XXXX@XXXXX.COM
> > Dim lr As Long
> > Dim mc As Long
> > lr>=>Cells.Find("*>,>Cells(Rows.Count,>C>lumns.Count) _
> > , , , xlByRows, xlPrevious).Row
> > >MsgBox lr
> > For i = 1 To 2
> > mc = Rows(1>.>ind(What:="Grade", LookIn:=xlValues, _ >>>> LookAt:=xlPart, SearchOrder:=xlByColumns,>_> > > SearchDi>e>tion:=xlNext, MatchCase > >> > :=False, SearchFormat:=False).Column - 1
> > 'MsgBox mc
> > Application.Su>(R>n>e(Cells(1, mc + i), Cel>s(>r, mc + i>))
> > Next i
> > End Sub- Hide quoted text -
>
> > - Show quoted >ext -
>
> Hello,
> My requiremen> is to look up for 'Grade' >n >ny Row and put the sum> > formula in>the next two >olumns.
> Le> me put up an>example
>
>>Primary Class 1
> 1 << > 1 2 >> 1 3
> > gt;2
> 1 2
> Grade 5 11 gt;<<>The macro puts up valu> here...I need sum
> formula>in>both the columns.
>
> As you can see above ...Grade can appear in any Row..
>
> Hope I am clear now
> Thanks- Hide quoted text -
>
> - Show quoted text -

Sub SumColumns1SAS()
'SalesAid Software XXXX@XXXXX.COM
Dim mc As Range
Set mc = Cells.Find(What:="Grade", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase _
:=False, SearchFormat:=False)
If Not mc Is Nothing Then
End If
mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
"=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
End Sub
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Reha » Fri, 13 Aug 2010 04:46:27

n Aug 11, 11:04m, Don Guillett Excel MVP < XXXX@XXXXX.COM >
wrote:
> > > > lrow = ws.Cells(Rows.Count, x).End(xlUp).O>f>e>(>).Row
> > > > If Application.Sum(>a>g>(>s.Cells(2, x),
> > >>> ws.Cell>(>r>w> x))) > 0 Then
> > > > gt;gt;gt;gt;With ws.Cells(lrow, x)
> > > > > > > > .Formula = "=Sum(" & Range(ws.Cells(2,
> > > > >)> >s.Cells(lrow - 1, x)).Address(0, 0) & ")"> > > > > > > > > gt;> >n> >ith
> > > > End If
> > > > Next
> > > > >n> Sub
>
> > > > My >e>u>r>ment is to search for a text app>ar>n> >n>Sheet1 and Sum
> > > > adjacent Columns.
> > > > No. of Rows w>u>d>b> varying.
>
> > > > For eg: loo>u> >h> text 'Grade' and Sum adjacent two >o>u>n>.
> > > > If the text 'Grade' appears>i> >>>> > > Column B then Sum Col C and D>..> > > > > if in T then Sum >o>u>n> U and V
>
> > > > >n> >o>on...
> > > > Thanks and regards
> > > > Reha
>
> > > Try th>s>t> find the last row, find the text in row > >n> sum columns
> >>>>S>b SumCol>m>s>AS()
> > > 'Sale>A>d>Software XXXX@XXXXX.COM
> > > Dim lr As Long
> > > >i> >
> > > Dim mc As Long
> > > lr =>C>l>s.Find("*", C>l>s>Rows.Count, Colum>s>C>unt) _
> > > , , , xlByRows, xlPrevious).Row
> > > 'M>g>o> lr
> > > For i = 1 To 2
> > > mc = Rows(1).>i>d>What:="Grade", LookIn:=xlValues, _
> >>>>L>okAt:=xlPart, SearchOrder:=xlByColumns, _
> >>>>:>False, SearchFormat:=False).Column - 1
> > > 'MsgBox mc
> > > >e>ls(lr, mc>+>i> = _
> > > Application.Sum(Ra>ge>C>l>s(1, mc + i), Cells(lr,>mc>+>i)))
> >>>>Next i
> > > End Sub- Hide quoted text -
>
> > > - Show quoted text>-> >
> > Hello,
> > My requirement >s>to look up for 'Grade' in a>y >o> and put the sum
> > >o>mula in the n>x> two columns.> > > 1 2
> > 1<< 2
> > gt;gt;1 3
> > 1 > 2> > > Grade 5 11 << >he>m>cro puts up value here>.>I need sum
> > formula in bo>h >h> columns.
>
> > As yo> c>n see above ...Grade ca> appear in any Row..
>
> > Hope I am cl>ar now
> > Thanks> Hide quoted text -
>
> > - Show quoted text -
>
> Su> SumColumns1SAS()
> 'SalesAid Software dguill> XXXX@XXXXX.COM
> Dim mc As Range
> Set >c = Cells.Find(What:="Grade", Lo>kIn:=xlValues, _
> LookAt:=>lPart, Se>rchOrder:=xlByColumns, _
> SearchDirection:=>lNext, MatchCase _
> :=False, SearchForm>t:=False)
> If Not mc Is Noth>ng>Then
> End If
> mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _

Thanks a lot for your help
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Reha » Fri, 13 Aug 2010 04:51:22

n Aug 11, 11:04m, Don Guillett Excel MVP < XXXX@XXXXX.COM >
wrote:
> > > > lrow = ws.Cells(Rows.Count, x).End(xlUp).O>f>e>(>).Row
> > > > If Application.Sum(>a>g>(>s.Cells(2, x),
> > >>> ws.Cell>(>r>w> x))) > 0 Then
> > > > gt;gt;gt;gt;With ws.Cells(lrow, x)
> > > > > > > > .Formula = "=Sum(" & Range(ws.Cells(2,
> > > > >)> >s.Cells(lrow - 1, x)).Address(0, 0) & ")"> > > > > > > > > gt;> >n> >ith
> > > > End If
> > > > Next
> > > > >n> Sub
>
> > > > My >e>u>r>ment is to search for a text app>ar>n> >n>Sheet1 and Sum
> > > > adjacent Columns.
> > > > No. of Rows w>u>d>b> varying.
>
> > > > For eg: loo>u> >h> text 'Grade' and Sum adjacent two >o>u>n>.
> > > > If the text 'Grade' appears>i> >>>> > > Column B then Sum Col C and D>..> > > > > if in T then Sum >o>u>n> U and V
>
> > > > >n> >o>on...
> > > > Thanks and regards
> > > > Reha
>
> > > Try th>s>t> find the last row, find the text in row > >n> sum columns
> >>>>S>b SumCol>m>s>AS()
> > > 'Sale>A>d>Software XXXX@XXXXX.COM
> > > Dim lr As Long
> > > >i> >
> > > Dim mc As Long
> > > lr =>C>l>s.Find("*", C>l>s>Rows.Count, Colum>s>C>unt) _
> > > , , , xlByRows, xlPrevious).Row
> > > 'M>g>o> lr
> > > For i = 1 To 2
> > > mc = Rows(1).>i>d>What:="Grade", LookIn:=xlValues, _
> >>>>L>okAt:=xlPart, SearchOrder:=xlByColumns, _
> >>>>:>False, SearchFormat:=False).Column - 1
> > > 'MsgBox mc
> > > >e>ls(lr, mc>+>i> = _
> > > Application.Sum(Ra>ge>C>l>s(1, mc + i), Cells(lr,>mc>+>i)))
> >>>>Next i
> > > End Sub- Hide quoted text -
>
> > > - Show quoted text>-> >
> > Hello,
> > My requirement >s>to look up for 'Grade' in a>y >o> and put the sum
> > >o>mula in the n>x> two columns.> > > 1 2
> > 1<< 2
> > gt;gt;1 3
> > 1 > 2> > > Grade 5 11 << >he>m>cro puts up value here>.>I need sum
> > formula in bo>h >h> columns.
>
> > As yo> c>n see above ...Grade ca> appear in any Row..
>
> > Hope I am cl>ar now
> > Thanks> Hide quoted text -
>
> > - Show quoted text -
>
> Su> SumColumns1SAS()
> 'SalesAid Software dguill> XXXX@XXXXX.COM
> Dim mc As Range
> Set >c = Cells.Find(What:="Grade", Lo>kIn:=xlValues, _
> LookAt:=>lPart, Se>rchOrder:=xlByColumns, _
> SearchDirection:=>lNext, MatchCase _
> :=False, SearchForm>t:=False)
> If Not mc Is Noth>ng>Then
> End If
> mc.Offset(, 1).Resize(, 2).FormulaR1C1 = _
> "=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
> End Sub- Hide quoted text -
>
> - Show quoted text -

To add to my previous reply.
'A small change would be more beneficial to my requirement.
If in the macro I can specify the starting Row that would really
helpful.
Now the macro takes up everything from Row 1 till the end
If the data heading starts from Row 4 then the macro should calculate
from Row 5 till the end.
I can change the row number as per my requirement whenever required
Hope I am clear
Thanks once again
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Don Guille » Fri, 13 Aug 2010 05:37:40

n Aug 11, 2:51m, Reha < XXXX@XXXXX.COM > wrote:
> > > > > If Application.Sum(>a>g>(>s>Cells(2, x),
> > > >>> ws.Cell>(>r>w> >))) > 0 Then
> > > > > gt;gt;gt;gt;gt;With ws.Cells(lrow, x)
> > > > > > > > > > .Formula = "=Sum(" & Range(ws.Cells(2,
> > > > > >)> >s>Cells(lrow - 1, x)).Address(0, 0) & ")"
> > > > > Next
> > > > > >n> >u>
>
> > > > > My re>u>r>m>n> is to search for a text appeari>g >n>S>e>t> and Sum
> > > > > adjacent Columns.
> > > > > No. of Rows wou>d>b> >a>ying.
>
> > > > > For eg: looku> >h> >e>t 'Grade' and Sum adjacent two colu>n>.> > > > > > if in T then Sum Colu>n> > >n> V
>
> > > > > and >o>o>.>.> >
> >>> > > >n> help would be greatly appreciate.
> > > > > Thanks and regards
> > > > > >e>a
>
> > > > Try this>t> >i>d the last row, find the text in row 1 an> >u> >olumns
> > > > S>b>S>m>olumnsSA>(> >>>> > > 'SalesAid S>f>w>r> XXXX@XXXXX.COM
> > > > Dim lr As Long
> > > > Dim > >>>>>> > Dim mc As Long
> > > > lr = C>l>s>F>nd("*", Cells>R>w>.>ount, Columns.Cou>t> > >> > > > , , , xlByRows, xlPrevious).Row
> > > > 'MsgBo> >r> > > > > mc = Rows(1).Fi>d>W>a>:="Grade", LookIn:=xlValues, _
> > > >>L>o>A>:=xlPart, SearchOrder:=xlByColumns, _
> > > > >e>rchDirection:>x>N>x>, MatchCase _
> > > > :>F>l>e> SearchFormat:=False).Column - 1
> > > > 'MsgBox mc
> > > > >e>l>(>r, mc + i> > > >> > > > Application.Sum(Range(>el>s>1> >c + i), Cells(lr, mc + >))> >>>> > > Nex> > >> > > > End Sub- Hide quoted text -
>
> > > > - Show quoted text -
> > > My requirement is>t> >ook up for 'Grade' in any R>w >n> >ut the sum
> > > form>l> >n the next tw> >o>umns.
> > > >e> >e put up an e>a>p>e
>
> > > gt;gt;gt;Primary Cla>s>1> > > > 1 2
> > > 1 2
> > > <<1>gt;gt;3
> > > 1 2
>>> > > > 1 2
> > > Grade 5 11 << The >ac>o>p>ts up value here...I n>e> >um
> > > formula in both the>co>u>n>.
>
> > > As you can >ee>a>ove ...Grade can appear>i> any Row..
>
> > > Hope I am clear now >>>> > Thanks- Hide q>o>ed text -
>
> > > - Show quoted text -
>
> > Sub SumC>l>mns1SAS()
> > 'SalesAid Software dguill...@gm>i>.com
> > Dim mc As Range
> > Set mc =>C>lls.Find(What:="Grade", LookIn:=>l>alues, _
> > LookAt:=xlPart> >earchOrde>:>xlByColumns, _
> > SearchDirection:=xlNext, >a>chCase _
> > :=False, SearchFormat:=Fals>)> > > If Not mc Is Nothing Then> >>>>End If
> > mc.Offset(,>1)>Resize(, 2).FormulaR1C1 = _
> > "=SUM(R[-" & mc.Row - 1 & "]C:R[-1]C)"
> > End Sub- Hide quo>ed text -
>
> > - Show quoted text -
>
> To add to my previous >eply.
> 'A>small change would be more beneficial to my requirement.
> >f in the macro I can specify the starting Row that would really
> helpf>l.
> Now the macro takes u> everything from Row 1 till the end
> If the data heading starts from>Row 4 then the mac>o should calculate
> from Row 5 til
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Reha » Fri, 13 Aug 2010 11:52:52

n Aug 12, 12:37m, Don Guillett Excel MVP < XXXX@XXXXX.COM >
wrote:
> > > > > > If Application.Sum(R>n>e>w>.>C>lls(2, x),
> > > > >>> ws.Cell>(>r>w> >)>) > 0 Then
> > > > > > gt;gt;gt;gt;gt;gt;With ws.Cells(lrow, x)
> > > > > > > > > > > >Formula = "=Sum(" & Range(ws.Cells(2,
> > > > > > >)> >s>C>lls(lrow - 1, x)).Address(0, 0) & ")"
> > > > > > > > > > >N>m>erFormat = "#,###"> > > > > > > Next
> > > > > > >n> >u> >>> > > > > > > My requ>r>m>n> >s>to search for a text appearing i> S>e>t> >n> >um
> > > > > > adjacent Columns.
> > > > > > No. of Rows would>b> >a>y>n>.
>
> > > > > > For eg: lookup >h> >e>t>'>rade' and Sum adjacent two columns.> > >>>>>>>>>>C>lumn B then Sum Col C and D...
> >>> > > > >f>i> F then Sum Col>mn> > >n> >.>.
> > > > > > if in T then Sum Columns > >n> > >>> > > > > > > and so o>.>.> > > > > > > Thanks and regards
> > > > > > >e>a> > > > > > Try this t> >i>d>t>e last row, find the text in row 1 and su> >o>u>n>
> > > > > Sub S>m>o>u>n>SAS()
>>>>>>>>>>'SalesAid Softwar> >g>i>l> XXXX@XXXXX.COM
> > > > > Dim lr As Long
> > > > > Dim i
>>>>>>>>>>Dim mc As Long
> > > > > lr = Cel>s>F>n>(>*", Cells(Row>.>o>n>,>Columns.Count) _ >>>>>>>>>> , , , xlByRows, xlPrevious).Row
> > > > > 'MsgBox lr> > > > > > mc = Rows(1).Find>W>a>:>">rade", LookIn:=xlValues, _
> > > > > L>o>A>:>x>Part, SearchOrder:=xlByColumns, _
> > > > > >e>r>h>irection:=xlN>x>,>M>t>hCase _
> > > > > :=Fal>e> >e>r>hFormat:=False).Column - 1
> > > > > 'MsgBox mc
> > > > > Ce>l>(>r> >c + i) = > >>>>>>>> > Application.Sum(Range(Cell>(1> >c>+>i>, Cells(lr, mc + i)))
>>>>>>>>> End Sub- Hide quoted text -
>
> > > > > - Show quoted text -
>
>>>>>>>>Hello,
> > > > My requirement is t> >o>k>up for 'Grade' in any Row a>d >u> >h> sum
> > > > formula >n>t>e>next two colu>n>.> > > > > > > 1 2
> > > > 1 2
> > > > 1 <<;3> > > > >>> > > > 2
> > > > Grade 5 11 << The macr> p>t> >p>value here...I need su> >>>>>> > formula in both the colum>s.> >
> > > > Hope I am clear now
> > > >>T>a>ks- Hide quoted te>t>-> >
> > > > - Show quoted text -
>
> > > Sub SumColumns>S>S>)
> > > 'SalesAid Software XXXX@XXXXX.COM > >>>> > Dim mc As Range
> > > Set mc = Cel>s>F>nd(What:="Grade", LookIn:=xlValu>s> >
> > > LookAt:=xlPart, Sear>h>r>er:=xlByC>l>m>s, _
> > > SearchDirection:=xlNext, MatchCas> > >> > > :=False, SearchFormat:=False)
> > > >f>Not mc Is Nothing Then
> > > >nd>I> >> > > mc.Offset(, 1).Re>iz>(> 2).FormulaR1C1 = _
> > > "=SU>(>[-" & mc.Row - 1 & "]C:R[-1]C)"
> > > End Sub- Hide quoted te>t>-
>
> > > - Show quoted text -
>
> > To add to my previous repl>.> > > 'A sma>l>change would be more be
 
 
 

Macro to Sum Two Adjacent Columns as per Text in previous Column

Post by Don Guille » Sat, 14 Aug 2010 07:07:51

n Aug 11, 9:52m, Reha < XXXX@XXXXX.COM > wrote:
> > > > > > > If Application.Sum(>a>g>(>s>C>l>s(2, x),
> > > > > >>> ws.Cell>(>r>w> >)>)>> 0 Then
> > > > > > > gt;gt;gt;gt;gt;gt;W>th ws.Cells(lrow, x)
> > > > > > > > > > > > >F>rmula = "=Sum(" & Range(ws.Cells(2,
> > > > > > > >)> >s>C>l>s(lrow - 1, x)).Address(0, 0) & ")"
> > > > > > > > > > > >N>m>e>F>rmat = "#,###"
> > > > > > > > > > > > > >n> With
> >>> > > > > > > > End If
> > > > > > > Next
> > > > > > > En> >u> >>> > > > > > > > No. of Rows would b> >a>y>n>.> > > > > > > > For eg: lookup th> >e>t>'>r>d>' and Sum adjacent two columns.
> > > > > > > >f the text 'Grade' appears in
> > > >>>>>>>>C>l>m> B then Sum Col C and D...
> > > >>> > > >f>i> > >hen Sum Columns>G >n> >.>.> > > > > > > > Thanks and regards
> > > > > > > >e>a> > > > >>>>>>'>a>e>Aid Software dgui>l>.>@>m>i>.com
> > > > > > Dim lr As Long
> > > > > > Dim i
> > >>>>>>>>D>m>mc As Long
> > > > > > lr = Cells>F>n>(>*>,>Cells(Rows.Co>n>,>C>l>m>s.Count) _
> > >>>>>>>>,>,>, xlByRows, xlPrevious).Row
> > > > > > 'MsgBox lr
> > > > > > >or i = 1 To 2
> > > > > > mc = Rows(1).Find(W>a>:>">r>d>", LookIn:=xlValues, _
> > > > > > Loo>A>:>x>P>r>, SearchOrder:=xlByColumns, _
> > > > > > >e>r>h>i>e>tion:=xlNext,>M>t>h>a>e>_
> > > > > > :=False, >e>r>h>o>m>t:=False).Column - 1
> > > > > > 'MsgBox mc
> > > > > > Cell>(>r> >c>+>i) = _
>>>>>>>>>>>>Application.Sum(Range(Cells(1,>mc>+>i>,>C>l>s(lr, mc + i)))
> > > > >>>>N>x> >
> > > >>>>>>E>d>Sub- Hide quoted text -
>
> > > > > > - Show quoted text -
>
> > >>>>>>H>l>o,
> > > > > My requirement is to >o>k>u> >or 'Grade' in any Row and p>t >h> >u> >> > > > > formula in t>e>n>x> >wo columns.
>
> > >>>>>>gt;gt;Primary C>a>s>1> > > > > > 1 2
> > > > > 1 3
> > > ><<gt; > 1 2
> > > > > gt;> > > > > 2
> > > > > Grade 5 11 << The macro pu>s >p>v>l>e>here...I need sum
> >>>>>>>>f>rmula in both the columns.
>> >>>>>>>>>>As you can see above ..>Gr>d> >a> appear in any Row..
>> > > > > > Thank>->H>d> quoted text -
> >>>>>>>> > - Show quoted text -
>
> > > > Sub SumColumns1SAS()> > >>>>>>D>m mc As Range
> > > > Set mc = Cells.F>n>(>h>t:="Grade", LookIn:=xlValues, _ >>>>>>>> LookAt:=xlPart, SearchOrde>:>x>B>Columns, > >>>>>> > SearchDirection:=xlNext, MatchCase _
> >>>>>>:>False, SearchFormat:=False)
> > > > If N>t>m> >s Nothing Then
> > > > End If> >>>>>>>>mc.Offset(, 1).Resize(,>2)>F>r>ulaR1C1 = _
> > > > "=SUM(R[-">&>m>.Row - 1 & "]C:R[-1]C)"
> > > > End Sub- Hide quoted text