some repetitive job need vba HELP!!

T

timber

HI.

I don’t know if I found the right place to post my vba problem so tha
I can have an advice about the possibility of excel vba can help me i
my worksheet…I shall try here… the question is clearly stated below
thank you very much…

I have an excel spreadsheet that starts from cell A1 until AF2000. in
few cells explained below has some very repetitive & boring tasks I d
everyday in my life…

so, i think i found a better way to illustrate my problem in my macro
i record while i drag down the formula =IF(OR(F10="T",F10="F"),C10,0)
u can paste the macro into your vba & see the formula in column X. ok
in this example, i drag from C10 until C20 (aware of the bold font tha
C… can be adjusted in that formula above) so when the macro finishe
running, the formula will be in =IF(OR(W10="T",W10="F"),C20,0). but a
i told you before, in between the C10 and C20, a result comes out in
cell... ok ... give it Z10. i found C12 has the highest value among th
C10 and C20 in Z10. so, i need that C12 stick in X10 at last a
=IF(OR(W10="T",W10="F"),C12,0) and the highest figure in Z10, exampl
999.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 3/1/2004 by user
'

'
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[1]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[2]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[3]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[4]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[5]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[6]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[7]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[8]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[9]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[10]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013")
Type:=xlFillDefault
Range("X10:X2013").Select
End Sub


the problem is i can record my dragging from C10 to C20 but i canno
make macro choose the highest value at last...

so, do you understand the problem so that maybe vba can do it?
anyway, please consult me & if you need further assistance just b
asking questions I will give you more details that you need to solv
the question above.

Thank you very much
 
F

Frank Kabel

Hi
not quite sure what you're try to achieve. but if you want to put the
highest value in column C from column C (if the conditions are met)
maybe the following formula is what you're looking for. Insert the
following in Z10:
=IF(OR(F10="T",F10="F"),MAX($C$10:C10),0)
and copy this formula down

--
Regards
Frank Kabel
Frankfurt, Germany
HI.

I don’t know if I found the right place to post my vba problem so that
I can have an advice about the possibility of excel vba can help me in
my worksheet…I shall try here… the question is clearly stated below…
thank you very much…

I have an excel spreadsheet that starts from cell A1 until AF2000. in
a few cells explained below has some very repetitive & boring tasks I
do everyday in my life…

so, i think i found a better way to illustrate my problem in my macro:
i record while i drag down the formula =IF(OR(F10="T",F10="F"),C10,0).
u can paste the macro into your vba & see the formula in column X. ok,
in this example, i drag from C10 until C20 (aware of the bold font
that C… can be adjusted in that formula above) so when the macro
finished running, the formula will be in
=IF(OR(W10="T",W10="F"),C20,0). but as i told you before, in between
the C10 and C20, a result comes out in a cell... ok ... give it Z10.
i found C12 has the highest value among the C10 and C20 in Z10. so, i
need that C12 stick in X10 at last as =IF(OR(W10="T",W10="F"),C12,0)
and the highest figure in Z10, example 999.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 3/1/2004 by user
'

'
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[1]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[2]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[3]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[4]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[5]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[6]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[7]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[8]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[9]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[10]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
End Sub


the problem is i can record my dragging from C10 to C20 but i cannot
make macro choose the highest value at last...

so, do you understand the problem so that maybe vba can do it?
anyway, please consult me & if you need further assistance just by
asking questions I will give you more details that you need to solve
the question above.

Thank you very much…
Click to expand...
 
B

Bob Phillips

Hi Timber,

I don't know why you keep putting a new formula in, but I think this version
of the last formula does what you want

Range("X10").AutoFill Destination:=Range("X10:X" & Cells(Rows.Count,
"C").End(xlUp).Row), Type:=xlFillDefault

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

timber > said:
HI.

I don't know if I found the right place to post my vba problem so that
I can have an advice about the possibility of excel vba can help me in
my worksheet.I shall try here. the question is clearly stated below.
thank you very much.

I have an excel spreadsheet that starts from cell A1 until AF2000. in a
few cells explained below has some very repetitive & boring tasks I do
everyday in my life.

so, i think i found a better way to illustrate my problem in my macro:
i record while i drag down the formula =IF(OR(F10="T",F10="F"),C10,0).
u can paste the macro into your vba & see the formula in column X. ok,
in this example, i drag from C10 until C20 (aware of the bold font that
C. can be adjusted in that formula above) so when the macro finished
running, the formula will be in =IF(OR(W10="T",W10="F"),C20,0). but as
i told you before, in between the C10 and C20, a result comes out in a
cell... ok ... give it Z10. i found C12 has the highest value among the
C10 and C20 in Z10. so, i need that C12 stick in X10 at last as
=IF(OR(W10="T",W10="F"),C12,0) and the highest figure in Z10, example
999.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 3/1/2004 by user
'

'
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[1]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[2]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[3]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[4]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[5]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[6]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[7]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[8]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[9]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
Range("X10").Select
ActiveCell.FormulaR1C1 =
"=IF(OR(RC[-1]=""T"",RC[-1]=""F""),R[10]C[-21],0)"
Range("X10").Select
Selection.AutoFill Destination:=Range("X10:X2013"),
Type:=xlFillDefault
Range("X10:X2013").Select
End Sub


the problem is i can record my dragging from C10 to C20 but i cannot
make macro choose the highest value at last...

so, do you understand the problem so that maybe vba can do it?
anyway, please consult me & if you need further assistance just by
asking questions I will give you more details that you need to solve
the question above.

Thank you very much.
Click to expand...
 
Top