Beginner's help

[Guest]

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.

 

[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

 

[Guest]

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]

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

 

[Guest]

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.