UserForm Text Box Percentage

[Hazel]

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.

 

[Tom Ogilvy]

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.

 

[Hazel]

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

 

[Tom Ogilvy]

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

 

[Hazel]

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

 

[Tom Ogilvy]

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

 

[Hazel]

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