VBA how to extract with variable lenght..

[lemming78]

Hello, i have this worksheet

[TABLE="width: 1431"]
<tbody>[TR]
[TD="colspan: 7"]DateTime,Name,Country,Volatility,Actual,Previous,Consensus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Data[/TD]
[TD]Ora[/TD]
[TD]Nome[/TD]
[TD]Country[/TD]
[TD]Volatility[/TD]
[TD]Actual[/TD]
[TD]Previous[/TD]
[TD]Consensus[/TD]
[/TR]
[TR]
[TD="colspan: 5"]20121001 02:00:00{Labour Day}{Australia}{0}{}{}{}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20121001[/TD]
[TD="align: right"]02:00:00[/TD]
[TD]Labour Day}{Australi[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]20121001 02:00:00{National Day}{China}{0}{}{}{}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20121001[/TD]
[TD="align: right"]02:00:00[/TD]
[TD]National Day}{Ch[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and i need to extract parts of the rows into the proper cells on the right. I have written this piece of code but i don't know how to stop the search function to the first result. It always find the last bracket and not the first, in order to stop.

Public Sub CalendarSheet()Dim stringa As Variant, i As Integer
'Worksheets.Add After:=Sheets(1)
'ActiveSheet.Name = "Dati"
'nomi in Dati
'Range("A1").Value = "Data"
'Range("B1").Value = "Ora"
'Range("C1").Value = "Nome"
'Range("D1").Value = "Country"
'Range("E1").Value = "Volatility"
'Range("F1").Value = "Actual"
'Range("G1").Value = "Previous"
'Range("H1").Value = "Consensus"
'Range("A1:H1").EntireColumn.AutoFit
'Range("A1:H1").Copy
'sostituzione valori non corretti prima dei simboli valuta
'Worksheets("eventdates").Select
'Range("K1").Select
'ActiveSheet.Paste


Range("A1").Select
Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False


'sistemare in colonne a fianco i dati
i = 2
For Each stringa In Range("A2:A720")
Range("K" & i).Value = Left(stringa, 8)
Range("L" & i).Value = Mid(stringa, 10, 8)
Range("M" & i).Value = Mid(stringa, WorksheetFunction.Search("{", stringa) + 1, Len(stringa) - Range("A" & i).Find("}", , , , , xlPrevious))


i = i + 1
Next


End Sub

Thanks!

 

[northwolves]

Range("M" & i).Value = split(split(stringa, "{",)(1),"}")(0)

Hope it helps

 

[pgc01]

Hi

You can also do it directly without vba using Text-to-Columns.

 

[lemming78]

Range("M" & i).Value = split(split(stringa, "{",)(1),"}")(0)

Hope it helps

Thanks. It doesn't work. A msgbox is prompted, expression missing is the error

if i modify it this way

Range("M" & i).Value = Split(Split(stringa, "{")(1), "}")(0)

error is index is not included in the interval

I need to do this by macro because i need a macro for multiple sheets

 

[pgc01]

With Text-to-Columns it would be like:

Sub Test()

    
With Range("A2:A100")
    
    .TextToColumns Destination:=.Range("B1"), DataType:=xlDelimited, Other:=True, OtherChar:="{"
    
    With .Columns("B:H")
        .Replace What:="}", Replacement:="", LookAt:=xlPart
        .AutoFit
    End With
End With
End Sub

 

[lemming78]

With Text-to-Columns it would be like:

Sub Test()

    
With Range("A2:A100")
    
    .TextToColumns Destination:=.Range("B1"), DataType:=xlDelimited, Other:=True, OtherChar:="{"
    
    With .Columns("B:H")
        .Replace What:="}", Replacement:="", LookAt:=xlPart
        .AutoFit
    End With
End With
End Sub

more than perfect. Thanks!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[northwolves]

Thanks. It doesn't work. A msgbox is prompted, expression missing is the error

if i modify it this way

Range("M" & i).Value = Split(Split(stringa, "{")(1), "}")(0)

error is index is not included in the interval

I need to do this by macro because i need a macro for multiple sheets

Sorry my firefox make me crazy this 2 days.

The following should work for you.

Range("M" & i).Value = Split(Split(stringa, "{")(1), "}")(0)