is there any easy way to check for text?

[Cath]

Hi all

I have some data that is taken by macro into another spreadsheet, put in the right format, then it goes into access. For access it is essential that all the data is numeric NOT text. There will be lots of blanks in the data, this is fine, just as long as what there is, is numeric.

I've had a read around on the board and it seems there isn't a 'istext' in vba, but there is an 'isnumeric'

So the only way I can think to check it, is while moving the data, it loops through the columns of data, checking first to see if the cell is blank, then checking if the data is numeric, then warning the user if not.

However if I loop through it will give a warning every time it find some text. Which could be very annoying if by mistake the user has put in lots of text. Ideally I'd like it check through the whole lot, and then say at the end whether any text was found.

Can anyone think of a way to do this? I'm not asking anyone to write the whole code, just if its possible. (although no doubt I will be asking for more help once I start trying to write it!!)

Thanks for your time!!

 

[al_b_cnu]

Hi Cath,

Using isNumeric, how about this as a starter:

Sub xxx()
Dim R As Range, vCur As Variant

For Each R In Range("A1:A10").SpecialCells(xlCellTypeConstants)
    vCur = R.Value
    If IsNumeric(vCur) Then
        'Do your thing here
    End If
Next R
End Sub

[Edit] - Note - to avoid accessing cells directly too much (performance issues), I set vCur to the current cell contents.

 

[Harvey]

you can use a boolean, (ie. dim anytextfound as boolean) and set it to true if there is any text found ( if not isnumeric(resultvalue) then anytextfound = true else exportvalue(resultvalue) ) assuming exportvalue is the function that exports it to access, and give an error message in the end if there is text found.

In addition, you can store the line numbers of the cells where the text is found in an array, and display it in the error message.

I hope this helps.

Regards,

Martien

 

[Cath]

Hi

Thank, just had a go and that works, but if I put in the rest of my routine in the 'do your thing here' it will repeat it over and over again.

I move the data in blocks not individually as cells. And just to make things worse, i want to check for text down the columns, but I move the data in rows (there is text in every other column so I can't check the rows for text).

Cath

 

[Harvey]

Altering al_b_cnu his function, you would get a result looking like this:

Sub xxx() 
Dim R As Range, vCur As Variant
Dim isTextFound as Boolean, erroneousCells as String

erroneousCells = "Text is found in the following cells: "

For Each R In Range("A1:A10").SpecialCells(xlCellTypeConstants) 
    vCur = R.Value 
    If IsNumeric(vCur) Then 
        'Do your thing here
    Else
        isTextFound = true
        erroneousCells = erroneousCells + " " + R.Address
    End If 
Next R 

If isTextFound then MsgBox erroneousCells

End Sub

Made from scratch, so don't shoot me if you find any grammatical errors

 

[Cath]

Hi sorry I was replying to Alan then, must have posted at the same time.

And Martien, sorry, I don't quite understand what you're suggesting? could you explain a little more, sorry

 

[Harvey]

What does your data look like? Can you post an example?

 

[Cath]

sorry Martien, keep posting at the same time.

Again, I don't want to keep repeating the action. Could I do the action at the end??

 

[Cath]

Hi

My text is in this sort of format. So in the macro. I check along the rows to see if there is data in, then copy the whole row into another spreadsheet. (that is linked to access). But you can see I need to check down the columns to find any text

1.000 L 1.000 L 1.000 L
2.000 L 2.000 L 2.000 L
3.000 L 3.000 L 3.000 L
text H 4.000 L 4.000 L

Cath

 

[Harvey]

Hehe, it's a bit confusing indeed

I don't quite understand what you mean by repeating the action.
Do you mean you are exporting all the data at once?
If not, you can replace the "A1:A10" with the range where your data is in,
and replace the "Do your thing here" with the steps you are using to export your data.

Hmm, I just read your last post, I think I can write a short function for that. Hang on a little