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 |
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) |