Beginner's help

[jello]

Hi,

Forgive me my lack of knowledge - I have not read any books, just picking it
up as I go but can you help me with the following ?

1) I can record a macro to paste in the "ActiveSheet"(copied text from
webpage) that I copied before I started recording, but how do I copy selected
text in VBA?

2) I can do the function allowing me to sum a range of values on another
worksheet:
e.g. =SUM(INDIRECT(calc!H10))
where h10 is a range i.e. worksheet_name!$A$1:A$23

Now with the follwing values:

Value CELL
apples b10
worksheet_name!$A$1:A$23 h10
apples and pears b99
worksheet_name!$B$1:B$23 h99

I want to do SUM(INDIRECT(calc!H99))
when I search a range of cells (one cell would be "apples and pears" - b99)
for "apples" and find the matching row(s), inthis case row 99

When I try to use the sum and indirect functions in the VBEditor it says
they don't exist - presumably because they are excel worksheet functions ?
Anyway are there other functions in VBEditor or do you need to write them
yourself !?

3) Can you recommend any good books/websites on VBA ?

Many many thanks

[Myrna Larson]

Do you know about the macro recorder? Turn it on and do the steps manually.
That's a good way to get started.

On Tue, 25 Jan 2005 20:01:03 -0800, "jello" <[email protected]>
wrote:

> Hi,
>
>Forgive me my lack of knowledge - I have not read any books, just picking it
>up as I go but can you help me with the following ?
>
>1) I can record a macro to paste in the "ActiveSheet"(copied text from
>webpage) that I copied before I started recording, but how do I copy selected
>text in VBA?
>
>2) I can do the function allowing me to sum a range of values on another
>worksheet:
>e.g. =SUM(INDIRECT(calc!H10))
>where h10 is a range i.e. worksheet_name!$A$1:A$23
>
>Now with the follwing values:
>
>Value CELL
>apples b10
>worksheet_name!$A$1:A$23 h10
>apples and pears b99
>worksheet_name!$B$1:B$23 h99
>
>I want to do SUM(INDIRECT(calc!H99))
>when I search a range of cells (one cell would be "apples and pears" - b99)
>for "apples" and find the matching row(s), inthis case row 99
>
>When I try to use the sum and indirect functions in the VBEditor it says
>they don't exist - presumably because they are excel worksheet functions ?
>Anyway are there other functions in VBEditor or do you need to write them
>yourself !?
>
>3) Can you recommend any good books/websites on VBA ?
>
>Many many thanks

[Tom Ogilvy]

in VBA to use a worksheetfunction, prepend it with
Application.WorksheetFunction

res =
Application.worksheetFunction.Sum(range(range("calc!H10").Value).Value)

you don't need indirect as Range can work with a string, same as indirect.


With Match and Vlookup Functions, it is better just to use Application
rather than Application.WorksheetFunction.

set rng = Range("B10") ' Apples

set rng1 = Range("B50:B150") ' range to be searched

res = Application.Match("*" & rng & "*", rng1,0)
if not iserror(res) then
set rng2 = rng1(res)
total =
applicaton.WorksheetFunction.Sum(Range(Cells(rng2.Row,"H").Value).Value)
msgbox Total & " for range: " & Cells(rng2.Row,"H").Value
else
msgbox Range("B10").Value & " was not found"
End if

--
Regards,
Tom Ogilvy




"jello" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Forgive me my lack of knowledge - I have not read any books, just picking
it
> up as I go but can you help me with the following ?
>
> 1) I can record a macro to paste in the "ActiveSheet"(copied text from
> webpage) that I copied before I started recording, but how do I copy
selected
> text in VBA?
>
> 2) I can do the function allowing me to sum a range of values on another
> worksheet:
> e.g. =SUM(INDIRECT(calc!H10))
> where h10 is a range i.e. worksheet_name!$A$1:A$23
>
> Now with the follwing values:
>
> Value CELL
> apples b10
> worksheet_name!$A$1:A$23 h10
> apples and pears b99
> worksheet_name!$B$1:B$23 h99
>
> I want to do SUM(INDIRECT(calc!H99))
> when I search a range of cells (one cell would be "apples and pears" -
b99)
> for "apples" and find the matching row(s), inthis case row 99
>
> When I try to use the sum and indirect functions in the VBEditor it says
> they don't exist - presumably because they are excel worksheet functions ?
> Anyway are there other functions in VBEditor or do you need to write them
> yourself !?
>
> 3) Can you recommend any good books/websites on VBA ?
>
> Many many thanks

[jello]

Thanks for the help Tom thats really useful.

I'm pleased to say I have got it working- well almost !
I find that sometimes I can run it fine in the immediate window but as a
call to a function from a cell formula i.e. =regionTotal("NI") it fall sover
on the debugger on the Totals line where it does the sum.

Now my function is having trouble even running in the immediate window.
Experimented with Long and Integer as types to use but no closer
Can you help again ?

Public Function regionTotal(countryRng As String) As Long

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range

Dim Total As Long

Dim res1 As Variant
Dim res2 As Variant
Dim resFinal As Integer

Set rng = Range("calc!B10") ' Apples
Set rng1 = Range("calc!B33:B65") ' range to be searched
res1 = Application.Match("*" & rng & "*", rng1, 0)
res2 = Application.Match("*" & countryRng & "*", rng1, 0)

resFinal = Application.WorksheetFunction.Max(res1, res2)

If Not IsError(resFinal) Then
Set rng2 = rng1(resFinal)
Total = Application.WorksheetFunction.Sum(Range(Cells(rng2.Row,
"H").Value).Value)
MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
Else
MsgBox Range("calc!B10").Value & " was not found"

End If

regionTotal = Total

End Function

"Tom Ogilvy" wrote:

> in VBA to use a worksheetfunction, prepend it with
> Application.WorksheetFunction
>
> res =
> Application.worksheetFunction.Sum(range(range("calc!H10").Value).Value)
>
> you don't need indirect as Range can work with a string, same as indirect.
>
>
> With Match and Vlookup Functions, it is better just to use Application
> rather than Application.WorksheetFunction.
>
> set rng = Range("B10") ' Apples
>
> set rng1 = Range("B50:B150") ' range to be searched
>
> res = Application.Match("*" & rng & "*", rng1,0)
> if not iserror(res) then
> set rng2 = rng1(res)
> total =
> applicaton.WorksheetFunction.Sum(Range(Cells(rng2.Row,"H").Value).Value)
> msgbox Total & " for range: " & Cells(rng2.Row,"H").Value
> else
> msgbox Range("B10").Value & " was not found"
> End if
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "jello" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Forgive me my lack of knowledge - I have not read any books, just picking
> it
> > up as I go but can you help me with the following ?
> >
> > 1) I can record a macro to paste in the "ActiveSheet"(copied text from
> > webpage) that I copied before I started recording, but how do I copy
> selected
> > text in VBA?
> >
> > 2) I can do the function allowing me to sum a range of values on another
> > worksheet:
> > e.g. =SUM(INDIRECT(calc!H10))
> > where h10 is a range i.e. worksheet_name!$A$1:A$23
> >
> > Now with the follwing values:
> >
> > Value CELL
> > apples b10
> > worksheet_name!$A$1:A$23 h10
> > apples and pears b99
> > worksheet_name!$B$1:B$23 h99
> >
> > I want to do SUM(INDIRECT(calc!H99))
> > when I search a range of cells (one cell would be "apples and pears" -
> b99)
> > for "apples" and find the matching row(s), inthis case row 99
> >
> > When I try to use the sum and indirect functions in the VBEditor it says
> > they don't exist - presumably because they are excel worksheet functions ?
> > Anyway are there other functions in VBEditor or do you need to write them
> > yourself !?
> >
> > 3) Can you recommend any good books/websites on VBA ?
> >
> > Many many thanks
>
>
>

[Tom Ogilvy]

Possibly, although I am not sure why you are doing the two searches.

It needs a range string in column H for the total line to use to specify
what range to sum.

Public Function regionTotal(countryRng As String) As Long

Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range

Dim Total As Long

Dim res1 As Variant
Dim res2 As Variant
Dim resFinal As Integer

Set rng = Range("calc!B10") ' Apples
Set rng1 = Range("calc!B33:B65") ' range to be searched
res1 = Application.Match("*" & rng & "*", rng1, 0)
res2 = Application.Match("*" & countryRng & "*", rng1, 0)

resFinal = Application.WorksheetFunction.Max(res1, res2)

If Not IsError(resFinal) Then
Set rng2 = rng1(resFinal)
Total = Application.WorksheetFunction.Sum(rng2.parent.Range( _
rng2.parent.Cells(rng2.Row, "H").Value).Value)
MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
Else
MsgBox Range("calc!B10").Value & " was not found"

End If

regionTotal = Total

End Function

--
Regards,
Tom Ogilvy


"jello" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the help Tom thats really useful.
>
> I'm pleased to say I have got it working- well almost !
> I find that sometimes I can run it fine in the immediate window but as a
> call to a function from a cell formula i.e. =regionTotal("NI") it fall
sover
> on the debugger on the Totals line where it does the sum.
>
> Now my function is having trouble even running in the immediate window.
> Experimented with Long and Integer as types to use but no closer
> Can you help again ?
>
> Public Function regionTotal(countryRng As String) As Long
>
> Dim rng As Range
> Dim rng1 As Range
> Dim rng2 As Range
>
> Dim Total As Long
>
> Dim res1 As Variant
> Dim res2 As Variant
> Dim resFinal As Integer
>
> Set rng = Range("calc!B10") ' Apples
> Set rng1 = Range("calc!B33:B65") ' range to be searched
> res1 = Application.Match("*" & rng & "*", rng1, 0)
> res2 = Application.Match("*" & countryRng & "*", rng1, 0)
>
> resFinal = Application.WorksheetFunction.Max(res1, res2)
>
> If Not IsError(resFinal) Then
> Set rng2 = rng1(resFinal)
> Total = Application.WorksheetFunction.Sum(Range(Cells(rng2.Row,
> "H").Value).Value)
> MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
> Else
> MsgBox Range("calc!B10").Value & " was not found"
>
> End If
>
> regionTotal = Total
>
> End Function
>
> "Tom Ogilvy" wrote:
>
> > in VBA to use a worksheetfunction, prepend it with
> > Application.WorksheetFunction
> >
> > res =
> > Application.worksheetFunction.Sum(range(range("calc!H10").Value).Value)
> >
> > you don't need indirect as Range can work with a string, same as
indirect.
> >
> >
> > With Match and Vlookup Functions, it is better just to use Application
> > rather than Application.WorksheetFunction.
> >
> > set rng = Range("B10") ' Apples
> >
> > set rng1 = Range("B50:B150") ' range to be searched
> >
> > res = Application.Match("*" & rng & "*", rng1,0)
> > if not iserror(res) then
> > set rng2 = rng1(res)
> > total =
> > applicaton.WorksheetFunction.Sum(Range(Cells(rng2.Row,"H").Value).Value)
> > msgbox Total & " for range: " & Cells(rng2.Row,"H").Value
> > else
> > msgbox Range("B10").Value & " was not found"
> > End if
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "jello" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > Forgive me my lack of knowledge - I have not read any books, just
picking
> > it
> > > up as I go but can you help me with the following ?
> > >
> > > 1) I can record a macro to paste in the "ActiveSheet"(copied text from
> > > webpage) that I copied before I started recording, but how do I copy
> > selected
> > > text in VBA?
> > >
> > > 2) I can do the function allowing me to sum a range of values on
another
> > > worksheet:
> > > e.g. =SUM(INDIRECT(calc!H10))
> > > where h10 is a range i.e. worksheet_name!$A$1:A$23
> > >
> > > Now with the follwing values:
> > >
> > > Value CELL
> > > apples b10
> > > worksheet_name!$A$1:A$23 h10
> > > apples and pears b99
> > > worksheet_name!$B$1:B$23 h99
> > >
> > > I want to do SUM(INDIRECT(calc!H99))
> > > when I search a range of cells (one cell would be "apples and pears" -
> > b99)
> > > for "apples" and find the matching row(s), inthis case row 99
> > >
> > > When I try to use the sum and indirect functions in the VBEditor it
says
> > > they don't exist - presumably because they are excel worksheet
functions ?
> > > Anyway are there other functions in VBEditor or do you need to write
them
> > > yourself !?
> > >
> > > 3) Can you recommend any good books/websites on VBA ?
> > >
> > > Many many thanks
> >
> >
> >

[jello]

Forgive me I probably haven't explained my problem very well:

In my spreadsheet I have the following rows of data:

Row1 (Actually row 10)
Michael
Adams 22 2 23 query!$AE$2:$AE$23 query!$AF$2:$AF$23 query!$M$2:$M$23 query!$R$2:$R$23 query!$T$2:$T$23 query!$AD$2:$AD$23 query!$X$2:$X$23
Row2 (Actually row 33)
Michael Adams,Chaucer Plc
NI 4 2 5 query!$AE$2:$AE$5 query!$AF$2:$AF$5 query!$M$2:$M$5 query!$R$2:$R$5 query!$T$2:$T$5 query!$AD$2:$AD$5 query!$X$2:$X$5

Row3 (Actually row 34)
Michael Adams,Chaucer Plc
SC 2 6 7 query!$AE$6:$AE$7 query!$AF$6:$AF$7 query!$M$6:$M$7 query!$R$6:$R$7 query!$T$6:$T$7 query!$AD$6:$AD$7 query!$X$6:$X$7

Row4 (Actually row 35)
Michael Adams,Chaucer Plc
UK 16 8 23 query!$AE$8:$AE$23 query!$AF$8:$AF$23 query!$M$8:$M$23 query!$R$8:$R$23 query!$T$8:$T$23 query!$AD$8:$AD$23 query!$X$8:$X$23

Now in column H, the first row "Michael Adams" is the range
'query!$M$2:$M$23' and the others three rows representing Michael
Adams,Chaucer Plc UK, Michael Adams,Chaucer Plc NI and Michael Adams,Chaucer
Plc SC are subsets and have corresponding ranges 'query!$M$2:$M$5',
'query!$M$6:$M$7' , 'query!$M$8:$M$23'
I was performing 2 searches one to find michael adams (res1=1), UK(I passed
this in to the function as get res2 = 3 since this is 3rd row of searched
range), then I take the max - I know this is bad is there an easier way to
find Michael Adams and UK ?

Anyway I get rng2 = rng1(3) with value = Michael Adams,Chaucer Plc UK but I
want to total across the range specified in that row's column H - in this
case the cell H35 which contains the value 'query!$M$8:$M$23'

When I run the totals line:
Total =
Application.WorksheetFunction.Sum(rng2.Parent.Range(rng2.Parent.Cells(rng2.Row, "H").Value).Value)

it falls over and the locals windows is blank.

Can you help I am not sure what the above totals line syntax means ?
Can you explain what Cells(rng2.Row, "H").Value means ?

Thanks very much again,
Kind regards,
Rob.

"Tom Ogilvy" wrote:

> Possibly, although I am not sure why you are doing the two searches.
>
> It needs a range string in column H for the total line to use to specify
> what range to sum.
>
> Public Function regionTotal(countryRng As String) As Long
>
> Dim rng As Range
> Dim rng1 As Range
> Dim rng2 As Range
>
> Dim Total As Long
>
> Dim res1 As Variant
> Dim res2 As Variant
> Dim resFinal As Integer
>
> Set rng = Range("calc!B10") ' Apples
> Set rng1 = Range("calc!B33:B65") ' range to be searched
> res1 = Application.Match("*" & rng & "*", rng1, 0)
> res2 = Application.Match("*" & countryRng & "*", rng1, 0)
>
> resFinal = Application.WorksheetFunction.Max(res1, res2)
>
> If Not IsError(resFinal) Then
> Set rng2 = rng1(resFinal)
> Total = Application.WorksheetFunction.Sum(rng2.parent.Range( _
> rng2.parent.Cells(rng2.Row, "H").Value).Value)
> MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
> Else
> MsgBox Range("calc!B10").Value & " was not found"
>
> End If
>
> regionTotal = Total
>
> End Function
>
> --
> Regards,
> Tom Ogilvy
>
>
> "jello" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the help Tom thats really useful.
> >
> > I'm pleased to say I have got it working- well almost !
> > I find that sometimes I can run it fine in the immediate window but as a
> > call to a function from a cell formula i.e. =regionTotal("NI") it fall
> sover
> > on the debugger on the Totals line where it does the sum.
> >
> > Now my function is having trouble even running in the immediate window.
> > Experimented with Long and Integer as types to use but no closer
> > Can you help again ?
> >
> > Public Function regionTotal(countryRng As String) As Long
> >
> > Dim rng As Range
> > Dim rng1 As Range
> > Dim rng2 As Range
> >
> > Dim Total As Long
> >
> > Dim res1 As Variant
> > Dim res2 As Variant
> > Dim resFinal As Integer
> >
> > Set rng = Range("calc!B10") ' Apples
> > Set rng1 = Range("calc!B33:B65") ' range to be searched
> > res1 = Application.Match("*" & rng & "*", rng1, 0)
> > res2 = Application.Match("*" & countryRng & "*", rng1, 0)
> >
> > resFinal = Application.WorksheetFunction.Max(res1, res2)
> >
> > If Not IsError(resFinal) Then
> > Set rng2 = rng1(resFinal)
> > Total = Application.WorksheetFunction.Sum(Range(Cells(rng2.Row,
> > "H").Value).Value)
> > MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
> > Else
> > MsgBox Range("calc!B10").Value & " was not found"
> >
> > End If
> >
> > regionTotal = Total
> >
> > End Function
> >
> > "Tom Ogilvy" wrote:
> >
> > > in VBA to use a worksheetfunction, prepend it with
> > > Application.WorksheetFunction
> > >
> > > res =
> > > Application.worksheetFunction.Sum(range(range("calc!H10").Value).Value)
> > >
> > > you don't need indirect as Range can work with a string, same as
> indirect.
> > >
> > >
> > > With Match and Vlookup Functions, it is better just to use Application
> > > rather than Application.WorksheetFunction.
> > >
> > > set rng = Range("B10") ' Apples
> > >
> > > set rng1 = Range("B50:B150") ' range to be searched
> > >
> > > res = Application.Match("*" & rng & "*", rng1,0)
> > > if not iserror(res) then
> > > set rng2 = rng1(res)
> > > total =
> > > applicaton.WorksheetFunction.Sum(Range(Cells(rng2.Row,"H").Value).Value)
> > > msgbox Total & " for range: " & Cells(rng2.Row,"H").Value
> > > else
> > > msgbox Range("B10").Value & " was not found"
> > > End if
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "jello" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi,
> > > >
> > > > Forgive me my lack of knowledge - I have not read any books, just
> picking
> > > it
> > > > up as I go but can you help me with the following ?
> > > >
> > > > 1) I can record a macro to paste in the "ActiveSheet"(copied text from
> > > > webpage) that I copied before I started recording, but how do I copy
> > > selected
> > > > text in VBA?
> > > >
> > > > 2) I can do the function allowing me to sum a range of values on
> another
> > > > worksheet:
> > > > e.g. =SUM(INDIRECT(calc!H10))
> > > > where h10 is a range i.e. worksheet_name!$A$1:A$23
> > > >
> > > > Now with the follwing values:
> > > >
> > > > Value CELL
> > > > apples b10
> > > > worksheet_name!$A$1:A$23 h10
> > > > apples and pears b99
> > > > worksheet_name!$B$1:B$23 h99
> > > >
> > > > I want to do SUM(INDIRECT(calc!H99))
> > > > when I search a range of cells (one cell would be "apples and pears" -
> > > b99)
> > > > for "apples" and find the matching row(s), inthis case row 99
> > > >
> > > > When I try to use the sum and indirect functions in the VBEditor it
> says
> > > > they don't exist - presumably because they are excel worksheet
> functions ?
> > > > Anyway are there other functions in VBEditor or do you need to write
> them
> > > > yourself !?
> > > >
> > > > 3) Can you recommend any good books/websites on VBA ?
> > > >
> > > > Many many thanks
> > >
> > >
> > >
>
>
>