create/copy formula to adjacent cell/s

wgb789

New Member
Joined
Apr 26, 2018
Messages
6
Good morning to all,

I have the following sample data in column "A".
I've only shown 8 rows worth of data which could increase to 3000+, what I want to do is extract the last 4 digits into Column "B" only for the data shown

T/PR/T3B/01PA
T/PR/T3B/01PA
T/PR/T3B/01PA
T/PR/T3B/01PA
T/PR/T3B/01PA
T/PR/T3B/01PB
T/PR/T3B/01PB
T/PR/T3B/01PB


<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
I can create VBA to copy a formulainto "B1" what I can't get my head round is to copy only where data exists in column "A".
This is part of a larger VBA exercise and this is the stumbling block.

As you may have guessed I am a complete novice at VBA so any help would be much appreciated.
Thanks in advance.
Bill.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,829
Office Version
  1. 2013
Platform
  1. Windows
Try this:
Code:
[LEFT][COLOR=#222222][FONT=Tahoma]Sub Copy_Over()[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]'Modified  8/14/2018  6:12:28 AM  EDT[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Dim i As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Dim Lastrow As Long[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Lastrow = Cells(Rows.Count, "A").End(xlUp).Row[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]For i = 1 To Lastrow[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]    If Cells(i, 1).Value <> "" Then Cells(i, "B").Value = Right(Cells(i, 1), 4)[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Next[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]Application.ScreenUpdating = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Tahoma]End Sub[/FONT][/COLOR][/LEFT]
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,235
Office Version
  1. 365
Platform
  1. Windows
A couple of other approaches to also consider if you want.
I have assumed that there is a heading row and the data you want to act on starts at row 2. If it does start in row 1 then change "A2" to "A1" in my codes.

If the data in column A is always formatted like your samples, and you want to extract what is after the last "/", then you could try this
Code:
Sub LastPart1()
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
    .Value = .Offset(, -1).Value
    .Replace What:="*/", Replacement:="", LookAt:=xlPart
  End With
  Application.ScreenUpdating = True
End Sub

If the data could be more variable & you want the last 4 characters
Code:
Sub LastPart2()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(#="""","""",right(#,4))", "#", .Address))
  End With
End Sub
 

wgb789

New Member
Joined
Apr 26, 2018
Messages
6
Thank you both ("My answer is this" and "Peter_SSs") in this instance I have used "my answer is this" code and it worked perfectly.
Again many thanks for your very prompt replies
Bill.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,829
Office Version
  1. 2013
Platform
  1. Windows
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
Thank you both ("My answer is this" and "Peter_SSs") in this instance I have used "my answer is this" code and it worked perfectly.
Again many thanks for your very prompt replies
Bill.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,235
Office Version
  1. 365
Platform
  1. Windows
Thank you both ("My answer is this" and "Peter_SSs") in this instance I have used "my answer is this" code and it worked perfectly.
Again many thanks for your very prompt replies
Bill.
You're welcome. Glad you got something you are happy with. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,019
Messages
5,526,288
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top