Copying data from one col. to another

Panoos64 · Oct 17, 2018

Hi all, kindly advise me so that to create a VBA code which should copying the left 7 characters from Col. “H” to col. “E” and place the account type in col. “D”. Account types are: If start col. “E” from 5 = D

4 = C

8= N

Below I present the original data and expected result

Thanking you in advance

Original data

[TABLE="class: MsoNormalTable, width: 427"]
<tbody>[TR]
[TD="width: 47"][/TD]
[TD="width: 118"]

D

[/TD]
[TD="width: 105"]

E

[/TD]
[TD="width: 105"]

F

[/TD]
[TD="width: 68"]

G

[/TD]
[TD="width: 269"]

H

[/TD]
[/TR]
[TR]
[TD="width: 47"]

1

[/TD]
[TD="width: 118"]

Account Type

[/TD]
[TD="width: 105"]

Account

[/TD]
[TD="width: 105"]

Description

[/TD]
[TD="width: 68"]

KK

[/TD]
[TD="width: 269"]

Transaction Description

[/TD]
[/TR]
[TR]
[TD="width: 47"]

2

[/TD]
[TD="width: 118"][/TD]
[TD="width: 105"][/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 269"]

5020201 EMPLOYEE 8

[/TD]
[/TR]
[TR]
[TD="width: 47"]

3

[/TD]
[TD="width: 118"][/TD]
[TD="width: 105"][/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 269"]

5020205 EMPLOYEE 15

[/TD]
[/TR]
[TR]
[TD="width: 47"]

4

[/TD]
[TD="width: 118"][/TD]
[TD="width: 105"][/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 269"]

5020312 EMPLOYEE 18

[/TD]
[/TR]
[TR]
[TD="width: 47"]

5

[/TD]
[TD="width: 118"][/TD]
[TD="width: 105"][/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 269"]

5020406 EMPLOYEE 22

[/TD]
[/TR]
</tbody>[/TABLE]

Expected result

[TABLE="class: MsoNormalTable, width: 6"]
<tbody>[TR]
[TD="width: 47"][/TD]
[TD="width: 118"]

D

[/TD]
[TD="width: 105"]

E

[/TD]
[TD="width: 105"]

F

[/TD]
[TD="width: 68"]

G

[/TD]
[TD="width: 277"]

H

[/TD]
[/TR]
[TR]
[TD="width: 47"]

1

[/TD]
[TD="width: 118"]

Account Type

[/TD]
[TD="width: 105"]

Account

[/TD]
[TD="width: 105"]

Description

[/TD]
[TD="width: 68"]

KK

[/TD]
[TD="width: 277"]

Transaction Description

[/TD]
[/TR]
[TR]
[TD="width: 47"]

2

[/TD]
[TD="width: 118"]

D

[/TD]
[TD="width: 105"]

5020201

[/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 277"]

5020201 EMPLOYEE 8

[/TD]
[/TR]
[TR]
[TD="width: 47"]

3

[/TD]
[TD="width: 118"]

D

[/TD]
[TD="width: 105"]

5020205

[/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 277"]

5020205 EMPLOYEE 15

[/TD]
[/TR]
[TR]
[TD="width: 47"]

4

[/TD]
[TD="width: 118"]

C

[/TD]
[TD="width: 105"]

4020312

[/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 277"]

4020312 EMPLOYEE 18

[/TD]
[/TR]
[TR]
[TD="width: 47"]

5

[/TD]
[TD="width: 118"]

N

[/TD]
[TD="width: 105"]

8020406

[/TD]
[TD="width: 105"][/TD]
[TD="width: 68"][/TD]
[TD="width: 277"]

8020406 EMPLOYEE 22

[/TD]
[/TR]
</tbody>[/TABLE]

Frank_Excel · Oct 17, 2018

Hi,

Try this one.

Code:

Sub Macro1()


    Dim x As Integer
    Application.ScreenUpdating = False
    
    ' Set numrows = number of rows of data.
    NUMrows = Range("H2", Range("H2").End(xlDown)).Rows.Count
    
    Range("D2").Select
    
    ' Establish "For" loop to loop "numrows" number of times.
    For x = 2 To NUMrows + 1
    
        ' Copy and paste value and formula
        Range("E" & x).Value = Left(Range("H" & x).Value, 7)
        ActiveCell.FormulaR1C1 = "=IF(VALUE(LEFT(RC[4],1))=5,""D"",IF(VALUE(LEFT(RC[4],1))=4,""C"",IF(VALUE(LEFT(RC[4],1))=8,""N"","""")))"
        ' Selects cell down 1 row from active cell.
        ActiveCell.Offset(1, 0).Select
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Ciao,
Frank

steve the fish · Oct 17, 2018

There will be loads of ways to do this. Here is one:

Code:

Dim lr As Long, arr, arr2, i As Long, x As Double, myCode As String

With Sheets("Sheet2")
    lr = .Range("H" & .Rows.Count).End(xlUp).Row
    If lr > 1 Then
        arr = .Range("D2:E" & lr)
        arr2 = .Range("H2:H" & lr)
        For i = LBound(arr2, 1) To UBound(arr2, 1)
            arr(i, 2) = CDbl(Left(arr2(i, 1), 7))
            x = CDbl(Left(arr2(i, 1), 1))
            Select Case x
                Case 5: myCode = "D"
                Case 4: myCode = "C"
                Case 8: myCode = "N"
                Case Else: myCode = ""
            End Select
            arr(i, 1) = myCode
        Next
        .Range("D2:E" & lr) = arr
    End If
End With

footoo · Oct 17, 2018

Here's another one :

Code:

Dim r As Range: Set r = Range([H2], Cells(Rows.Count, "H").End(xlUp))
Dim a$: a = r.Address
r.Offset(, -3) = Evaluate("index(left(" & a & ",7),)")
r.Offset(, -4) = Evaluate("if(" & a & "="""","""",if(left(" & a & ",1)=""5"",""D"",if(left(" & a & ",1)=""4"",""C"",""N"")))")

Panoos64 · Oct 17, 2018

Thank you Frank, the code works nicely. Thanks also for your time spent for my project. Hv a great day

Panoos64 · Oct 17, 2018

Hi Steve, perfect work.It works according to my data. Many thanks for your support. Hv a lovely day

Panoos64 · Oct 17, 2018

Thank you foot. Your code works too. Many thanks for your support. Hv a nice day

Copying data from one col. to another

Panoos64

Well-known Member

Excel Facts

Frank_Excel

New Member

steve the fish

Well-known Member

footoo

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Copying data from one col. to another

Panoos64

Well-known Member

Excel Facts

Frank_Excel

New Member

steve the fish

Well-known Member

footoo

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock