Blank cell not really empty when copy and paste from different projectsheets

[cfernand74]

Can not seem to find a straight answer to this question and it seem that everyone that would transfer data between excel sheets that are located in different projects will at one time will have to deal with this. The issue is I do a lot of copy and paste to sheets from different project s and when I do so the blank cells that are carried over do not respond to any test correctly. I have trying for a very long time trying to make sense of all this information and have test samples found in google searches but nothing makes sence. Things work as they should if one is working on brand new worksheet, but as soon as you copy and paste all goes out the window.
Some test I have done look like this:
1) Copy a range of cells from one worksheet to another.
2) The sheet I past to does many types of calculations.
3) Some of these calculation need to check if there is a value in that blank cell to perform another action.
I have tried on a blank cell:
[vba]If IsEmpty(bw3) Then msgbox (“Its empty”)
If IsNumeric(Range("bw4")) Then msgbox (“it’s a number”)
If strName = vbNullString then (“Null”)
IF strName = "" then msbox ("there is a "" inside")
If Range("am7") = "" Then MsgBox ("there is a "" inside")
If Range("am7") Is Nothing Then MsgBox ("its Nothing")
If Range("am7") = Null Then MsgBox ("its Null")
If IsNull(Range("am7").Value) Then MsgBox ("its Null2")[/vba]
The question simply is what is it that is being carried over when one copies and paste a cell that is blank and not just holding an invisible blank space from one projectsheet to another. I assume that someone out there must now how to test for it and then get a actual result that holds true under different test.
Try testing before responding. And do not hit delete on the cell you are testing otherwise the test will work after doing so. The whole point I do not have the time to be deleting these blank cells I need to test for what is there and be done with it.
Thanks in advance

[westconn1]

my best guess would be a space (or more)
try

if trim(range("am7")) = vbnullstring then msgbox "contains spaces"

of course it could contain various other characters including Tab

to test a cell

with range("am7")
  for i = 1 to len(.text)
     debug.print asc(mid(.text, i , 1))
  next
end with

will give a list of chr values in the immediate window

[cfernand74]

thanks for trying to help
So far your test gives:
32
32
There is a space before the 32 and another space after 32
Question? Is the 32 appearing twice a result of your code running twice or is there really two instances of 32 such as:
32 32
Another thing that is very odd is your test does not give the actual values in a field if there is a value in the cell. What i mean if there is a number in the cell such as:
5000
The result given by your test is:
53
48
48
48
Also tested the word:
Cash
The Result given by your test is"
67
97
115
104
Also i tried running this code to see if the number 32 shows as a number and it doesn't. Is it failing because there maybe two instances of 32 (as tested with your code) with a space in between that this is failing?
[vba]If IsNumeric(Range("am7")) Then MsgBox ("A number inside")[/vba]
As for the code you provided below seams to work but i wonder if its testing true only because there is a space between the two 32's. Please advise on this.
[vba]If trim(range("am7")) = vbnullstring Then msgbox "contains spaces"[/vba]
This is the code i have been testing with and i have tested many different cells to see results:
[vba]Sub TestWhatsInCell() With Range("am7")
For i = 1 To Len(.Text)
Debug.Print Asc(Mid(.Text, i, 1))
'MsgBox Asc(Mid(.Text, i, 1))
Next
End With
'If IsNumeric(Range("am7")) Then MsgBox ("A number inside")
'If Trim(Range("am7")) = vbNullString Then MsgBox "contains spaces"
End Sub[/vba]
I forgot to mention in my first post that i initially extract the data from internet as a grid format Then convert to comma delimited Then once open i copy and paste to the projectsheet that holds the formulas Then i transfer the calculation and text back to the internet to be submitted to the company i work for.
Hope you can help.

[westconn1]

32 is character code for space
your results indicate 2 spaces in cell, therefore not = to 1 space

As for the code you provided below seams to work but i wonder if its testing true only because there is a space between the two 32's.

no that is just how debug.print works prints each call on new line with separator
test with debug.print Asc(Mid(.Text, i, 1));
note ;
the TRIM method will remove all leading and trailing spaces, resulting in vbnullstring (or "") in this case

another test that can be useful in cases like this is len(range("am7")), should return 2 in this case

67 is character code for C etc
if interested, you should easily be able to search ascii character code table

[cfernand74]

Thanks for all your help like i said in my prior post the the code you gave me works great i have modified over thirty of my code so far and lots more to go.
[VBA]If trim(range("am7")) = vbnullstring Then msgbox "contains spaces" [/VBA]

I was wondering of how does one "Trim" if one wants to check if there is a number in a cell and one is not sure if there are spaces attached to that number.
Same goes if there is just regular text in that cell such as "apple" or letter "B"

Would you provide me code to test for number and text while trimming.

I have tried this code before but not reliable

[vba] If IsNumeric(Range("am7")) Then MsgBox ("A number inside")[/vba]

thanks in advance.

[westconn1]

a cell containing a numeric value cannot contain leading or trailing spaces
trim will not remove spaces between character so like 2 2 in a cell will test as false for isnumeric with or without trim, but val will return 22 which will test as true with isnumeric, but that is not the whole cell content, any text string can contain numbers, but val of any text, will always test as numeric even on empty cells

if length of the trimmed cell is > 0 then cell contains some text or value which may contain spaces
you can compare the lengths of trimmed and untrimmed cells to find how many spaces are removed
like

set cel = range("g26")
msgbox "number leading and trailing of spaces in " & cel.address & " is " & len(cel) - len(trim(cel)) & " leaving " &  len(trim(cel)) & " characters, which test as numeric = " & isnumeric(cel) & " showing a value of " & Val(cel)

any empty cell or cell starting with a non-numeric character will return val of 0

hope this helps you, but it is probably still not a complete answer