I'm trying to create a macro for someone at work and i'm having some issues. I'm just learning and i'm not super great at macros but I'm working on it.
First of, i'd like to attach the sheet but my Boss is super picky about this stuff. Which makes stuff like this hard. So I'll do my best to explain what i'm trying to do.
First off we export a report that has a list of a bunch of owner names. This is created by someone else so we have no control over it. We are trying to create a macro that formats and organizes the information the same way. The formatting takes about an hours to do each time and my boss exports the report a couple times a week.
We got the formatting to work but now we are on the the next problem. I'll try to explain the best I can. Once we get it formatted and organized we create a pivot table so we can analyze the information. The issue we are having is the owners name in the list isn't always consistent. Typically we use a LEFT function to grab the first 5 or 10 characters to combat that issue but in this case it that won't work because sometimes the very first letter is different on the owners names column even though it is the same name. Look at the table below. It is over simplified but gives the ides.
So if we look at "Owner 3" for example. There are 9 entries for owner 3 but 6 different ways the name displays. When we create the pivot we want them to be treated like the same owner. Long story short (or long at this point
) we created a big IF statement that calls on a table in a different sheet (named "Tables"). Basically the statement says if "Owner 3a" change to "Owner 3" and so on. It is a lot more complicated than that but you get the idea.
Here is the IF statement we are trying to use:
If we do it manually in the sheet it works. But when we try to create a macro inserting this formula the macro breaks when we try to copy the formula down the row.
This is the macro that is written right now
Now if we just paste the formula into the cell next to the owner name it works. If we manually copy it down it works. It is when we try to put the formula in the macro that it breaks.
I know this is super complicated without seeing the sheet, but i'm hoping you can help us.
First of, i'd like to attach the sheet but my Boss is super picky about this stuff. Which makes stuff like this hard. So I'll do my best to explain what i'm trying to do.
First off we export a report that has a list of a bunch of owner names. This is created by someone else so we have no control over it. We are trying to create a macro that formats and organizes the information the same way. The formatting takes about an hours to do each time and my boss exports the report a couple times a week.
We got the formatting to work but now we are on the the next problem. I'll try to explain the best I can. Once we get it formatted and organized we create a pivot table so we can analyze the information. The issue we are having is the owners name in the list isn't always consistent. Typically we use a LEFT function to grab the first 5 or 10 characters to combat that issue but in this case it that won't work because sometimes the very first letter is different on the owners names column even though it is the same name. Look at the table below. It is over simplified but gives the ides.
| Owner 1 |
| Owner 2 |
| Owner 3 |
| Owner 1a |
| Owner 1b |
| Owner 1c |
| Owner 2 |
| Owner 2a |
| Owner 3 |
| Owner 3 |
| Owner 3 |
| Owner 3a |
| Owner 3b |
| Owner 3b |
| Owner 3c |
| Owner 3d |
So if we look at "Owner 3" for example. There are 9 entries for owner 3 but 6 different ways the name displays. When we create the pivot we want them to be treated like the same owner. Long story short (or long at this point
) we created a big IF statement that calls on a table in a different sheet (named "Tables"). Basically the statement says if "Owner 3a" change to "Owner 3" and so on. It is a lot more complicated than that but you get the idea.Here is the IF statement we are trying to use:
| =IF(C2=Tables!$B$5,Tables!$C$5,IF(C2=Tables!$B$6,Tables!$C$5,IF(C2=Tables!$B$7,Tables!$C$7,IF(LEFT(C2,3)=Tables!$B$9,Tables!$C$9,IF(LEFT(C2,10)=LEFT(Tables!$B$17,10),Tables!$C$17,IF(C2=Tables!$B$19,Tables!$C$19,IF(LEFT(C2,6)=Tables!$B$20,Tables!$C$19,IF(LEFT(C2,14)=Tables!$B$26,Tables!$C$26,IF(C2=Tables!$B$29,Tables!$C$29,IF(C2=Tables!$B$30,Tables!$C$30,IF(ISNUMBER(SEARCH(Tables!$B$32,C2)),Tables!$C$32,IF(LEFT(C2,8)=LEFT(Tables!$B$36,8),Tables!$C$36,IF(LEFT(C2,20)=Tables!$B$38,Tables!$C$38,IF(LEFT(C2,9)=LEFT(Tables!$B$41,9),Tables!$C$41,IF(ISNUMBER(SEARCH(LEFT(Tables!$B$45,5),C2)),Tables!$C$45,IF(ISNUMBER(SEARCH(Tables!$B$48,C2)),Tables!$C$48,IF(LEFT(C2,8)=LEFT(Tables!$B$52,8),Tables!$C$52,IF(ISNUMBER(SEARCH(Tables!$B$54,C2)),Tables!$C$54,IF(ISNUMBER(SEARCH(Tables!$C$56,C2)),Tables!$C$56,C2))))))))))))))))))) |
If we do it manually in the sheet it works. But when we try to create a macro inserting this formula the macro breaks when we try to copy the formula down the row.
This is the macro that is written right now
Code:
Sub Macro28()'
' Macro28 Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
ActiveSheet.Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(C2=Tables!$B$5,Tables!$C$5,IF(C2=Tables!$B$6,Tables!$C$5,IF(C2=Tables!$B$7,Tables!$C$7,IF(LEFT(C2,3)=Tables!$B$9,Tables!$C$9,IF(LEFT(C2,10)=LEFT(Tables!$B$17,10),Tables!$C$17,IF(C2=Tables!$B$19,Tables!$C$19,IF(LEFT(C2,6)=Tables!$B$20,Tables!$C$19,IF(LEFT(C2,14)=Tables!$B$26,Tables!$C$26,IF(C2=Tables!$B$29,Tables!$C$29,IF(C2=Tables!$B$30,Tables!$C$30,IF(ISNUMBER(SEARCH(Tables!$B$32,C2)),Tables!$C$32,IF(LEFT(C2,8)=LEFT(Tables!$B$36,8),Tables!$C$36,IF(LEFT(C2,20)=Tables!$B$38,Tables!$C$38,IF(LEFT(C2,9)=LEFT(Tables!$B$41,9),Tables!$C$41,IF(ISNUMBER(SEARCH(LEFT(Tables!$B$45,5),C2)),Tables!$C$45,IF(ISNUMBER(SEARCH(Tables!$B$48,C2)),Tables!$C$48,IF(LEFT(C2,8)=LEFT(Tables!$B$52,8),Tables!$C$52,IF(ISNUMBER(SEARCH(Tables!$B$54,C2)),Tables!$C$54,IF(ISNUMBER(SEARCH(Tables!$C$56,C2)),Tables!$C$56,C2)))))))))))))))))))"
ActiveCell.Offset(1, 0).Range("A1").Select
End SubNow if we just paste the formula into the cell next to the owner name it works. If we manually copy it down it works. It is when we try to put the formula in the macro that it breaks.
I know this is super complicated without seeing the sheet, but i'm hoping you can help us.
