Enter codes next to description VBA

Panoos64 · Nov 7, 2018

Hi all, kindly advice me for a VBA code so that to run through col. “A” and place the codes in adjacent cells to description, col. “B” but for the last 2 rows. The codes are follow:

[TABLE="class: MsoNormalTable, width: 128"]
<tbody>[TR]
[TD="width: 138"]

TRADE UNION

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

5012505

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

SOCIAL INSURANCE

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

5012508

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

MEDICAL FUND

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

5012512

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

TAX DUE

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

5012518

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

Below I present the original data and expected result

Thanking you in advance

Original data

[TABLE="class: MsoNormalTable, width: 181"]
<tbody>[TR]
[TD="width: 138"]

DEDUCTIONS

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

555.01

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

SOCIAL INSURANCE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

2,528.36

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

MEDICAL FUND

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

125.45

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

TAX DUE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

486.81

[/TD]
[/TR]
[TR]
[TD="width: 138"][/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

CONTRIBUTION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

SOCIAL INSURANCE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

2,528.36

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

MEDICAL FUND

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

125.45

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

TAX DUE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"][/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

DEDUCTIONS

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

555.01

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

SOCIAL INSURANCE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

2,528.36

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

MEDICAL FUND

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

125.45

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

TAX DUE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

486.81

[/TD]
[/TR]
[TR]
[TD="width: 138"][/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

CONTRIBUTION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

SOCIAL INSURANCE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

2,528.36

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

MEDICAL FUND

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

125.45

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

TAX DUE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
</tbody>[/TABLE]

Expected result

[TABLE="class: MsoNormalTable, width: 181"]
<tbody>[TR]
[TD="width: 138"]

DEDUCTIONS

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

555.01

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

SOCIAL INSURANCE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

2,528.36

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

MEDICAL FUND

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

125.45

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

TAX DUE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

486.81

[/TD]
[/TR]
[TR]
[TD="width: 138"][/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

CONTRIBUTION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

SOCIAL INSURANCE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

2,528.36

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

MEDICAL FUND

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

125.45

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

TAX DUE

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"][/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

DEDUCTIONS

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

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

5012505

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

555.01

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

SOCIAL INSURANCE

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

5012508

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

2,528.36

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

MEDICAL FUND

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

5012512

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

125.45

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

TAX DUE

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

5012518

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

486.81

[/TD]
[/TR]
[TR]
[TD="width: 138"][/TD]
[TD="width: 75"][/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

CONTRIBUTION

[/TD]
[TD="width: 75"][/TD]
[TD="width: 88"]

AMOUNT

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

TRADE UNION

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

5012505

[/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD="width: 138"]

SOCIAL INSURANCE

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

5012508

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

2,528.36

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

MEDICAL FUND

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

5012512

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

125.45

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

TAX DUE

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

5012518

[/TD]
[TD="width: 88"][/TD]
[/TR]
</tbody>[/TABLE]

Joe4 · Nov 7, 2018

Just have your VBA code insert a VLOOKUP formula to look up the listing and return the corresponding code.
You can get a lot of the needed code by turning on the Macro Recorder, and record yourself doing that manually.
If you need help cleaning up the code or making it more dynamic after doing that, post that recorded code here along with the specifics of the changes you need to make.

Panoos64 · Nov 7, 2018

Hi Joe, I will proceed with your above advice, but therefore i will coding the whole col. "B". However is a significant point that except the last 2 section, the others should be blank and so whenever i will prepare it i will return to get new instructions. Thank you once again for your continuance support. Hv a great day

Joe4 · Nov 7, 2018

Why does your data appear to be duplicated like that?
What is the logic for determining where these codes should start?

Panoos64 · Nov 7, 2018

Hi, The first 2 are the records of each department and i is not necessary to code them. The important records are the summary of the project which are the last 2 sections so is significant to coding only the summary of them and not coding each department. Just i present one department and is the reason that the figures are duplicate.
In my opinion there is no point to start. The code should run through col. "A" and should coding the last 2 sections and ignore any other above them.
Thank u for your intention to resolve my issue.

Joe4 · Nov 7, 2018

Will the last two sections always be title "DEDUCTIONS" and "CONTRIBUTION"?

Where is that lookup grid that shows the department and codes located (as we will need to reference that in our formula)?
If we could give that a named range, it will make it much easier to work with.

Joe4 · Nov 7, 2018

OK, assuming that my assumptions up above are true, you already have a blank column B, and you name your lookup grid "CodeRange", this code should do what you want:

Code:

Sub AddCodes()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with data
    r = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through rows backwards
    Do Until r = 1
        Select Case Cells(r, "A")
'           Exit sub when you hit "DEDUCTIONS"
            Case "DEDUCTIONS"
                Exit Sub
'           Do nothing if you hit "CONTRIBUTION" or blank
            Case "CONTRIBUTION", ""
'               Do nothing
'           Otherwise enter vlookup formula
            Case Else
                Cells(r, "B").FormulaR1C1 = "=VLOOKUP(RC[-1],CodeRange,2,0)"
        End Select
'       Move up one row
        r = r - 1
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Panoos64 · Nov 8, 2018

Well done Joe, it works perfect and base on my data. There are headings about departments names, but it works so and i do not like to inconvenience you again. I appreciated so much for what you done for my projects and for your kind support. Hv a great lovely date, all the best!

Joe4 · Nov 8, 2018

You are welcome.

Enter codes next to description VBA

Panoos64

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

Enter codes next to description VBA

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP, Junior Admin

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