some repetitive job need vba HELP!!

  • Thread starter Thread starter timber
  • Start date Start date
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
 
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...
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 2
Macro that deletes and pastes filterd rows and then some 2
same macro on multiple sheets 4
Vlookup in vba - how to use absolute rows not relative 1
Macro Crashing 3
lookup returns zero or the wrong number 1
fixing a run-time error'9': Subscript out of range 0
lookup vba 2

Back
Top