Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
Hi All
I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
Sub CommandButton1_Click()
Dim v(1 to 3), rng as Range, rng1 as Range set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) for i = 1 to 3 rng1.offset(0,i).Value = Application.Large(v,i) next Else msgbox Range("J1").Value & " was not found" End if End sub Would be a guess at what you want. -- Regards, Tom Ogilvy "Hazel" wrote: Hi All I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
Hi Tom
Nearly there, instead of going down Column J and I'm assuming that because I have entered the letter A 3 times in Column J that I want to replace -- it is offset to next Columns K,L,M where it enters the values on the same row of these columns. Any suggestions how to over come this would be much appreciated -- Many thanks hazel "Tom Ogilvy" wrote: Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) for i = 1 to 3 rng1.offset(0,i).Value = Application.Large(v,i) next Else msgbox Range("J1").Value & " was not found" End if End sub Would be a guess at what you want. -- Regards, Tom Ogilvy "Hazel" wrote: Hi All I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
So you will have A entered 3 times in column J. so is the percentage to be
entered to the right of A or overwrite A. Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range Dim sAddr as String, ii as Long set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) ii = 1 sAddr = rng1.Address do rng1.offset(0,1).Value = Application.Large(v,ii) set rng1 = rng.findnext(rng1) ii = ii + 1 Loop until rng.Address = sAddr or ii 3 Else msgbox Range("J1").Value & " was not found" End if End sub -- Regards, Tom Ogilvy "Hazel" wrote: Hi Tom Nearly there, instead of going down Column J and I'm assuming that because I have entered the letter A 3 times in Column J that I want to replace -- it is offset to next Columns K,L,M where it enters the values on the same row of these columns. Any suggestions how to over come this would be much appreciated -- Many thanks hazel "Tom Ogilvy" wrote: Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) for i = 1 to 3 rng1.offset(0,i).Value = Application.Large(v,i) next Else msgbox Range("J1").Value & " was not found" End if End sub Would be a guess at what you want. -- Regards, Tom Ogilvy "Hazel" wrote: Hi All I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
Hi Tom
You are a very astute man, the values from the Text Boxes are to overwrite the letters A in Column J -- I changed the offset to 0 and the Text Box values were placed in Column J however they filled in the blank cells between the letters A is there a way round this problem?? Thank you for your time in helping me -- Many thanks hazel "Tom Ogilvy" wrote: So you will have A entered 3 times in column J. so is the percentage to be entered to the right of A or overwrite A. Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range Dim sAddr as String, ii as Long set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) ii = 1 sAddr = rng1.Address do rng1.offset(0,1).Value = Application.Large(v,ii) set rng1 = rng.findnext(rng1) ii = ii + 1 Loop until rng.Address = sAddr or ii 3 Else msgbox Range("J1").Value & " was not found" End if End sub -- Regards, Tom Ogilvy "Hazel" wrote: Hi Tom Nearly there, instead of going down Column J and I'm assuming that because I have entered the letter A 3 times in Column J that I want to replace -- it is offset to next Columns K,L,M where it enters the values on the same row of these columns. Any suggestions how to over come this would be much appreciated -- Many thanks hazel "Tom Ogilvy" wrote: Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) for i = 1 to 3 rng1.offset(0,i).Value = Application.Large(v,i) next Else msgbox Range("J1").Value & " was not found" End if End sub Would be a guess at what you want. -- Regards, Tom Ogilvy "Hazel" wrote: Hi All I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
After fixing a typo and changing the Offset, I tested it and it worked
exactly as I understood you wanted it to do. I suspect we still have a communication problem. I am looking for the value in J6:J25 that matches the value you enter in J1. I assumed there would be different values that would be placed there. So I suspect you haven't entered a value in A. Thus it is searching for a blank cell and finding it - as you describe the situation. YOu can hard code the A in the find command if that is what you want: instead of Set rng1 = rng.Find(What:=rng.Parent.Range("J1"), _ put in Set rng1 = rng.Find(What:="A", _ or if the search term is to be retrieved from TB1 (never sure what role it played) Set rng1 = rng.Find(What:=Trim(me.TB1.Text), _ -- Regards, Tom Ogilvy "Hazel" wrote: Hi Tom You are a very astute man, the values from the Text Boxes are to overwrite the letters A in Column J -- I changed the offset to 0 and the Text Box values were placed in Column J however they filled in the blank cells between the letters A is there a way round this problem?? Thank you for your time in helping me -- Many thanks hazel "Tom Ogilvy" wrote: So you will have A entered 3 times in column J. so is the percentage to be entered to the right of A or overwrite A. Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range Dim sAddr as String, ii as Long set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) ii = 1 sAddr = rng1.Address do rng1.offset(0,1).Value = Application.Large(v,ii) set rng1 = rng.findnext(rng1) ii = ii + 1 Loop until rng.Address = sAddr or ii 3 Else msgbox Range("J1").Value & " was not found" End if End sub -- Regards, Tom Ogilvy "Hazel" wrote: Hi Tom Nearly there, instead of going down Column J and I'm assuming that because I have entered the letter A 3 times in Column J that I want to replace -- it is offset to next Columns K,L,M where it enters the values on the same row of these columns. Any suggestions how to over come this would be much appreciated -- Many thanks hazel "Tom Ogilvy" wrote: Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) for i = 1 to 3 rng1.offset(0,i).Value = Application.Large(v,i) next Else msgbox Range("J1").Value & " was not found" End if End sub Would be a guess at what you want. -- Regards, Tom Ogilvy "Hazel" wrote: Hi All I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserForm Text Box Percentage
Hi Tom
Your hard code solution did the job perfectly would have answered sooner only we have an ISP problem at the moment keep losing connection. Tb1 collects the total bonus for the area and the other 3 text boxes take the percentages to be shared out between 3 agents out of 50 by just entering "A" against the agents name the right bonus now drops into the correct cell against his income. Now all I have to do is get it working for all 7 area's round the country. Trouble is I have to put them on separate Command Buttons -- as below Sub Pool_Click() Dim v(1 To 3), rng As Range, rng1 As Range Dim sAddr As String, ii As Long Set rng = Worksheets("Percentage").Range("J6:J25") Set rng1 = rng.Find(What:="A", _ After:=rng(rng.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then v(1) = Evaluate(Me.Tb4.Text) v(2) = Evaluate(Me.Tb5.Text) v(3) = Evaluate(Me.Tb6.Text) ii = 1 sAddr = rng1.Address Do rng1.Offset(0, 0).Value = Application.Large(v, ii) Set rng1 = rng.FindNext(rng1) ii = ii + 1 Loop Until rng.Address = sAddr Or ii 3 Else MsgBox Range("J1").Value & " was not found" End If End Sub Sub PoolB_Click() Dim v(1 To 3), rng As Range, rng1 As Range Dim sAddr As String, ii As Long Set rng = Worksheets("Percentage").Range("K6:K25") Set rng1 = rng.Find(What:="B", _ After:=rng(rng.Count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then v(1) = Evaluate(Me.Tb8.Text) v(2) = Evaluate(Me.Tb9.Text) v(3) = Evaluate(Me.Tb10.Text) ii = 1 sAddr = rng1.Address Do rng1.Offset(0, 0).Value = Application.Large(v, ii) Set rng1 = rng.FindNext(rng1) ii = ii + 1 Loop Until rng.Address = sAddr Or ii 3 Else MsgBox Range("K1").Value & " was not found" End If End Sub Much appreciated for all your time and effort -- Many thanks hazel "Tom Ogilvy" wrote: After fixing a typo and changing the Offset, I tested it and it worked exactly as I understood you wanted it to do. I suspect we still have a communication problem. I am looking for the value in J6:J25 that matches the value you enter in J1. I assumed there would be different values that would be placed there. So I suspect you haven't entered a value in A. Thus it is searching for a blank cell and finding it - as you describe the situation. YOu can hard code the A in the find command if that is what you want: instead of Set rng1 = rng.Find(What:=rng.Parent.Range("J1"), _ put in Set rng1 = rng.Find(What:="A", _ or if the search term is to be retrieved from TB1 (never sure what role it played) Set rng1 = rng.Find(What:=Trim(me.TB1.Text), _ -- Regards, Tom Ogilvy "Hazel" wrote: Hi Tom You are a very astute man, the values from the Text Boxes are to overwrite the letters A in Column J -- I changed the offset to 0 and the Text Box values were placed in Column J however they filled in the blank cells between the letters A is there a way round this problem?? Thank you for your time in helping me -- Many thanks hazel "Tom Ogilvy" wrote: So you will have A entered 3 times in column J. so is the percentage to be entered to the right of A or overwrite A. Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range Dim sAddr as String, ii as Long set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) ii = 1 sAddr = rng1.Address do rng1.offset(0,1).Value = Application.Large(v,ii) set rng1 = rng.findnext(rng1) ii = ii + 1 Loop until rng.Address = sAddr or ii 3 Else msgbox Range("J1").Value & " was not found" End if End sub -- Regards, Tom Ogilvy "Hazel" wrote: Hi Tom Nearly there, instead of going down Column J and I'm assuming that because I have entered the letter A 3 times in Column J that I want to replace -- it is offset to next Columns K,L,M where it enters the values on the same row of these columns. Any suggestions how to over come this would be much appreciated -- Many thanks hazel "Tom Ogilvy" wrote: Sub CommandButton1_Click() Dim v(1 to 3), rng as Range, rng1 as Range set rng = Worksheets("Percent").range(J6:J25) set rng1 = rng.Find(What:=rng.parent.Range("J1"), _ After:=rng(rng.count), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng1 is nothing then v(1) = Evaluate(me.TB2.Text) v(2) = Evaluate(me.TB3.Text) v(3) = Evaluate(me.TB4.Text) for i = 1 to 3 rng1.offset(0,i).Value = Application.Large(v,i) next Else msgbox Range("J1").Value & " was not found" End if End sub Would be a guess at what you want. -- Regards, Tom Ogilvy "Hazel" wrote: Hi All I have 4 in a line Text Boxes on a Userform Tb1 shows the total of cash collected on clicking a Command Button - Tb2 shows 50% of Tb1 - Tb3 shows 30% of Tb1 - Tb4 shows 20% of Tb1. On the Percent sheet of my workbook in Column J starting at Row 6 Thru Row 25 I have to show the values of Tb2,Tb3,Tb4 easy eh!! Would it be possible to enter say in Column J the letter A and then run a macro? that would search Column J and find the cells with the letter A in them and replace them with the values of the Text Boxes in decending order of percentages. I really wouls appreciate some help with this -- in fact I haven't a clue where to start. -- Many thanks hazel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Text to get a Percentage | Excel Worksheet Functions | |||
how to get a text/letters to equal to a percentage | Excel Worksheet Functions | |||
Extract a percentage from a text | Excel Programming | |||
Percentage calculation on text columns | Excel Discussion (Misc queries) | |||
Convert Percentage to Text | Excel Discussion (Misc queries) |