Macro to take data from string and put it in another cell

[3thr3e]

Hi,

I have a column of data (column D) which contains a 3 digit string I want to extract from it. I've come up with the code below which replaces each cell in D with that 3 digit string. However, this is not what I want to do. I want to keep each cell in column D the same, but put that 3 digit string in column A (same row number).

Is it just a simple modification to the code below? Also I would like the loop to start from D2 and go down (as D1 is a column heading). And obviously A2 when putting the sxtracted data in.

Any help would be much appreciated

Sub HotelName()
Dim LR As Long, i As Long, myCell As String
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("D" & i)
If Left(.Value, 3) <> "999" Then
.Value = " " & .Value
Else
.Value = Mid(.Value, 14, 3)
End If
End With
Next i
End Sub

 

[VoG]

Try

Sub HotelName()
Dim LR As Long, i As Long, myCell As String
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("D" & i)
        If Left(.Value, 3) <> "999" Then
            .Offset(, -3).Value = " " & .Value
        Else
            .Offset(, -3).Value = Mid(.Value, 14, 3)
        End If
    End With
Next i
End Sub

 

[3thr3e]

Worked a treat - Thanks!

Try

Sub HotelName()
Dim LR As Long, i As Long, myCell As String
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("D" & i)
        If Left(.Value, 3) <> "999" Then
            .Offset(, -3).Value = " " & .Value
        Else
            .Offset(, -3).Value = Mid(.Value, 14, 3)
        End If
    End With
Next i
End Sub