How to enter a complex concatenation formula

[jaks999]

I have a column of payroll code data consisting of 12 character in column B, as follows:
"010203RP0001"

which needs to be expanded into an account code, in column C, of the following format:

"01.02.03.00.00.00.11900.0001"

My formula to create this is as follows:

=LEFT(B15,2) & "." & MID(B15,3,2) & "." & MID(B15,5,2) & ".00.00.00."  & VLOOKUP(MID(B15,7,2),sal_code,2,FALSE) & "." & RIGHT(B15,4)

When I insert this in VBA code, I get problems with adding the decimal places between every consecutive 2 digits, unless I enclose them in single digits like follows:

   ActiveCell.Formula = "=LEFT(B13,2) & '.'  & MID(B13,3,2) & '.' & MID(B13,5,2) & '.00.00.00.'  & VLOOKUP(MID(B13,7,2),sal_code,2,FALSE) & '.' & RIGHT(B13,4)"

But I also get a Object defined error. Does this possibly relate to the reference to the salary code table (sal_code) defined in the worksheet? If so, how do I get around this?

 

[pcmedic411]

Hello jaks999,

Did you record the formula entry with the macro recorder?

VBA formula debugging tip-
If your formula entered in VBA gives an error, perform the following steps:
Enter the formula in excel, and if you get the correct result then start the macro recorder and click in the formula cell, then click in another cell. Stop the recorder. If you look at your original formula you enterd in VBA and then at the formula the macro recorder created you may be able to notice any differences between the 2 formulas, thus finding the error.

Let me know if this helps.

Thanks,

Chuck

 

[VBslammer]

Just replace the single quotes with 2 quotes. Since you're inside a string already, this will format the formula properly.

I use the Debug.Print statement to troubleshoot this kind of thing:

[tt]
Debug.Print "=LEFT(B13,2) & ""."" & MID(B13,3,2) & ""."" & MID(B13,5,2) & "".00.00.00."" & VLOOKUP(MID(B13,7,2),sal_code,2,FALSE) & ""."" & RIGHT(B13,4)"
[/tt]

Then you can see if it works right in the immediate window:

[tt]
=LEFT(B13,2) & "." & MID(B13,3,2) & "." & MID(B13,5,2) & ".00.00.00." & VLOOKUP(MID(B13,7,2),sal_code,2,FALSE) & "." & RIGHT(B13,4)
[/tt]



VBSlammer

Unemployed in Houston, Texas

 

[jaks999]

Hello pcmedic411,

Yes your solution was helpful, and I used it for another formula that I needed to record. However, for the problem I wrote about, I decided to setup a cell with the formula that I could copy from, instead of creating and inserting the formula using VBA.

Sorry for the late reply, but I didn't get around to finishing my program until last night. Too many other things to do.

thanks.