Finding last cell in column containing formulas using VBA
 

I have a table which is updated, then the values from three columns
transferred over to another sheet, folllowing on from the last time data was
transferred (the table gets wiped each time this is done.. The first two
columns are ok to find the last cell, as they are text, and are copied like
this:

Range("A2", Range("B65536").End(xlUp)).Name = "draughtsmenproject"
Range("draughtsmenproject").Copy
Sheets("Tally").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste

The final column though, contains a SUM formula, which counts as a 'filled'
cell when excel checks. Can i get Excel to ignore the formula if the result
is 0, or would i be better using an offset function in VBA, to count across
the number of columns from one of the last two text entries to the last SUM
entry (which would be on the same line).

Any help is appreciated.

Richard
--
Richard

Finding last cell in column containing formulas using VBA
 

You could either go across two columns from the last cell in column A by
adding .Offset(0,2), or go up 1 row from the C column with .Offset(-1,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Richard" wrote in message
...
I have a table which is updated, then the values from three columns
transferred over to another sheet, folllowing on from the last time data
was
transferred (the table gets wiped each time this is done.. The first two
columns are ok to find the last cell, as they are text, and are copied
like
this:

Range("A2", Range("B65536").End(xlUp)).Name = "draughtsmenproject"
Range("draughtsmenproject").Copy
Sheets("Tally").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste

The final column though, contains a SUM formula, which counts as a
'filled'
cell when excel checks. Can i get Excel to ignore the formula if the
result
is 0, or would i be better using an offset function in VBA, to count
across
the number of columns from one of the last two text entries to the last
SUM
entry (which would be on the same line).

Any help is appreciated.

Richard
--
Richard

Finding last cell in column containing formulas using VBA
 

Thanks Bob

I have tried to insert the offset onto the range i defined as
"draughtsmenproject", so it would go two across from there. The problem is
that the null values in the cells that contained the formulas are being taken
across to the other sheet. i was hoping to be able to use the offset function
for the column C values only when there was a value in column A and column B.

--
Richard


"Bob Phillips" wrote:

You could either go across two columns from the last cell in column A by
adding .Offset(0,2), or go up 1 row from the C column with .Offset(-1,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Richard" wrote in message
...
I have a table which is updated, then the values from three columns
transferred over to another sheet, folllowing on from the last time data
was
transferred (the table gets wiped each time this is done.. The first two
columns are ok to find the last cell, as they are text, and are copied
like
this:

Range("A2", Range("B65536").End(xlUp)).Name = "draughtsmenproject"
Range("draughtsmenproject").Copy
Sheets("Tally").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.Paste

The final column though, contains a SUM formula, which counts as a
'filled'
cell when excel checks. Can i get Excel to ignore the formula if the
result
is 0, or would i be better using an offset function in VBA, to count
across
the number of columns from one of the last two text entries to the last
SUM
entry (which would be on the same line).

Any help is appreciated.

Richard
--
Richard

Finding last cell in column containing formulas using VBA
 

Do you mean a range that ignores rows that have no value? I thought you were
just referring to the last row, where A & B were null, C had a formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Richard" wrote in message
...
Thanks Bob

I have tried to insert the offset onto the range i defined as
"draughtsmenproject", so it would go two across from there. The problem is
that the null values in the cells that contained the formulas are being
taken
across to the other sheet. i was hoping to be able to use the offset
function
for the column C values only when there was a value in column A and column
B.