How to use VBA to drag down ?

[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

 

[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

 

[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

 

[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

 

[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

 

[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.

 

[vumian]

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

 

[vumian]

oh,i've not ever thought it before,) so great great, you're good man

thank you very much

 

[Chris]

I think it's so cool that you help others whose first language is not
English. Way to go Dave. Yes, you are a good man. I agree with vumian.
--
Chris

| 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.
|
| vumian wrote:
| >
| > 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.
| >
| > --
| > vumian
| > ------------------------------------------------------------------------
| > vumian's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=36494
| > View this thread:
http://www.excelforum.com/showthread.php?threadid=563001
|
| --
|
| Dave Peterson

 

[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>.