sorry hit the wrong button.

I'm pulling a part of a string from one cell, and putting it in another.
I've got the method working, except one problem. In some instances the string
that's being pulled is all numbers. In these cases it's getting put into the
new cell as a date. I've tried applying CStr() to the line, but it still
pulls in the number as a date. How can I go about stopping it from doing
this. Not not all strings getting pulled are numbers, only a handful.

Here's the code I'm using:

lastRow = Cells(Rows.Count, "F").End(xlUp).Row 'Get the last row on the page.

For i = 2 To lastRow 'Start at 2 to ignore heard line.
If Cells(i, "F") < "" Then
Cells(i, "E").Value = Mid(Cells(i, "F"), 1, InStr(1, Cells(i,
"F"), " "))
End If
Next i 'Incriment counter and reloop

Looks like your target cells are formatted as dates. Change to General.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
sorry hit the wrong button.

I'm pulling a part of a string from one cell, and putting it in another.
I've got the method working, except one problem. In some instances the
string
that's being pulled is all numbers. In these cases it's getting put into
the
new cell as a date. I've tried applying CStr() to the line, but it still
pulls in the number as a date. How can I go about stopping it from doing
this. Not not all strings getting pulled are numbers, only a handful.

Here's the code I'm using:

lastRow = Cells(Rows.Count, "F").End(xlUp).Row 'Get the last row on the
page.

For i = 2 To lastRow 'Start at 2 to ignore heard line.
If Cells(i, "F") < "" Then
Cells(i, "E").Value = Mid(Cells(i, "F"), 1, InStr(1, Cells(i,
"F"), " "))
End If
Next i 'Incriment counter and reloop

How can I apply the General or text format to the cell in VBA? I know how to
do it on the excel spreadsheet by formatting the cell, but I'm creating the
column just before I run this loop. So I'd have to format it then.

"Bob Phillips" wrote:

Looks like your target cells are formatted as dates. Change to General.

--
HTH

Bob Phillips

columns("G").numberformat="General"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RSteph" wrote in message
...
How can I apply the General or text format to the cell in VBA? I know how
to
do it on the excel spreadsheet by formatting the cell, but I'm creating
the
column just before I run this loop. So I'd have to format it then.

"Bob Phillips" wrote:

Looks like your target cells are formatted as dates. Change to General.

--
HTH

Bob Phillips

Change your If statement to add a line as follows:
If Cells(i, "F") < "" Then
Cells(i,"E").NumberFormat = "General"
Cells(i, "E").Value = Mid(Cells(i, "F"), _
1, InStr(1, Cells(i, "F"), " "))
End If

That will make sure the format is the same for each cell you post to.


"RSteph" wrote:

sorry hit the wrong button.

I'm pulling a part of a string from one cell, and putting it in another.
I've got the method working, except one problem. In some instances the string
that's being pulled is all numbers. In these cases it's getting put into the
new cell as a date. I've tried applying CStr() to the line, but it still
pulls in the number as a date. How can I go about stopping it from doing
this. Not not all strings getting pulled are numbers, only a handful.

Here's the code I'm using:

lastRow = Cells(Rows.Count, "F").End(xlUp).Row 'Get the last row on the page.

For i = 2 To lastRow 'Start at 2 to ignore heard line.
If Cells(i, "F") < "" Then
Cells(i, "E").Value = Mid(Cells(i, "F"), 1, InStr(1, Cells(i,
"F"), " "))
End If
Next i 'Incriment counter and reloop