Macre gives ?Value

[Tim Hanson]

I can not get this working as a macro, the formula itself works

I am tiring to remove everything including the . to the right of the . e.g aaaaa.ntu.thuh I get aaaaa but I need aaaaa.ntu

Thanks

Sub DoesThisWork()
Dim c
Dim Lastrow As Long
    With Sheets("sheet1")
        Lastrow = .range("C" & .Rows.Count).End(xlUp).Row
    End With
    For Each c In range("E2:E" & Lastrow)
        c.Value = "=Left(A2, Find(""."", A2) - 1)"
    Next
   
    range("E1").Select
    ActiveCell.FormulaR1C1 = "Source"
End Sub

 

[NARAYANK991]

Hi Tim ,

Can you say what you want this macro to do ?

Narayan

 

[NARAYANK991]

Hi Tim ,

See if this is OK ?

Sub DoesThisWork()
    Dim c As Range
    Dim Lastrow As Long
   
    With Sheets("sheet1")
        Lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
        For Each c In .Range("E2:E" & Lastrow)
            c.Formula = Evaluate("=LEFT(" & c.Offset(, -2).Address & ", FIND("".""," & c.Offset(, -2).Address & ") - 1)")
        Next
 
        .Range("E1").Value = "Source"
    End With

End Sub

Narayan

 

[Tim Hanson]

NARAYANK991, sorry about the poorly worded question, I need to remove all characters to the right of the last "." (trere might be more then one) the cells to be evaluated are in column A and the output is to column E

The code you posted gives ?vaule

Thank you

 

[NARAYANK991]

Hi Tim ,

If each cell can contain more than one period , as in the following examples :

37.456.01
43.123456.333.1
4.222222.1.1.1.444.2
0.2222222.33.444.5555

then the formula to remove characters only to the right of the last period , would be :

=LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)

Can you try out this formula and confirm that it does what you want ? Thereafter we can look at coding this in VBA.

Narayan

 

[Tim Hanson]

NARAYANK991, thank you, your formula works perfectly, I tired adding it to vba but it highlights in red and throws an error.

Sub Splitter()
    Dim c As range
    Dim Lastrow As Long
 
    With Sheets("sheet1")
        Lastrow = .range("C" & .Rows.Count).End(xlUp).Row
        For Each c In .range("E2:E" & Lastrow)
            c.Formula = "=LEFT(A2,FIND("|",SUBSTITUTE(A2,".","|",LEN(A2)-LEN(SUBSTITUTE(A2,".",""))))-1)"
        Next
        .range("E1").Value = "Source"
    End With

End Sub

 

[NARAYANK991]

Hi Tim ,

Try this :

Sub ThisDoesWork()
    Dim c As Range
    Dim Lastrow As Long
 
    With Sheets("sheet1")
        Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
        For Each c In .Range("E2:E" & Lastrow)
            c.Formula = Evaluate("=LEFT(" & c.Offset(, -4).Address & ", FIND(""|"",SUBSTITUTE(" & c.Offset(, -4).Address & ",""."",""|"",len(" & c.Offset(, -4).Address & ")- LEN(SUBSTITUTE(" & c.Offset(, -4).Address & ",""."","""" )))) - 1)")
        Next
        .Range("E1").Value = "Source"
    End With

End Sub

Narayan

 

[Tim Hanson]

NARAYANK991 worked like a charm, thank you!!!