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