Left of character

[Takes2ToTango]

Hi All,

Is there a way to change the below code to do the following:
Grab all characters LEFT of '/' from databodyrange.copy and then paste into the below range?

 ws.ListObjects("List").ListColumns(5).DataBodyRange.Copy
 wsasn.Range("G2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[KOKOSEK]

Hmm try something like this:

a = ws.ListObjects("List").ListColumns(5).DataBodyRange.Value
    For i = LBound(a) To UBound(a)
        On Error Resume Next   ' just in case if there is no / in string
        a(i, 1) = Left(a(i, 1), InStr(1, a(i, 1), "/") - 1)
        On Error GoTo 0
    Next i
wsasn.Range("G2").Resize(UBound(a), 1).Value = a

 

[Peter_SSs]

If I have understood (from the very brief description & no sample data or expected results ) then I think this may do them all at once.

ws.ListObjects("List").ListColumns(5).DataBodyRange.Copy
With wsasn.Range("G2").Resize(ws.ListObjects("List").DataBodyRange.Rows.Count)
  .PasteSpecial Paste:=xlPasteValues
  .Replace What:="/*", Replacement:="", LookAt:=xlPart
End With

 

[Takes2ToTango]

Thank you both for your replies. I'll give you a bit more of a description to help!

So basically I have a table and one column has a list of numbers in this format - 1234567/003

Basically I want to copy and paste the 1234567 into a column of table 2 and then copy and paste the 003 into another column of table 2

Table 1

1234567/003

Table 2

Number 1	Number 2
1234567	003

so it would look like the above.

I hope this helps!

 

[KOKOSEK]

Quickly (not tested):

sub Abracadabra()
Dim a, b, c As Variant
a = ws.ListObjects("List").ListColumns(5).DataBodyRange.Value
    For i = LBound(a) To UBound(a)
        On Error Resume Next   ' just in case if there is no / in string
        b(i) = Left(a(i, 1), InStr(1, a(i, 1), "/") - 1)                             'whatever is before /
        c(i) = Trim(Mid(a(i, 1), InStr(1, a(i, 1), "/") + 1, 10))             'whatever is after /
        On Error GoTo 0
    Next i

wsasn.Range("G2").Resize(UBound(b), 1).Value = a        'column before /
wsasn.Range("H2").Resize(UBound(c), 1).Value = a        'column after /

end sub

 

[Takes2ToTango]

Quickly (not tested):

sub Abracadabra()
Dim a, b, c As Variant
a = ws.ListObjects("List").ListColumns(5).DataBodyRange.Value
    For i = LBound(a) To UBound(a)
        On Error Resume Next   ' just in case if there is no / in string
        b(i) = Left(a(i, 1), InStr(1, a(i, 1), "/") - 1)                             'whatever is before /
        c(i) = Trim(Mid(a(i, 1), InStr(1, a(i, 1), "/") + 1, 10))             'whatever is after /
        On Error GoTo 0
    Next i

wsasn.Range("G2").Resize(UBound(b), 1).Value = a        'column before /
wsasn.Range("H2").Resize(UBound(c), 1).Value = a        'column after /

end sub

Thanks for your response.

I seem to be getting a type mismatch on the below
For i = LBound(a) To UBound(a)

 

[Takes2ToTango]

Thank you Peter, your example did what I was after.

Thank you both for all your help!

 

[KOKOSEK]

Error in line with For? Weird. Are data there in column 5 on this Table called "List"?

BTW, obviously should be b and c, not a on bottoms lines

Sheet3.Range("G2").Resize(UBound(b), 1).Value = b        'column before /
Sheet3.Range("H2").Resize(UBound(c), 1).Value = c        'column after /

 

[Peter_SSs]

Thank you Peter, your example did what I was after.

Did it? I thought that you now wanted both columns

I want to copy and paste the 1234567 into a column of table 2 and then copy and paste the 003 into another column of table 2

Not sure it will go with tables in the destination if they are formal tables but otherwise this should give you both columns as once.

ws.ListObjects("List").ListColumns(5).DataBodyRange.Copy
With wsasn.Range("G2").Resize(ws.ListObjects("List").DataBodyRange.Rows.Count)
  .PasteSpecial Paste:=xlPasteValues
  .TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="/"
End With