subscript out of range

[Directlinq]

CAn anybody tell me why this code is giving me an error

' Open a text file for input. inputbox returns the path to read the file
Open Environ("USERPROFILE") & "\Desktop\text.txt" For Input As nFileNum
lLineCount = 1
' Read the contents of the file
Do While Not EOF(nFileNum)
   Line Input #nFileNum, sNextLine
   sText = sNextLine
strData() = Split(sNextLine, " – ")
 
artist = Trim(strData(0))
artist = Replace(artist, "# ", "")
songname = Trim(strData(1))

It saying subscript out of range on the

songname = Trim(strData(1))

line.

All this code does is reads the text file and splits the text into 2 strings and removes a character.

Many Thanks

 

[boblarson]

probably on that line there are not two resulting fields. So, what you should do is when it errors out click the DEBUG button and then go find what strData(0) is and look at sNextLine is in the Immediate Window using

?sNextLine and then hit enter

see what line it is breaking on.

 

[Directlinq]

# JAY SEAN FT LIL’ WAYNE - Down

This is the line it is breaking on according to the immidiate window and it is split by a -

Any ideas

Or another way i can split the line in question?

Thanks again

 

[boblarson]

So you're sure that line has the single space before and after the hyphen? It might have more than one so your split code might not be getting it right. I would see about only splitting using the "-" instead of " - " which might catch it.

 

[Directlinq]

Ive changed it from " - " to "-" and it still is stopping on that line?
Wierd
Any other ideas
Thank you for helping by the way

 

[boblarson]

Is it possible to upload that text file? (zipped)

 

[Directlinq]

It is so small i can post it just copy it into a text file called text.txt

# ALEXANDRA BURKE – Bad Boys 
# DAVID GUETTA FT AKON – Sexy Chick 
# MADONNA – Celebration 
# EVA SIMONS – Silly Boy 
# DEADMAU5 – Ghosts ‘N’ Stuff 
# LA ROUX – I’m Not Your Toy 
# CHERYL COLE – Fight For This Love 
# COBRA STARSHIP – Good Girls Go Bad 
# WHITNEY HOUSTON – Million Dollar Bill 
# SHENA – Nasty Little Rumour 
# SEAN PAUL – Press It Up 
# TINCHY STRYDER – You’re Not Alone 
# JAY SEAN FT LIL’ WAYNE - Down 
# LMFAO – I’m In Miami Trick (Clean Club Version) 
# CHIPMUNK – Oopsy Daisy 
# PITBULL – Hotel Room Service (Clean Version) 
# MASTER SHORTIE – Bringing It Back 
# TAIO CRUZ – Break Your Heart 
# ASHER ROTH FT. KERI HILSON – She Don’t Wanna Man 
# JADE EWEN – My Man 
# JAY Z FT RHIANNA & KANYE – Run This Town 
# BEYONCE – Broken Hearted Girl 
# JLS – Everybody In Love 
# MR. HUDSON – White Lies 
# ROBBIE WILLIAMS – Bodies 
# OU EST LE SWIMMING POOL – Dance The Way I Feel 
# JORDIN SPARKS – SOS (Let The Music Play) 
# ALPHABEAT – The Spell 
# MUSE – Uprising

 

[boblarson]

Yep, found it. That one line has a different type of hyphen. Not sure how or where it would have come from for each but here's the line of code that will take care of it:

replace this:

Split(sNextLine, " – ")

with this:

Split(sText, IIf(InStr(1, sNextLine, "–") > 0, "–", "-"), , vbTextCompare)

 

[Directlinq]

Wow that does more than i could have hoped for.
Could you briefly just explain what that line does.
Ive got if something is greater than 0

Thank you so much

 

[boblarson]

It basically says if a certain character is in the string use it, and if not use the other.