Clear contents of cells

[Cyberdude]

kpuls, you struck a nerve. Lately I've been struggling with knowing when to use "long" instead of "integer". I habitually use integer, and now it looks like I should be using long.

all Integer's have to be converted to Long's

When would integer be preferable to using long, assumming memory space is not a consideration?

[Ken Puls]

Honestly, Sid, the only time I can think of is if an arguement to a function specifically requires an Integer rather than a Long. Can't think of any off the top of my head right now though, but I know that there are some.

I pretty much use Long for everything now.

[austenr]

Hmmm..I tried both but neither seems to work....xld's does not compile...

[austenr]

Johnske,
Sorry that I said your code didn't work. I was running it against the wrong sheet.

Thanks! Works great!

[johnske]

Hi Austen,

Glad to be able to help - but to get rid of all unnecessary items and to keep it PC for Ken (hi Ken - noted) I'll give a further mod here:[vba]Option Explicit

Sub CheckForNumeric()

Dim I As Long

With Worksheets("Sheet2")
For I = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If IsNumeric(.Cells(I, "A").Value) Then
.Range("A" & I) = Right(.Range("A" & I), 6)
End If
Next I
End With

End Sub[/vba]Regards,
John

[Ken Puls]

...and to keep it PC for Ken ...

[austenr]

Well..I thought Johnske's code (see above) took care of the problem. After further testing I came across another obstacle. When you encounter a cell with contents as this:

5.00E+20 or this
147608130-111111

I does not work. I know that the bottom example is because it is not numeric which I thought I explained earlier but after a closer look I did not (sorry). As for the contents of 5.00E+20, will it not work on those kind of contents? Thanks

[austenr]

Would it be easier to check to see if the format of the cell is numeric and if not change it to that? In other words is the first character is >=0 then format to numeric?

[austenr]

Can someone help with this? Getting type mismatch.


[VBA] Sub CheckForNumeric()
Dim newString As String
Dim I As Long

I = 1

With Worksheets("Sheet1")
For I = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If I >= "A" And I <= "Z" Then
If IsNumeric(.Cells(I, "A").Value) Then
.Range("A" & I) = Right(.Range("A" & I), 6)
End If
End If
Next I
End With

End Sub[/VBA]

[Bob Phillips]

Can someone help with this? Getting type mismatch.

You are testing a long against an alpha, no chance!

This line
If I >= "A" And I <= "Z" Then

should be replcaed by
If .Cells(I, "A").Value >= "A" And .Cells(I, "A").Value <= "Z" Then

But it still won't work because the folowing line is at odds with that line. First you test for a capital letter start, and then test for numeric. You can't have both.

[austenr]

I got this to work for me:

[VBA]Sub CheckForNumeric()

Dim newString As String
Dim I As Long

I = 1

With Worksheets("Sheet1")
For I = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
'If IsNumeric(.Cells(I, "A").Value) Then
If Trim(Sheets("Sheet1").Cells(I, 1).Value <= "9") Then
.Range("A" & I) = Right(.Range("A" & I), 6)
End If
Next I
End With

End Sub[/VBA]

[Bob Phillips]

I got this to work for me:

Test with a value of 1ABCDEF!

[austenr]

Not necessary. This works for me. I will never have that situation in the data I am testing.

[johnske]

Hi Austen,

I thought we were testing for pure numerics. Not to worry, the ones with anything else in them, such as 1234567-987654321,147608130-111111 or even qwertiu123456789 can be handled with this[vba]Option Explicit

Sub CheckForNumeric()
Dim I As Long
With Worksheets("Sheet2")
For I = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If IsNumeric(Right(.Cells(I, "A"), 6)) Then
.Cells(I, "A") = Right(.Cells(I, "A"), 6)
End If
Next I
End With
End Sub
[/vba]However for the ones such as 5.00E+20 you have a problem, I really don't know how you would handle that (?the last 6 digits would be all zeroes anyway?)

Regards,
John

[austenr]

Yes you are correct. Might just delete them. Just junks it up a bit. But thanks for the code.