Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner's help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner's help
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner's help
in VBA to use a worksheetfunction, prepend it with
Application.WorksheetFunction res = Application.worksheetFunction.Sum(range(range("cal c!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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner's help
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("cal c!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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner's help
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.Rang e( _ 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" wrote in message ... 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("cal c!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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Beginner's help
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.Rang e(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.Rang e( _ 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" wrote in message ... 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("cal c!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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
beginner's tutorial sources | New Users to Excel | |||
Beginner's question | Excel Programming |