# create/copy formula to adjacent cell/s

#### wgb789

##### New Member
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.
Bill.

### 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
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
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
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
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
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.

Replies
11
Views
105
Replies
0
Views
146
Replies
2
Views
114
Replies
5
Views
56
Replies
0
Views
44