How to use VBA to drag down ?

V

vumian

i have 2 col
- fisrt and second col contain value (exiset already)

How to use VBA to minus them an drag down at third col to end of row o
col 1 or 2

EX: col 1 and 2 have 1000 rows
therefore, col 3 will be 1000 result rows automatically
Thanks for your help
 
D

Dave Peterson

Option Explicit
Sub testme()
Dim LastRow As Long

With Worksheets("sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("c1:C" & LastRow).FormulaR1C1 _
= "=rc[-2]-rc[-1]"
End With
End Sub
 
V

vumian

ths very, but i take extend my thinks following :

if they are 2 cols random, not next to each other above.
how to do it, pl
 
D

Dave Peterson

You'll still need to pick out one of the columns that can define where that last
used row is.

LastRow = .Cells(.Rows.Count, "X").End(xlUp).Row

And you'll have to adjust this to match the column that you're putting the
formula. In fact, I'd change it to something like:

Option Explicit
Sub testme()
Dim LastRow As Long
dim FirstCol as Long
dim SecondCol as long
dim FormCol as long

with worksheets("Sheet1")
firstcol = .range("e1").column 'I used column 5 (E)
SecondCol = .range("Z1").column 'I used column 26 (Z)
FormCol = .range("AZ1").column 'I used column 52 (AZ)

'using the first column to get the longest row
Lastrow = .cells(.rows.count,firstcol).end(xlup).row

.range(.cells(1,formcol),.cells(lastrow,formcol)).formula _
= "=" & .cells(1,firstcol).address(0,0) _
& "-" & .cells(1,secondcol).address(0,0)

end with

end sub
 
V

vumian

Oke, ths, it runs well.

i also get one small thing more, pls help me

Col A stores Number as Text
How to convert it to end of 'Number as Text' ,
row 1- title is present a real text.

thanks for your help
 
D

Dave Peterson

Select an empty cell.
edit|copy
select column A
edit|Paste special|check addition

If you need this in your macro, record a macro and you'll see useable code.
 
V

vumian

oh,i've ever thought it before,:)) so great great, you're good man , :p
thank you very much
 
V

vumian

oh,i've not ever thought it before,:)) so great great, you're good man
:p
thank you very much
 
D

Dave Peterson

There are lots of people in these groups that help people--no matter what the
OP's mother tongue is--be it English, VBA or even binary <vbg>.
 
Top