I'm an Oracle programmer with no VBA knowledge or experience. I've been asked to modify some Excel macros that were written for a report we get from our payroll provider to make it easier to view and then search for some different scenarios.
The payroll provider changed the layout of the report so I need to alter the macro accordingly.
The old report had accounting codes on each row for the employee. The new report groups the employees by the accounting codes with the codes listed on three rows with a varying number of rows below with the employees assigned to that accounting code.
The accounting codes are separated by a slash character so it looks like this:
code1/code2/code3/code4/code5
code6/code7/code8/code9/
code10/code11/code12
EmployeeID Name (Last Name, First Name) Pos# EarnCode Earnings
N1234567 Messi, Leonardo 09876 LT1 $ 3,185.55
N2345678 Iniesta, Andreas 08765 REG $ 1,980.10
N3456789 Hernandez, Xavier 07654 REG $ 2,027.80
code1/code2/code3/code4/code5
code6/code7/code8/code9/
code10/code11/code12
EmployeeID Name (Last Name, First Name) Pos# EarnCode Earnings
N4567890 Hazard, Eden 06543 REG $ 2,187.15
N5678901 Fabregas, Cesc 05432 LT1 $ 1,875.60
N6789012 Terry, John, 04321 NBO $ 1,290.50
N7890123 Thibaut, Courtois 03219 REG $ 1,365.25
I need to move code4 and code7 to separate columns for every employee row below them, and then obviously changing the variable values when the accounting codes change for their appropriate employees. If it were SQL, I would use instr to find the position of the third slash and then take the next five characters like this:
substr(instr(ACCT_CODES, '/', 3), 5)
SQL's instr function can count the number of occurences of the item in question but in VBA it can only find the first location (from what I can find.) I've tried the VBA function Split but haven't made it work yet.
How can I pull out what I need and assign it to a variable to be used on the rows below?
You can use split to split a string to an array, Split(ACCT_CODES,"/")(6) will give you the 7th and (3) will give you the 4th