Customer Profilesv4 - Paul 7.24.14.xlsx
In the CurrentCustomer WS, Column B is "Commodity", I need to insert a new row for the commodity "Red Kale" between rows 6 & 7 repeating every 23 rows down to 446 & 447. Is there a simple way to do this without manually copying and inserting every row manually?
Thanks,
Hi ExcelDummy
This can be done with VBA Code. Do you need to do the same for DevelopingCustomers?
Will this be a tool you'll need to use again for additional Commodities?
Last edited by jaslake; 08-09-2014 at 01:44 PM.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
Jadlake,
Thanks for the response. This will need to be done on DevelopingCustomers and yes it would be a tool I would use again when adding additional commodities.
Hi ExcelDummy
Let me play with it a bit...see what I can come up with.
Hi ExcelDummy
Create a Test Folder...place both of the attached Workbooks in that Test Folder. Open the TestBook v1.1.xlsm. Click the Button.
The Code will ask you which Customer Profile Workbook you wish to modify. Select that Workbook (only one in this Test Folder...Customer Profilesv4 - Paul 7.24.14.xlsx)
The Code will then ask you to select the Row ABOVE which you wish to Insert a New Commodity. It'll further ask you to Name the New Commodity.
This Code is in TestBook and can be run on any Customer Profile Workbook that's of the SAME STRUCTURE.
Please Login or Register to view this content.
Jaslake,
Thanks, but I keep getting a runtime error 13 - type mismatched?
Hi ExcelDummy
On what line of Coderuntime error 13 - type mismatched
Here is the highlighted code line from the debug screen:
YesNo = MsgBox(nCell & " will be inserted before every occurance " & rCell, vbYesNo + vbCritical)
Hi ExcelDummy
Are you using the Files I uploaded or different Files?
I followed your instructions above, created the test folder on my desktop, downloaded the attached files into the test folder, then ran the testbook file.
Hi ExcelDummy
I modified the Code slightly but of little consequence/significance. I see you're using Excel 2013 and I have no access to that. On my platform the Code performs as expected in both Excel 2007 and 2010.
Replace the TestBook v1.1.xlsm with the attached...let me know.
Still getting the same runtime error. Maybe its a difference in the excel versions? Thanks for trying. I don't want you to have to spend anymore time on this if you don't want.
Thanks,
Hi ExcelDummy
Are you using Excel 2013?
Yes, I am using Excel 2013
Alright...I'll try to get some help from a Member that has access to 2013...
just for test purpose change the DIM for Response to Variant and see what that does
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
Hi ExcelDummy
The Code in the attached incorporates the change suggested by Ernest. Ernest appears to have Excel 2010 and the Code performs as expected in 2010.
Replace the old File with this File. Run the Code...let us know...
thanks John
@Ernest
You're welcome...don't suppose you have access to Excel 2013???
nope...not really sure I want too....I figured Variant will cast the data type to whatever it needs....AND you can look in the locals window and see what the system is setting the data type too....yes, my English syntax sucks....
@Ernest
I had previously tested Variant in 2007 and 2010 and, of course, it worked. We'll see if 2013 likes it...hopefully.
Thanks for the input.
Original code works in xl2013.
@exceldummy, can you check if you have any missing references.
With the code workbook open,
ALT+F11 to go to VBE
Tools > References.
Any missing references will appear near the top of the list.
You must select only one cell and not whole row - this will cause Type Mismatch error.
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
@Izandol
I believe you may have discovered the issue...hope the OP hasn't given up.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks