• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Right Function

parth007

Member
I have one excel sheet which have data in Column "A"
Now need Vba code to only write last 3 letters of column "a" in column"B"
Below is the code

Code:
ActiveSheet.Range("B2:B5000") = Right(ActiveSheet.Range("A2"), 3)

Problem here is that column "A" may have n number of records in it... and the code only works for one single column..
any suggestion..
Attached is sample file.
 

Attachments

  • rIGHT fUNCTION.xlsx
    8.9 KB · Views: 1
Sorry Team members i got theanswer...

Code:
Dim SourceRange As Range, DestinationRange As Range, i As Integer
 
    'Define our source range as A1:A10 of Sheet1
    Set SourceRange = ActiveSheet.Range("A1:A5000")
 
    'Define our target range where we will print.
    'Note that this is expected to be of same shape as source
    Set DestinationRange = ActiveSheet.Range("B1:B5000")
 
    'Iterate through each source cell and print left 2 bits in target cell
    For i = 1 To SourceRange.Count
 
        DestinationRange(i, 1).Value = Right(SourceRange(i, 1).Value, 3)
 
    Next i
 

A very bad idea to use a loop for this kind of stuff ‼ :confused:
Worksheet functions are more efficient !​
Code:
    With Sheet1.Cells(1).CurrentRegion.Columns(2)
        With .Cells(2).Resize(.Rows.Count - 1)
            .FormulaR1C1 = "=RIGHT(RC[-1],3)"
                .Formula = .Value
        End With
    End With
Do you like it ? So thanks to click on bottom right Like !
 
Marc, actually me a Sql server DBA, but we are dependent on the data from excel which we receive from fellow members post we upload in SQL Database & work on it.. Unfortunatley the team is not available to provide the structured Excel data to us.. so as i have little knowledge of VB i am asked to streamline all reportings so that My original profile (SQL DBA/ Developer) will not face any delay in work..

So the best i could think is to take helpo from Expert in forums... :(
 
Back
Top