Need help with VBA

[Need help with sumif]

I'm not sure If I could explain this easy but let me try


on sheet1 there are name list
on sheet2 there are list of names with their earning in $(Name could appear
more than once)
on sheet3 I want to find all cells that contains (David) & what ever $
that's next to it add it when I click a command button

If this doesn't explain enough maybe I could attach the sample let me know
Thanks !!!!

[JE McGimpsey]

I'm not sure why you can't just use a formula:

=SUMIF(Sheet2!A:A,"David",Sheet2!B:B)

In VBA you could do the same using

Public Sub Commandbutton1_Click()
With Worksheets("Sheet2")
ActiveCell.Value = Application.SumIf( _
.Columns(1).Cells, "David", .Columns(2).Cells)
End With
End Sub




In article <[email protected]>,
Need help with sumif <[email protected]>
wrote:

> I'm not sure If I could explain this easy but let me try
>
>
> on sheet1 there are name list
> on sheet2 there are list of names with their earning in $(Name could appear
> more than once)
> on sheet3 I want to find all cells that contains (David) & what ever $
> that's next to it add it when I click a command button
>
> If this doesn't explain enough maybe I could attach the sample let me know
> Thanks !!!!

[Need help with sumif]

"JE McGimpsey" wrote:

> I'm not sure why you can't just use a formula:
>
> =SUMIF(Sheet2!A:A,"David",Sheet2!B:B)
>
> In VBA you could do the same using
>
> Public Sub Commandbutton1_Click()
> With Worksheets("Sheet2")
> ActiveCell.Value = Application.SumIf( _
> .Columns(1).Cells, "David", .Columns(2).Cells)
> End With
> End Sub
>



Thanks I'm kind of familiar with Excel Formulas like
=SUMIF(Sheet2!A:A,"David",Sheet2!B:B)

I'm Just started to learn VBA
I do understand your VBA code except where

ActiveCell.Value = Application.SumIf( _
..Columns(1).Cells, "David", .Columns(2).Cells)

when I type : application. (program shows me list of what I could put in
but I do not see application.Sumif

and what is underscore means after sumif(

I'm new to this excel community please let me know if I'm going to far
I would like to send you an e-mail so I could better explain what I'm trying
to do
cause you look like you know what you are doing


>
>
>
> In article <[email protected]>,
> Need help with sumif <[email protected]>
> wrote:
>
> > I'm not sure If I could explain this easy but let me try
> >
> >
> > on sheet1 there are name list
> > on sheet2 there are list of names with their earning in $(Name could appear
> > more than once)
> > on sheet3 I want to find all cells that contains (David) & what ever $
> > that's next to it add it when I click a command button
> >
> > If this doesn't explain enough maybe I could attach the sample let me know
> > Thanks !!!!
>

[Dave Peterson]

If you use:

Application.WorksheetFunction, you'll see SumIf as one of the options.

xl97 added the .worksheetfunction. portion. But it isn't required.

I'm sure J.E. uses =sumif() enough that he doesn't need the intellisense for
that worksheet function. (In fact, the intellisense doesn't really help much
with "application.worksheetfunction.sumif(". It just shows you generic
arguments.

The underscore followed by a space is a continuation character that means the
logical line is continued on the next physical line.

This is nice when when people post in newsgroups so that you (as a reader) don't
have to worry how to reassemble the line to fix any syntax errors.

It's also nice so you don't have real long lines in the VBE -- you don't have to
scroll right and left to read that line.

And I'm speaking for lots of people--it's better to keep your follow up
questions in the newsgroups. There are lots of people who can try to help and
there are lots of people who may be interested in the response. (Lots of
lurkers pick up hints/tips this way.)



Need help with sumif wrote:
>
> "JE McGimpsey" wrote:
>
> > I'm not sure why you can't just use a formula:
> >
> > =SUMIF(Sheet2!A:A,"David",Sheet2!B:B)
> >
> > In VBA you could do the same using
> >
> > Public Sub Commandbutton1_Click()
> > With Worksheets("Sheet2")
> > ActiveCell.Value = Application.SumIf( _
> > .Columns(1).Cells, "David", .Columns(2).Cells)
> > End With
> > End Sub
> >
>
> Thanks I'm kind of familiar with Excel Formulas like
> =SUMIF(Sheet2!A:A,"David",Sheet2!B:B)
>
> I'm Just started to learn VBA
> I do understand your VBA code except where
>
> ActiveCell.Value = Application.SumIf( _
> .Columns(1).Cells, "David", .Columns(2).Cells)
>
> when I type : application. (program shows me list of what I could put in
> but I do not see application.Sumif
>
> and what is underscore means after sumif(
>
> I'm new to this excel community please let me know if I'm going to far
> I would like to send you an e-mail so I could better explain what I'm trying
> to do
> cause you look like you know what you are doing
>
> >
> >
> >
> > In article <[email protected]>,
> > Need help with sumif <[email protected]>
> > wrote:
> >
> > > I'm not sure If I could explain this easy but let me try
> > >
> > >
> > > on sheet1 there are name list
> > > on sheet2 there are list of names with their earning in $(Name could appear
> > > more than once)
> > > on sheet3 I want to find all cells that contains (David) & what ever $
> > > that's next to it add it when I click a command button
> > >
> > > If this doesn't explain enough maybe I could attach the sample let me know
> > > Thanks !!!!
> >

--

Dave Peterson

[JE McGimpsey]

In article <[email protected]>,
Dave Peterson <[email protected]> wrote:

> xl97 added the .worksheetfunction. portion. But it isn't required.
>
> I'm sure J.E. uses =sumif() enough that he doesn't need the intellisense for
> that worksheet function. (In fact, the intellisense doesn't really help much
> with "application.worksheetfunction.sumif(". It just shows you generic
> arguments.

Actually, I use MacXL, which doesn't *have* intellisense...

I eschew the WorksheetFunction object because of an old bug with
VLookup, which was not included as a WorksheetFunction method, but
worked as Application.VLookup.

Since it wasn't fixed until XL03 (IIRC), and all the worksheetfunction
methods are duplicated as application methods, I never bother with
worksheetfunction.

[Dave Peterson]

worksheetfunction.vlookup() will still raise an error when there isn't a match
in xl2003.

application.vlookup() will return an error if there isn't a match.

So that behavior hasn't changed in xl2003.

That's too bad that Mac users don't get the intellisense feature. It makes life
a bit easier.

JE McGimpsey wrote:
>
> In article <[email protected]>,
> Dave Peterson <[email protected]> wrote:
>
> > xl97 added the .worksheetfunction. portion. But it isn't required.
> >
> > I'm sure J.E. uses =sumif() enough that he doesn't need the intellisense for
> > that worksheet function. (In fact, the intellisense doesn't really help much
> > with "application.worksheetfunction.sumif(". It just shows you generic
> > arguments.
>
> Actually, I use MacXL, which doesn't *have* intellisense...
>
> I eschew the WorksheetFunction object because of an old bug with
> VLookup, which was not included as a WorksheetFunction method, but
> worked as Application.VLookup.
>
> Since it wasn't fixed until XL03 (IIRC), and all the worksheetfunction
> methods are duplicated as application methods, I never bother with
> worksheetfunction.

--

Dave Peterson

[JE McGimpsey]

In article <[email protected]>,
Dave Peterson <[email protected]> wrote:

> That's too bad that Mac users don't get the intellisense feature. It
> makes life a bit easier.

A bit, but after a while, it's not too hard to remember the syntax. I
wouldn't mind it on the Mac, but not having it, I don't miss it.