VBA converts the telephone fee schedule of telecommunication into departmental telephone expense schedule

Today, colleagues to do a job like this, is the telecommunications sent to the cost of the table, divided by the department of the telephone fee table, more than 100 calls, if one to check, really still need some time. The question is, is there a better way, let's take a look.

The price details given by the telco are the same, and the individual numbers are not combined with the cost.

First, through the filtering function of Excel, remove the blank line of the number, so that the number and cost to put a little closer, because the number of rows in the middle of the data is not fixed, so at least one line, it is better to do more.

As the row is staggered, the whole cut of the cost row data is moved up one line to the same line as the number. Then you can delete the unused rows.

This is the result of the conversion, such as column A and column C. Because there is no area code display in the departmental expense table, the two docking needs to deal with the number, this does not take a column to the right 8. There is a special case of broadband number that needs to be handled manually.

This is the Department fee table, the above data need to update the cost of the corresponding number in the table below. The original two cost tables with inconvenient first put in a sheet, so it is more convenient to find.

Write a VBA program, loop through the numbers, then look in the Department number table and replace the values on the right. There is an error in the processing, otherwise if you do not handle the words can not find the number when the error will be executed.

Sub getDate ()    on Error resume Next                                         ' does not error continue execution for        CLN = 1 to 147            PhoneNumber = Sheets ("Sheet1"). Cells (CLN, 2) Money            = Sheets ("Sheet1"). Cells (CLN, 3)                                         Dim rng as Range            Set rng = Worksheets ("head Office"). Range ("e1:e187"). Find (PhoneNumber,,, 1)                      ' MsgBox (rng. Address)                        rng. Offset (0, 1). Value =                        Money ' offset one column to the right is the cost value                If err.number = 0 Then                Sheets ("Sheet1"). Cells (CLN, 4) = "OK"             successfully identifies                            End if                        if err.number <> 0 then                Sheets ("Sheet1"). Cells (CLN, 4) = "Error"          wrong ID, focus on                err.number = 0            End If    nextend Sub

Procedures are finished, and then check, the special number of a few more processing will be OK.

VBA converts the telephone fee schedule of telecommunication into departmental telephone expense schedule