The following code steps through a range, tests an extracted value, and
either deletes the row or writes the test value in column 1 as a text value.

For r = lastrow To 8 Step -1
tst = Left(impt.Cells(r, 2), 9)
If Application.CountA(Rows(r)) = 0 Or IsNumeric(tst) = False Then
Rows(r).Delete
Else
impt.Cells(r, 1) = WorksheetFunction.Text(tst, 0)
End If
Next r

The problem is that the values written to the cells reference "cells(r,1)"
is a numberic value, not text. (The str() function gives the same result.)
I can achieve my objective if I run a new for...next loop on the extracted
values after the above routine has completed, but why is that necesssary?
Why will the conversion function not work as intended in the first loop?

DM

Dick,

impt.Cells(r, 1) = "'" & Format(tst, "0")

HTH,
Bernie
MS Excel MVP


"Dick Minter" wrote in message
...
The following code steps through a range, tests an extracted value, and
either deletes the row or writes the test value in column 1 as a text value.

For r = lastrow To 8 Step -1
tst = Left(impt.Cells(r, 2), 9)
If Application.CountA(Rows(r)) = 0 Or IsNumeric(tst) = False Then
Rows(r).Delete
Else
impt.Cells(r, 1) = WorksheetFunction.Text(tst, 0)
End If
Next r

The problem is that the values written to the cells reference "cells(r,1)"
is a numberic value, not text. (The str() function gives the same result.)
I can achieve my objective if I run a new for...next loop on the extracted
values after the above routine has completed, but why is that necesssary?
Why will the conversion function not work as intended in the first loop?

DM

Is column 1 formatted as text or something else. You may be getting an
implicit conversion when the value is dropped into the cell. Try formatting
the column to text proir to running the macro and see if that helps.
--
HTH...

Jim Thomlinson


"Dick Minter" wrote:

The following code steps through a range, tests an extracted value, and
either deletes the row or writes the test value in column 1 as a text value.

For r = lastrow To 8 Step -1
tst = Left(impt.Cells(r, 2), 9)
If Application.CountA(Rows(r)) = 0 Or IsNumeric(tst) = False Then
Rows(r).Delete
Else
impt.Cells(r, 1) = WorksheetFunction.Text(tst, 0)
End If
Next r

The problem is that the values written to the cells reference "cells(r,1)"
is a numberic value, not text. (The str() function gives the same result.)
I can achieve my objective if I run a new for...next loop on the extracted
values after the above routine has completed, but why is that necesssary?
Why will the conversion function not work as intended in the first loop?

DM