LinkBack URL
About LinkBacks
Hi Guys,
I have been using the below code to transpose some order numbers
What I want to know if it is possible to transpose the order numbers into everyother cell?
Please Login or Register to view this content.
many thanks
Jamie
Hi Guys,
I have been using the below code to transpose some order numbers
What I want to know if it is possible to transpose the order numbers into everyother cell?Please Login or Register to view this content.
many thanks
Jamie
Last edited by alansidman; 09-14-2016 at 10:58 AM. Reason: code tags added
Hi,
There are many possible ways- for instance
Please Login or Register to view this content.
Last edited by xlnitwit; 09-14-2016 at 12:09 PM.
Don
Please remember to mark your thread 'Solved' when appropriate.
Thats awesome thank you so much ^_^
could I ask you a question about naming those copied values
Certainly- what is it?
is there a way that you could name those copied ranges based on the value that has just been copied.
so say you copied 1234 onto sheet 2 B2. could you then name the range b2:c3 using the value you have just copied.
does that make sense?
Yes- but 1234 is not a valid name for a range. Are the copied values in fact numbers?
Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html
(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
my appologies will remember for next time
some will be numbers and some will be text. is there anyway around it? like adding a letter to the start of the account number or something?
It would be possible to add an underscore which should make it a valid name?
Please Login or Register to view this content.
Last edited by xlnitwit; 09-14-2016 at 12:09 PM.
got it
could you transpose column A:B from sheet 1 on To Sheet 2 and name the same range b2:c3 with the first value
JohnsTyres 1234 and the range would be named Johns tyres
its coming up with a runtime error 13 mismatch
[OutRange.Resize(1, 2).Name = "_" & InRange.Value2}
Whoops! I will correct that forthwith. Did you mean B2:C3 or B2:C2 as the range to name?
B2:C3 and thank you really appreciae your help
In that case this should do the job
Please Login or Register to view this content.
Thank you ^_^
Unfortuantly its now coming up with a runtime error 1004
Morning,
Which line is causing the error?
its saying object required but i have checked and all of the Ref's are correct.
Are you certain that the code names for your worksheets are Sheet1 and Sheet2? Code names are not necessarily the same as the text shown on the worksheet tab.
so I have checked all of the references and they are fine. when I run the code it copies the first value from sheet 1 and then stops
Sorry for being a pain xlnitwit
Simply stops without error?
I belatedly created a test workbook this morning and ran the code on it without any errors. Is it possible for you to attach your workbook here?
Hi,
I have attached the demo workbook I am using.
The problem is that you have duplicate rows in your source data and you cannot give two ranges the same name. How would you like to handle that?
Remove the duplicates
I have removed the duplicates and the same error is still occuring.
The following should work with that dataset- fixing the duplicate issue as well as coping with characters that are not valid in a name, which will be replaced with an underscore
Please Login or Register to view this content.
Hi,
I have been playing around with the above for ages and cant quite work out what I would need to amend for it to copy A,B & C instead of just A & B can you help me?
Change to this
Please Login or Register to view this content.
I have just tried to copy this over and it is coming up with a compile error sub or function not defined
Did you keep the other function code?
So I amended the code as advised but it is still only transposing A:B
Can you post a workbook including the code as you are using it at the moment please?
Here you go
That file is missing an important ingredient- the code as you are using it at the moment please.
my appologies please see attached
Oh dear- I'm afraid I overlooked a crucial change! Please use this
Please Login or Register to view this content.
Thats awesome thank you so much. if I was to use this for text as well is what can i remove to remove the underscore?
On the assumption you mean the leading underscore only, remove the red section here
Please Login or Register to view this content.
Hi XLNIT WIT,
Could i have some help please as I feel as though I am about to cry LOL
With what do you need help?
Hi,
So i have utilised all of the code over the last couple of days to make the attached workbook. What should happen is
1. Is that ConcatenateABandRemoveAllValueswhichwillerror Should run through sheet 1 and create the names for soon to be ranges on sheet2.
2. part of this also removes any random values such as (., spaces, brackets and so on) so as not to cause any conflicts.
3. then it should use the values in A1 to create ranges on sheet2
4. and then copy cells D & E into the range defined in A
5. But it keeps on erroring and I dont know why, as everything that i have put in place should ensure that the ranges dont error.
Last edited by bloodmilksky; 09-22-2016 at 08:18 AM. Reason: wrong workbook uploaded
Have you tried debugging the code by stepping through it with f8? If you were to do so, you will likely see that it fails in many places but is certainly not helped by the fact that column B consists solely of error values.
I have amended colum B as it should show all the Email Addy's. I am just awaiting for more information to be able to get the rest of them together.
Would you know how I would fix this.
the one I have attached now shows some of the emails that are their.
but still isnt creating the ranges.
I am sorry to ask so many questions but I am very new to VB and am just running on the little knowledge I have.
would it matter if I changed the error values to just a dummy email just to confirm that It can actually work?
That will certainly assist. In addition, in your CopySheet1A1toSheet2NextCellInColumnA procedure, you should loop from row 2, not from row 1, since that row contains headers rather than data.
is that this bit?
'Loop through all used cells on column A with a value
For l = 1 To lLastRow
That's correct- you need to start at row 2.
Should it look like this? sorry for asking so many questions
'Loop through all used cells on column A with a value
For l = 2 To lLastRow
Yes it should.
for some reason it is still showing as an error
Please post a sample workbook again with the corrected code and data without error values
would it affect the code if the amount of data has changed?
It shouldn't- which line is in error now?
Unless perhaps you have too many items and run out of columns.
well for the moment it is working okay. but I have yet to run the code which sorts all of the values into the named ranges.
Hi,
So I have tried to Loop it from Row 2 but it is just not working. the transpose code is perfect has set it all up perfectly and without creating duplicates but the sorting code just refuses to work and i am sure it is something really obvious I am missing
Hello again,
I presume you are referring to the CopySheet1A1toSheet2NextCellInColumnA procedure? If so, could you explain what it is intended to do?
Sort Columns B & C into the named ranges on sheet 2
The code should read thus
but this approach will be quite slow I imagine. It would be better to collate the data first and then write it out to the relevant ranges. I have to go into a board meeting shortly but will endeavour to post that approach later today.Please Login or Register to view this content.
what I also find troubling is that when I run "ConcatenateABandRemoveAllValueswhichwillerror " to prepare the values it wont transpose the range and gives me a mismatch error but it is identical to the values that it requires.
thank you I really appreciate your help
Not at all- I do this to keep my hand in with Excel so it's all useful to me!
Here is an example of my suggested approach
In truth, it would be better to combine this with the step that names the ranges in the first instance as there is a lot of overlap in the processing the two procedures do.Please Login or Register to view this content.
I think alot of the problem I am having is because I have seperated
ConcatenateABandRemoveAllValueswhichwillerror - Transposerange - CopySheet1A1toSheet2NextCellInColumnA
but I myself wouldnt even know where to begin with trying to merge these. everytime I have one part working the others dont. I am not ashamed to say it is making me a little bit emotional.
Do you require them to be separated for any reason?
not at all just dont have the knowledge to run them all as one.
I doubt I will have the time today but I will try to consolidate them for you tomorrow at some stage.
thats great thank you I really appreciate your help on this
Hey Xlnitwit Did you have any lucky? or should I go back to the drawing board?
I'm afraid I did not have the time yesterday but will see what I can do later this afternoon once I have some meetings out of the way.
thank you once again really appreciate it.....just didnt know if it was a lost cause LOL
Can you confirm which data should be brought across in the last routine? I know you have said columns C and D, but column D is empty in your last example file so I would like to confirm it is the correct one.
My appologies I think I may have got the ranges mixed up
So after the ranges have been set up on sheet 2 the below should be left ready to be sorted in to them. and its only Col B & C thats going into these defined ranges.
COL A COL B COL C
_LionDooE10161 LFRQ1DSI87-0800 w/c 19/09
_AlBarakatGroupCoLtdSaudiE10048 LBMDTRC387+0475075010 w/c 19/09
_DandAContactLensesLimitedDA0002 LOPTN1EI88+0600 w/c 26/09
Thank you so much for the help
That makes more sense. Can you provide a sample workbook as it is at the start and indicate which macros you run and in which order?
So it should go:
1. ConcatenateABandRemoveAllValueswhichwillerror
2. then it should sort the Item Codes and Dates using... CopySheet1A1toSheet2NextCellInColumnA ( but it weirds out for some reason)
Last edited by bloodmilksky; 10-03-2016 at 11:08 AM.
it wont let me upload the file as it is too big
got it with some adjustments
Did you manage to get the file okay?
I had not seen that message until now. I will download it and have a look this morning.
Is that the file as it will be before running the two routines you mention, or before running any of the code in the file?
yes excluding the VLOOKUP i use to get the email addresses.
That was intended as an either/or question. Is that file as it will be before you run any code at all, or as it will be just before you run the two routines we are talking about?
before any code has been run at all
OK, and you just run those two routines, or does any other code need to be run as well? At first glance it would appear more code is needed to put the data where I think it ought to be.
I think everything should be in the first routines. i think :S then it will just be emailing of those ranges
After running the first routine, Sheet1 has the following data:
Column A: Business PartnerID
Column B: email addresses
Column C: blank
Column D: blank
Column E: item code
Column F: Order date.
Is that how it should look? That doesn't seem to match the situation that has been described for the second routine.
sorry i must have missed something because it should only be on D left blank and then E&F should be sorted into the defined ranges
Did you need me to supply you with any further information
If you can confirm exactly what columns should be present, what data is in which column and which columns the macro should use to name the ranges, and which to populate the data in those named ranges, that will allow me to proceed with confidence.
so what should be present is
A B C
Reference - Email Adress - Blank
"A" Should be the name of the range and B & C Should appear next to A on sheet 2.
D E
Item Date
This will be the information that gets sorted into the named ranges.
If C is blank, there doesn't seem a lot of point in processing it?
Good Point. I think the reason for it was so there was a blank cell in each range to help make it easier when users where looking at the named ranges.
This version will create and populate the named ranges in one go which will hopefully improve the speed. It would be faster still if there were not a few thousand rows on Sheet 1 containing just "_".
Please Login or Register to view this content.
will this also sort the other colums into these named ranges
Yes it will.
you are a star thank you
Could you send me the workbook that you have this working on please?
Here is the workbook after running the code. The code is in module 6.
I have tried it and it keeps erroring
What error are you getting and on which line?
can I just confirm what order the Code is meant to run in? sorry Have been in meetings this morning and head is all over the place :S
That file has already had all the code run on it.
For it to work, you would need to replace all the data on Sheet1 with the original data from the example workbook you uploaded and then delete the blank column C or D after running the first routine.
its coming up with a mismatch error.
I am running the first session then on to the code you posted and its erroring.
Which line causes the error and did you delete the extra blank column before running the second bit of code?
nope I got it and it works perfectly thank you so much ^_^
I am just trying to adjust some code now to run through and email off the ranges
You're welcome.
If I wanted to How would I have the code exclude the blank colum?
In the output?
Yeah so it would exclude column C and then still sort E & F into those ranges
I do not have time to test this at the moment but I believe it will do the job
Please Login or Register to view this content.
its missing the item codes completley and sorting the references on a 3rd sheet I have set up (ac_no&email)
What data is in which columns on Sheet1 now?
its email account numbers from the Vlookup sheet i have been using to get the emails
I'm afraid that does not answer my question. I need to know the full data layout on Sheet1 when you run the code and what is happening in the output.
here is sheet that I have been running the code on
The previous code I gave you was designed to work with that layout- i.e. it processes columns D and E, rather than E and F as you just requested.
it works perfectly but the email address's arent showing as active. do you know how I could add that into the code.
sorry for all the questions and thank you
Do you mean you want them to be active hyperlinks?
Yes please
Replace the code in module 10 with the following
Please Login or Register to view this content.
Good Morning Xlnitwit ^_^
I was just wondering if I could pick your brains quickly on this workbook again please/
I will help if I can.
its just the below code I am using to try an email the ranges on sheet 2
it keeps giving me a runtime error but I believe I have defined everything correctly
Please Login or Register to view this content.
What is the error and on which line? Does the data start in row 1- my memory says it was row 2?
I am trying to use the Sub emailranges to fire off all of the emails on sheet 2 but it keeps on coming up with a runtime error. I was just wondering if you could point me in the right direction so i can get it to work.
I have amended the ranges on sheet2 to row 1 but it is having none of it
You must amend the code thus
Rather embarrassingly I have just sent several emails from a colleague's machine! It seems that the MailEnvelope does not allow one to step through the code.Please Login or Register to view this content.
LIKE SO ?
Please Login or Register to view this content.
Yes, exactly.
its now coming up with a invalid reference on the .introduction
I misread your code- you are missing this
after the line that makes the envelope visible.Please Login or Register to view this content.
i must have entered it wrong cause it is still showing a runtime error
Please Login or Register to view this content.
I have asked this several times before- what is the error and on which line? It is extremely difficult to help without that information.Originally Posted by bloodmilksky
so it fails on
"With ActiveSheet.MailEnvelope"
and the error says
Run-Time Error"-2147467259(80004005)2:
Method "mailenvelope"of Object "_Worksheet" Failed
I really can't read today! You haven't made it visible
Please Login or Register to view this content.
Last edited by xlnitwit; 10-07-2016 at 09:27 AM.
Loop without do?
I've edited the code above. And with that I am stopping as I clearly cannot concentrate today!
sorry that message sent early it has come with a Loop without do error. I have tried using a end if to fix it but then it just rolls onto another error.
I am so sorry for this.....I thought this would be very easy and it has just turned into a utter calamate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks