I have the following line of code that inserts into a particular cell
the count of the No. of asset records listed above it plus a text
string declaring " Assets in the selected period" plus it takes the
date value of two variables and formats them and places them in cell
along the text



rng.Formula = "=COUNTA(R[-4]C:R[-1]C)" & "&"" assets Aquired in the
selected period " & "" & Format$(first, "short date") & "" - "" &
Format$(last, "short date")




My problem is that this does not display as i would have liked the
dates are merely displayed as decimal numbers. I have changed the
format of the cell to correct this but that does not appear to alter
anything.

How do i get the cell to display the count for the cells display the
text message and the criteria range in the same cell

I'd use rng.formulaR1C1:

rng.FormulaR1C1 = "=COUNTA(R[-4]C:R[-1]C)" _
& " & " & """ assets Aquired in the selected period ""&" _
& """" & Format$(first, "short date") _
& " - " & Format$(last, "short date") & """"

wrote:

I have the following line of code that inserts into a particular cell
the count of the No. of asset records listed above it plus a text
string declaring " Assets in the selected period" plus it takes the
date value of two variables and formats them and places them in cell
along the text

rng.Formula = "=COUNTA(R[-4]C:R[-1]C)" & "&"" assets Aquired in the
selected period " & "" & Format$(first, "short date") & "" - "" &
Format$(last, "short date")

My problem is that this does not display as i would have liked the
dates are merely displayed as decimal numbers. I have changed the
format of the cell to correct this but that does not appear to alter
anything.

How do i get the cell to display the count for the cells display the
text message and the criteria range in the same cell

--

Dave Peterson

On Mar 16, 12:37*am, Dave Peterson wrote:
I'd use rng.formulaR1C1:

rng.FormulaR1C1 = "=COUNTA(R[-4]C:R[-1]C)" _
* * * * * * * * * * & " & " & """ assets Aquired in the selected period ""&" _
* * * * * * * * * * & """" & Format$(first, "short date") _
* * * * * * * * * * & " - " & Format$(last, "short date") & """"





wrote:

I have the following line of code that inserts into a particular cell
the count of the No. of asset records listed above it plus a text
string declaring " Assets in the selected period" plus it takes the
date value of two variables and formats them and places them in cell
along the text

rng.Formula = "=COUNTA(R[-4]C:R[-1]C)" & "&"" assets Aquired in the
selected period " & "" & Format$(first, "short date") & "" - "" &
Format$(last, "short date")

My problem is that this does not display as i would have liked the
dates are merely displayed as decimal numbers. I have changed the
format of the cell to correct this but that does not appear to alter
anything.

How do i get the cell to display the count for the cells display the
text message and the *criteria range in the same cell

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for that it worked like a charm but now how do i get the
followiing to work also:-

rng.FormulaR1C1 = "=COUNTA(" & ActiveCell.Offset(-1, 0).Address & ":$A
$9)" & " & " & """ assets Aquired in the selected period ""&" & """" &
Format$(first, "short date") & " - " & Format$(last, "short date") &
""""

First, .address by itself won't give you an R1C1 reference style address. So in
this case, you don't want to use .formular1c1 (and $A$9 isn't an R1C1 address,
either).

So just try

Rng.Formula = "=COUNTA(" & ActiveCell.Offset(-1, 0).Address _
& ":$A$9)" & " & " _
& """ assets Aquired in the selected period ""&" _
& """" & Format$(First, "short date") _
& " - " & Format$(Last, "short date") & """"



wrote:

<<snipped

rng.FormulaR1C1 = "=COUNTA(" & ActiveCell.Offset(-1, 0).Address & ":$A
$9)" & " & " & """ assets Aquired in the selected period ""&" & """" &
Format$(first, "short date") & " - " & Format$(last, "short date") &
""""

--

Dave Peterson

On Mar 16, 12:37*am, Dave Peterson wrote:
I'd use rng.formulaR1C1:

rng.FormulaR1C1 = "=COUNTA(R[-4]C:R[-1]C)" _
* * * * * * * * * * & " & " & """ assets Aquired in the selected period ""&" _
* * * * * * * * * * & """" & Format$(first, "short date") _
* * * * * * * * * * & " - " & Format$(last, "short date") & """"





wrote:

I have the following line of code that inserts into a particular cell
the count of the No. of asset records listed above it plus a text
string declaring " Assets in the selected period" plus it takes the
date value of two variables and formats them and places them in cell
along the text

rng.Formula = "=COUNTA(R[-4]C:R[-1]C)" & "&"" assets Aquired in the
selected period " & "" & Format$(first, "short date") & "" - "" &
Format$(last, "short date")

My problem is that this does not display as i would have liked the
dates are merely displayed as decimal numbers. I have changed the
format of the cell to correct this but that does not appear to alter
anything.

How do i get the cell to display the count for the cells display the
text message and the *criteria range in the same cell

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you very much three hours spent on this was driving me stir
crazy


Henry Onyeagbako