Error when using split function 2

[DCSage]

I am creating a package in SSIS which imports CSV files and dumps the data into a few tables. The files are initial csv reports run by the user and then saved to the folders that the package searches. The problem is that the csv files contain a field for the title which has the following string in the A column (range of A2 to the A64000):

COLD NEWS-BBNW-09041 HEART OF THE CITY: DYING TO EAT

How can I improve my macro to remove all information after the first hyphen. The worksheet may not always have the same name.

Sub removeHyphen()

Dim lngIndex As Long
Dim strParts() As String
Dim strInput As String
Dim SearchRange As Range
Dim startRange As Range
Dim endRange As Range
startRange = "A2"
endRange = "A64000"
Dim Count
Dim myObj As Object
Set myObj = Sheets(1)


'Set SearchRange = ThisWorkbook.Worksheets").Range(startRange, endRange)
Set SearchRange = ThisWorkbook.Worksheets("01012010_03312010").Select

strParts = Split(strInput, "-")
strInput = ""
Count = 1

Do While Count < endRange.End(xlDown).Row

'For lngIndex = 0 To UBound(strParts) - 1
        strInput = strInput & strParts(lngIndex) & "/"
   ' Next
    strInput = Left(strInput, Len(strInput) - 1)
    
    Loop
    
End Sub

 

[SkipVought]

I have not idea what you are doing with the range and sheet or your loop. There are a number of problems in general with your code.

With regard to the Split function, I'd use the InStr function instead to find the location of the FIRST hyphen and then use the Right function to reurn the segment to the right of that location.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DCSage]

I want to remove everything after the first hyphen.

 

[Andrzejek]

I am with Skip, but if you want "to remove all information after the first hyphen":

Dim str As String
Dim i As Integer

str = "COLD NEWS-BBNW-09041 HEART OF THE CITY: DYING TO EAT"

str = Left(str, InStr(str, "-") - 1)

Debug.Print str[green]
'COLD NEWS[/green]

Have fun.

---- Andy

 

[SkipVought]

And using the InStr and Right functions will accomplish that.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

AND the Len function as well.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DCSage]

I revised my code as i want to search through the entire A column. But when I run the macro from excel, I get the following error:

invalid procedure call or argument

revised code:

Sub ModifyShow()

Dim cellData As String
Dim x As Integer

      Range("A2").Select
      Do Until IsEmpty(ActiveCell)
         cellData = Right(cellData, InStr(cellData, "-") - 1)
         ActiveCell.Offset(1, 0).Select
      Loop
End Sub

 

[PHV]

I'd replace this:
cellData = Right(cellData, InStr(cellData, "-") - 1)
with this:
If ActiveCell Like "*-*" Then
ActiveCell = Left(ActiveCell, InStr(ActiveCell, "-") - 1)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DCSage]

Thank you Everyone!

The following is my completed code, which ran without errors:

Option Explicit
Sub ModifyShow()

Dim cellData As String
Dim x As Integer

    Range("A2").Select
     Do Until IsEmpty(ActiveCell.Value) And IsEmpty(ActiveCell.Offset(1, 0))
         
         If ActiveCell Like "*-*" Then
         ActiveCell = Left(ActiveCell, InStr(ActiveCell, "-") - 1)
         ActiveCell.Offset(1, 0).Select
         End If
         
      Loop
End Sub

is there a way to automate this script so that everytime my user saves a report as a CSV file, then this script would run? how can this script be used for every file?

Thanks.

 

[SkipVought]

is there a way to automate this script so that everytime my user saves a report as a CSV file, then this script would run?

HOW is your user saving the report as a .csv file? Please be VERY specific.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Have a look at the WorkbookBeforeSave event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DCSage]

sorry. the user pulls the information from a website and literally pastes the data into an excel worksheet (I have no control over this process). The users saves the file as a .csv file into a specified folder. I have a SSIS package that then loops over the files in the folder and exports the data to specific tables in the sql database.

 

[DCSage]

Thank you all so much!

 

[combo]

Assuming that you replace first hyphen and the text after in non-empty block below A2, you can use excel functionality and avoid looping:

Range(Range("A2"), Range("A2").End(xlDown)).Replace What:="-*", Replacement:="", LookAt:=xlPart

combo