CODEWITHSUNDEEP
vbaexcel
AriKari
AriKari

Reputation: 323

Return the last word from a sentence

Trying to get I/O as follows: 

     Col B is input        Col C will get output
 1   ABC DEF JKH              JKH
 2   KJJB ljac Kjb            Kjb
 3   jabcl sjac casc          casc
 . 
 .

But I am getting the error on line 11 as "invalid procedure call or argument":

    Option Explicit
    Sub seperate()
    Dim strarr() As String
    Dim str As String
    Dim i, j As Integer
    Dim k As Long
    Dim ws As Worksheet

    Set ws = Sheets("Main")
    k = ws.Range("B1048576").End(xlUp).Row

    For i = 1 To k
    str = Cells("i,B").Value 'Error here as Invalid proc call or argument
    strarr() = Split(str)
    j = UBound(strarr())
    Cells("i,C").Value = strarr(j)
    Next i
    End Sub

Upvotes: 0

Views: 112

Answers (3)

Gary's Student
Gary's Student

Reputation: 96791

Consider:

Option Explicit

Sub separate()
   Dim strarr() As String
   Dim sttr As String
   Dim i As Long, j As Long
   Dim k As Long
   Dim ws As Worksheet

   Set ws = Sheets("Main")
   k = ws.Range("B1048576").End(xlUp).Row

   For i = 1 To k
      sttr = Cells(i, "B").Text
      strarr = Split(sttr)
      j = UBound(strarr)
      Cells(i, "C").Value = strarr(j)
   Next i
End Sub

Upvotes: 3

A.S.H
A.S.H

Reputation: 29352

str = Cells("i,B").Value 'Error here as Invalid proc call or argument

Wrong syntax. Should be Range("B" & i) or Cells (i, "B")

Upvotes: 0

Excel Hero
Excel Hero

Reputation: 14764

Here you go.

Sub Santosh()
    Dim i&, v, w
    v = Worksheets("Main").[b1:index(b:b,match("*",b:b,-1))].Value
    For i = 1 To UBound(v)
        w = Split(v(i, 1), " ")
        v(i, 1) = w(UBound(w))
    Next
    Worksheets("Main").[c1].Resize(UBound(v)) = v
End Sub

Upvotes: 1

Related Questions