• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Reconfigure Marco

Zach

Member
I need some assistance with the attached spreadsheet. I am trying to build a template I can use where I can dump information into my "export" tab, have a basic concatenate function help feed the information to the "contract variance" sheet based on the information I input. And then take the new information from "contract Variance" from column J-M to create a tab that organizes the information in a way that I can use a vlookup to pull that information into the "input" tab based on plan, elevation, option number and cost code.

the attached file show the basic layout of the tabs but the macro part if far beyond my knowledge on how to count line items and convert them into a different format. Any help or possible suggestions to create a better flowing document would be greatly appreciated.

thanks
 

Attachments

  • Test Template.xlsx
    92 KB · Views: 5
I'm afraid your file does not match your description. There is no "input" tab. The sheet labeled "Contract variance template" appears like it's the front of the dashboard, but I'm unclear what other 4 sheets are for. Generally, you want one sheet to be the raw data, one sheet to do formulas/calculations, and one for the dashboard/report.

What you've got currently, it looks like the formulas on Dashboard are correctly pulling information in from "Export". However, you've not told us what you want to macro to do, exactly. Sheet doesn't even have column headers. :(

Perhaps you could explain, from the beginning, what exactly you need to happen with the data? We can see the raw form, what is the final output that you desire?
 
You know Luke as I was re-reading my post this morning. I'm surprised anyone answered because it was a lump of mass confusion. I'm attaching the file again with a bit more work that I've done and hopefully I'll create a better layout of what I need assistance with.

The file will start with 2 tabs "Contract Variance Template" and "Export".
1. The "Export" worksheet is just raw data pulled from our system and pasted into this file starting in cell B2.
2. The "Contract Variance Template" is a worksheet my company fills out when pricing changes within our division. I believe I have set this worksheet up to auto-fill all the info I need to populate based on my manual input of the greenish cells.

What I need to create are the Dark Green tabs "Import Data" and "Macro to build this tab" (I'm renaming this in the final product to "Variance Tab").
1. The "Macro to build this tab" is a worksheet I want to create that basically pulls the information from the "Contract Variance Template" and lays it out vertically so I can use a Vlookup in my "Import" tab. My main issue is I need the marco to count how many times an amount shows up under a particular cost code. I.E. 10 changes to amounts under the 30200 cost code but only 8 amount changes under the 30580 cost code, so I'll need 18 lines on this tab 10 30200 cost code and 8 30580.

I've written the create new worksheet macro, I just don't know how to tell it to only pull certain rows and columns.
Code:
Dim WS As Worksheet
Set WS = Sheets.Add
ActiveSheet.Name = "Variance Data"

2. The "Import" tab is just a duplicate of the "Export" tab except it is going to show the updated information from the "Contract Variance Template" plus any information that didn't change. I was thinking this tab could be just duplicated and have a formula pasted in column M that basically says If(the value in "import"A# doesn't show up in "Macro to build tab", then pull amount from "export", Pull amount from "macro to build tab") looks at the "Macro to Build this Tab" and pulls in the amount. My other issue here is if we are adding a new option that wasn't originally in the Export tab I need to create a line item (or line items) matching the current format so it all can upload.

I've written the code to create the copy of the export tab as well I just need to get the formulas written in here as well.
Code:
Sheets("Export").Copy After:=Sheets(2)
ActiveSheet.Name = "Import Data"

Again Expert advice or recommendations are greatly appreciated!
 

Attachments

  • Test Template.xlsm
    96.2 KB · Views: 6
@Luke M
Hi, buddy!
Out of town for Easter holiday, but with my.notebook to check how awful will the weather be, so I think that I could give it a look late tonight or tomorrow, it's supposed to be rainy and.windy.
Regards!
 
@Luke M
Hi!

Well I had the chance to give a quick look to this and it doesn't looks like nothing kwnow, unless for my reference to this thread which wasn't answered:
http://chandoo.org/forum/threads/au...w-rows-or-columns-are-added.13499/#post-81327

Downloaded the file and at a quick glance I wasn't involved, even I remember a couple of other projects about buildings and construction which have the business type in common with this, if my memory doesn't raise the parity error bit.

However I'll follow this thread just in case I may be able to help in a few days.

Regards!

PS: As I rely a bit on your non-ECC memory, if you happen to remember any clue about my work with those files, please do tell me.
 
Last edited:
Ok I've looked through that forum and all subsequent forums and I still haven't found what I'm looking for. I am still working within the file to try and create a macro to create a tab like the "Macro to build this tab" worksheet from the "Contract Variance Template". I can get the basic structure developed but I don't know how to properly tell the macro to look at the data on "Contract Variance Template", count the lines per cost code from row 12. and the create a worksheet to organize that information vertically.

Any idea here?
 
Hi Zach,

Looking at your 2nd file, I'm not able to see how the numbers match up to what you posted. Looking at Contract Variance worksheet...
Col J has 18 numbers, col K has 22. I'm assuming this is important, but I see that both of those codes have 18 lines in the export sheet. Using cell references, could you walk me through what you would need to macro to read/detect? Will we ever have more than 4 cost codes, or is that the max?
 
Yea, the lines didn't match up because I'm trying to get the code to create the information so I've been messing with the data. The new file attached has my most up to date work.

Ctrl+a will run the macro I've created so far and will create the 2 sheets I need for this process to be finalized. I've run the macro on the attached file so you can see what I'm looking at. The two green tab worksheets are just representations of the final products.

I've figured out how to get the macro to count the cells from J14:M65 and create a file where it puts all the information in a format that makes a vlookup easier. My problem now is trying to get formula's into the newly created worksheets so the information will feed freely.

The Macro creates a "Variance data" table in columns B-F and I need a concatenate formula in column A for columns b,c,d, and e.

In the "Import Data" worksheet, I need this formula to show up in Column M:
=IF(ISNA(VLOOKUP(A3,'Variance Data'!A:G,6,FALSE)),VLOOKUP(A3,Export!A:M,13,FALSE),IF(VLOOKUP(A3,'Variance Data'!A:G,6,FALSE)>0,VLOOKUP(A3,'Variance Data'!A:G,6,FALSE),VLOOKUP(A3,Export!A:M,13,FALSE)))

This formula(if I did it right) is saying to match my concatenate in column A to the concatenate in "Variance Data" and give me column 6, but if the concatenate cell is not in "Variance Data" match it to the Concatenate cell in "Export".

I hope this helps explain it clearer. I think I've gotten through the hardest part, but now the formulas are messing me up.
 

Attachments

  • Test Template (2).xlsm
    135.7 KB · Views: 1
Ok I got the first formula partially created but I'm running into a problem. If the constant in column B is number the formula works great, but if the constant is a + symbol then it doesn't calculate. What changes could I make to this to make it read both number and + symbol?
Code:
With Sheets("Variance Data")
        .Range("B2", .Range("B" & Rows.Count).End(xlUp)). _
            SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, -1).FormulaR1C1 = "=RC2&RC3&RC4&RC5"
    End With
 

Attachments

  • Test Template (2).xlsm
    137.9 KB · Views: 1
Hi Zach,

It looks like you want to look at all constants, not just the numbers then, so we'll remove that optional argument from your code. Please correct me if this is incorrect. Actually, not sure why you need special cells at all...don't all the cells in col A get the formula? Forgive me if I'm missing something.

I went through the entire code and cleaned it up a bit. Macro is running a lot faster now. :)
 

Attachments

  • Test Template L.xlsm
    112.1 KB · Views: 3
Thanks Luke-
I took some of your changes and altered my Macro to run a whole lot faster.

And now I'm trying to get a giant super formula into a cell on the "import data" tab and I tried using recorder but I don't think it's a very productive way of performing the task. I need column M to be a look up between the Variance data tab and the Export tab. If the value in A2 in "Import Data" worksheet is on the "Variance Data" worksheet pull over the corresponding amount, if not then pull the corresponding amount from "export" tab.

Code:
'Create Formula in Input Tab
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-12],'Variance Data'!C[-12]:C[-7],6,FALSE)),VLOOKUP(RC[-12],Export!C[-12]:C,13,FALSE),IF(VLOOKUP(RC[-12],'Variance Data'!C[-12]:C[-7],6,FALSE)>0,VLOOKUP(RC[-12],'Variance Data'!C[-12]:C[-7],6,FALSE),VLOOKUP(RC[-12],Export!C[-12]:C,13,FALSE)))"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M1000")
Range("P2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]"
Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P1000")
 

Attachments

  • Test Template (2).xlsm
    138.1 KB · Views: 1
Can definitely shorten that part up.
BTW, I noticed that instead of taking all of my code, you used a few pieces. Be careful to mind the leading "." in some of the spots, otherwise the correct parent object won't get referred to. :(
 

Attachments

  • Test Template L2.xlsm
    140.4 KB · Views: 7
I'm getting a Run-Time error '91': Object Variable or With Block not set at this point:

Code:
 lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row [code]
 
You've changed the code from what I gave you then, because there's no error on my machine. Did you try to copy and paste again? You might have missed the "With impSht line, or the statement at beginning where we define the impSht.
 
This thing is awesome and I am like 95% there. If I can find the time I'm going to learn this stuff better.
One last question and you can choose to help if you'd like. The attached file I highlighted the line on the Variance Data tab that was not originally on the export tab, but it is something that needs to show up on the import tab. Is there a way in the macro to verify that all the items in the "Variance tab" make it to the "import data" tab? I'm trying to find out if the macro can copy the last line of the Import data tab(with formula's) and paste it the correct amount of times, and replace the information (Cost code[if needed], plan, elev, option number). If the formulas are copied too once those items are there the amount will fill in automatically.

Let me know if this is possible?
 

Attachments

  • Test Template (2).xlsm
    137.2 KB · Views: 2
Sure thing. Check this file out.
 

Attachments

  • Test Template LM3.xlsm
    140.8 KB · Views: 1
Dude that's perfect, when I finish work today I'll be pulling up the VB and reading through the code to see if I can really understand how you made this last part work.

How long have you been doing stuff like this. I've really starting just trying to learn new things on this so knowing that stuff like this is possible is really cool.

Thanks again.
 
Ok Luke, I'm sorry but I need one more adjustment, only because I totally overlooked it. On the Export tab in column Q is the Bid Eff Date, when it copies to the Import tab the bid eff date stays the same. I actually need it to pull the date from the contract Variance template cell E9 "Date Effective"
 
Hi Zach,
I've been working with VB for about 8 years now. I started when I got repeated, mundane tasks as an intern, and figured there had to be a better way to do these tasks. Started by recording what I did and reading the code, and then started visiting forums. First reading what other experts had to say, then dipping my toes in the water and taking at crack at answering a few on my own. :)

To modify the code, we'll just add 1 more line at the end, commented in this snippet.
Code:
'Create Formula in Input Tab
With impSht
    'Determine last row with data on worksheet
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    .Range("M2:M" & lastRow).FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-12],'Variance Data'!C[-12]:C[-7],6,),Export!RC)"
    .Range("P2:P" & lastRow).FormulaR1C1 = "=RC[-3]"
    'NEW LINE: Change the date
    .Range("Q2:Q" & lastRow).Value = CVT.Range("E9").Value
End With
     
End Sub
 
Ok Luke, the file is doing what its supposed to do plus a little more. it is replacing values the way I needed it too, but it is also creating the extra lines at the bottom of the file from the Variance data tab. I've attached the file and highlighted the with yellow the items that are duplicate. Is that happening because I screwed something up?
 

Attachments

  • Test Template (2).xlsm
    131 KB · Views: 1
No, I forgot that col A was normally hidden, so Find isn't able to search those cells. Added in a line to temporarily unhide the column, and then do the searches. Seems to be working now.

BTW, I saw that you had a circular reference error on main sheet. Doesn't effect the macro, but wanted to let you know.
 

Attachments

  • Test Template Find.xlsm
    130.1 KB · Views: 3
Back
Top