r.offset() getting LONG value when cell property is text (VBA)

[vba_php]

hey you guys,

a little weirdness here, which I've seen so many times with VBA in excel. here is my code (partial):

        For Each r In Range("b2", "b4727")
        lng_period_loc = 0
            Do Until lng_period_loc > 0
                For i = Len(r) To 1 Step -1
                    cur_loc_searched = i
                    str_temp = Mid(r, cur_loc_searched, 1)
                        If str_temp = "." Then
                            lng_period_loc = cur_loc_searched
                            len_r = Len(r)
                            extension_name = Mid(r, lng_period_loc, (len_r - lng_period_loc) + 1)
                            r.Offset(0, 1) = extension_name
                            GoTo nextCell
                        End If
                Next i
            Loop
nextCell:
        Next r

this line is the problem:

r.Offset(0, 1) = extension_name

r.offset is a text field. I set the properties literally through the interface. extension_name is declared a string. when this line of code runs, the results are supposed to be this:

=> .0
.py
.lib
.pyc
etc, etc...

however, with the extensions that that are just numerics, I get the following results, without the period:

0
1
2
etc, etc...

what is going on here? here is what I see after the code runs as well, if I hover over any offending cell:

can you guys assist with this? It seems like it's an issue with my program settings, but I don't know where it's at. does excel do this marking by default? as in, insert apostrophes before numbers automatically? thanks.

 

[CJ_London]

first - your code is working off column B - there are no file extensions in that column
second - you say the results are 0,1,2 etc but your image shows 0,0,0 etc (which is what I would expect from your code)
third - why have code at all? you can just use a formula
fourth - why such complex code? you can just use the instr function

instead of this
lng_period_loc = 0 Do Until lng_period_loc > 0 For i = Len(r) To 1 Step -1 cur_loc_searched = i str_temp = Mid(r, cur_loc_searched, 1) If str_temp = "." Then lng_period_loc = cur_loc_searched len_r = Len(r) extension_name = Mid(r, lng_period_loc, (len_r - lng_period_loc) + 1) r.Offset(0, 1) = extension_name GoTo nextCell End If Next i Loop

you can just have
if instr(r,".") <>0 then r.offset(0,1)=mid(r,instr(r,"."))

 

[vba_php]

first - your code is working off column B - there are no file extensions in that column

yes there are. .0 IS the file extension. these are all resources from a python windows installation. python is very different than vba or anything else that old.

second - you say the results are 0,1,2 etc but your image shows 0,0,0 etc (which is what I would expect from your code)

the results *are* 1, 2, 3, etc... the image just shows the beginning of the file list. there are 4800+ files to loop through.

third - why have code at all? you can just use a formula

no I can't, because some files look like this:

2hd22.py.0

thus, using INSTR() one time does not work.

fourth - why such complex code? you can just use the instr function

see my answer to your #3. thanks, CJ! =) believe me, I think I've covered all the common sense bases at this point. but what about that annoying green marker in the upper left corner of the cell? can I shut that off via the options? why does that apostrophe appear automatically when it encounters a numeric?

 

[vba_php]

CJ and others,

If you want to see the file that has the code which is causing the problem, see attached. the routine behind sheet 2 that is causing the problem is called resource_extension_extract

 

[CJ_London]

OK
fifth - provide examples which actually demonstrate the issue, not one that is misleading

but what about that annoying green marker in the upper left corner of the cell? can I shut that off via the options?

but you want it to return a text value .py or whatever so this should just be a temporary issue - you say returning a number is wrong. You get it because you are extracting a string from text. If you want it as a number, use the clng or val function or similar to convert it

r.Offset(0, 1) = clng(extension_name)

note this will error if extension_name has non numeric values in it.

So before going further, we now see a different variant of the problem - please provide example strings of all the variants e.g.

.0
.1
.10
.py
.py.0
.py.1.2.3
.pyx.0

etc

 

[CJ_London]

third - why have code at all? you can just use a formula

no I can't, because some files look like this

but your code doesn't handle that situation

 

[vba_php]

OK
fifth - provide examples which actually demonstrate the issue, not one that is misleading

but you want it to return a text value .py or whatever so this should just be a temporary issue - you say returning a number is wrong. You get it because you are extracting a string from text. If you want it as a number, use the clng or val function or similar to convert it

r.Offset(0, 1) = clng(extension_name)

I don't think we're on the same page here, CJ. I think this entire thread has confused the heck out of you. I'll tell you what I'll do....let me go ask an actual excel expert, and I'll get back to you when i see what they have to say. I'm sorry about that! I think the reason this is going on is because of the nature of the work I'm doing. Nobody really knows how to do this, nor are they willing or have the brain power to do so. that's just a short explanation of what I'm doing with this website, that's all. thank you for your effort here. =)

 

[CJ_London]

good luck with your project.

 

[Darrell]

If you're concerned that the cell properties are not set as they should be you could just add this

r.Offset(0, 1).NumberFormat = "@"

before this

r.Offset(0, 1) = extension_name

 

[vba_php]

If you're concerned that the cell properties are not set as they should be you could just add this

r.Offset(0, 1).NumberFormat = "@"

before this

r.Offset(0, 1) = extension_name

yes, Darrell. thanks. that's exactly the answer I got from the excel expert I talked to. and it did solve the problem. again, thanks guys! sorry, but I couldn't share all the info. it has to remain a little bit in the dark until I make it available to the public.