Call in the cavalry -- need help solving this thread-prior to 2013

[teylyn]

Hi all,

We have "The Bad Threads" and some other threads in the Water Cooler where people exchange info about problematic threads. The original intention was to highlight threads where things go wrong, be they communication breakdown, silly attitudes or funny twists in the life of a thread.

Sometimes, though, we may get stuck in a thread and need help from other members to solve a problem, and we hope that someone else will come along, have a look at the situation and provide some fresh insights that can lead to a solution.

The moderators frequently call on each other if they encounter a problem they cannot solve under their own steam.

But for the other members, there is no single port of call to ask for assistance from other members when a thread seems to be stuck.

Therefore, I'd like to start this thread for all members of Excelforum.

If an exchange between the Original Poster (OP) and a member does not lead to a solution, it will more often than not just be abandoned, since a lot of members won't check out threads that already have received a few replies.

How do you call in other members to have a look at a thread and provide a new/fresh/different approach, or just another pair of eyes? So far, we don't have a structured way of doing this. We just hope that someone else will come along and have a look. Most times that does not happen and the OP will be stuck with their problem. They may go elsewhere and not return to Excelforum again.

Enter the "Cavalry thread".

If you find yourself in a situation where you can't provide a working solution for the Original Poster (OP), but you think that a solution could be achieved with the help of some other members here, you can post a reply to this thread and ask other members of Excelforum to go have a look (remember to post a link to the thread ).

I'm sure that "calling in the Cavalry" for help will result in more solved questions, and we will have more happy Excelforum campers, who will return to Excelforum for good and sound advice from a variety of top-notch Excel specialists.

We all spend a lot of time trying to help people conquer their Excel challenges. We all feel good when a question has been answered and the OP acknowledges that. So, call the Cavalry if you're stuck, so as a group we can help the OP find a solution for their question.

How about it?

cheers,

[Marcol]

Great idea teylyn

It's a call I have used a few times in the past, see this thread Posts #24 and #30.
http://www.excelforum.com/excel-gene...unction-2.html

[romperstomper]

A kind of "EF Regulars #1"?

[jwright650]

the "Cavalry thread".


How about it?

cheers,

I like it!....also some threads get bumped way down the "new posts" list if they haven't had much activity so maybe we can ask for help in there to get those threads solved or satisfied in some way.

[NBVC]

So should I transfer my last "Bad threads string of posts here"

[jwright650]

Here's one that could use some Word expertise...
http://www.excelforum.com/2458109-post1.html

[Domski]

I admit I sometimes get to the end of the day, go and have a couple of beers and then head home and forget all about a thread I may have left in limbo. Still think it might be handy to be able to call someone in to look at a thread I know I'm leaving or am struggling with.

Dom

[teylyn]

Someone who can live without commas, please have a look at this. I'm off to bed at almost 1 am.

(The OP put some punctuation marks in after my request, but it's still lacking, I feel)

http://www.excelforum.com/excel-gene...ml#post2460951

[jwright650]

OK, I'm stuck on this one.....anyone understand what the objective is with this workbook? I'd love to help, but have no idea what the end result is supposed to look like.

http://www.excelforum.com/excel-gene...formation.html

[jwright650]

Thanks to royUK for coming to the rescue for me.

[NBVC]

I don't feel too comfortable giving VBA advice so if anyone wants this one..go for it.

http://www.excelforum.com/excel-gene...ous-sheet.html

[Cutter]

Sound the bugle - this one requires more expertise than I can give.

OP is requesting a method to total values associated with an employee that may or may not be listed within a collection of sheets that may increase/decrease.

http://www.excelforum.com/excel-work...ml#post2465872

[Marcol]

Can anyone draw this one to a conclusion
http://www.excelforum.com/excel-gene...at-change.html

I just can't seem to get through to the OP, after a PDF to show steps, a link to basic sorting, 3 or 4workbooks, and the passing of a week, the penny still hasn't dropped.

[Mordred]

This is a great idea. That's all I have for the moment as I have been more or less offline for two weeks.

[MarvinP]

I try to end my unsolved message with.

"Maybe one of the Smart Forum Gurus can figure this out."

I think DO has this in his search engine and helps out frequently.
Maybe he know I leave people without answers???
Maybe he know I'm asleep when he's awake??

[Domski]

I'm off to meetings for most of the rest of the afternoon and seem to be going in circles on this one:

http://www.excelforum.com/excel-prog...ml#post2467706

Dom

[ChemistB]

I'm stumped as to why his spreadsheet is doing this. The combo box is returning the value as text and not a number. He attached an example.

http://www.excelforum.com/excel-gene...t-numeric.html

[NBVC]

His named range "size" in the drop down boxes sheet has cells formatted custom: #" which is number with the quote custom inserted.

If you change to text and actually enter the quotes in those cells, then the initial sheet works.

Edit: I think I misinterpreted...Please ignore

[Andy Pope]

If I use that approach for the 2 comboboxes then the formula in $F$34:$F$63 break down.

[NBVC]

You can always update the references to B26 or C26 in those formulas with Left(B26,2), Left(C26,2) or Substitute(B26,"""","")... but I am sure the methods you and romper provided should work better.

[ChemistB]

I think I just have a mental block on this one. It shouldn't be that difficult for some of you INDEX/LOOKUP geniuses. It's more of a delete similar data than a merge similar data.

http://www.excelforum.com/excel-gene...ilar-data.html
Thanks

[Blake 7]

Hi guys - im really busy at work and this is making my head spin. Could someone please take a look so the poor uy is not hanging on?

Cheers

http://www.excelforum.com/excel-gene...m-one-tab.html

[Blake 7]

Thanks Romper

[ChemistB]

http://www.excelforum.com/excel-gene...ormatting.html

OP wants a VBA solution for conditional formatting as people will be pasting data.

[Blake 7]

Hi Guys - hope that someone can help with this as im as desparate as the poster now to get to the bottom of it!

Col e is desired output, basically, he wants to sum between refils, will make sense when you see the attachment.

Here's the link below.

http://www.excelforum.com/excel-gene...-function.html


Thanks alot

[Cheeky Charlie]

I want to rename this thread 'first to the finish'... nice formula DLL.

[Blake 7]

I want to rename this thread 'first to the finish'... nice formula DLL.

lol , the hare and the tortoise!

I agree, cool formula DLL

[Cheeky Charlie]

Who are you calling tortoise? Cheeky bugger!


(tongue in cheek)

[Huron]

Can anyone help with this one? It works for me but not for the OP.

http://www.excelforum.com/excel-work...-printing.html

[Marcol]

Either to much beer or not enough.. ...

Can any one sober/not so sober find a way into this thread?
http://www.excelforum.com/excel-gene...ing-maybe.html

burp, please excuse me

[Cutter]

Hi Marcol

I think I've given him what he wants. But we'll have to wait to see.

[Marcol]

Cheers Cutter

You seem to understand the "Jargon"

BTW - I hated those "short change-overs". D after A and M after D were killers!!!

What does it all stand for?
And where does "pb11 come into the grander scheme?

[Cutter]

When I was subjected to it D was Day shift 8am to 4pm, A was Afternoon shift 4pm to 12m, E was Evening shift 7pm to 3am and M was Night shift 12m to 8am.

Going from day shift on Sunday to night shift Monday (which was actually Sunday night) or going from afternoon shift Sunday to day shift Monday were the short change-overs.

I have no idea what the other notations are. I assume the G is another day shift with a different start-end time to allow for overlapping personnel.


BTW - why are you still awake - doing an M shift??????

[Marcol]

Hmm...I can follow some of that, but it seems the OP now has a Z shift.(see the latest Post)
That's the point where I get totally lost, where to next?

I'm off for a Zzzzzz!!! shift, good luck

[Cheeky Charlie]

groan

[Cutter]

Hi folks

Could someone wise in the ways of Excel and its inner workings have a look at this.

http://www.excelforum.com/excel-gene...ml#post2488590


It requires an explanation regarding seemingly inconsistent simple arithmetic results.

[Cutter]

@shg

Thanks for responding. It seems the OP has decided to go with Open Office.

[teylyn]

Mac types needed. Weird date presentation that looks OK in the sample workbook attached when I open it on my box.

http://www.excelforum.com/excel-gene...-know-why.html

[jwright650]

HELP! ME...LOL

If one of you guys can figure this out...it will kill two threads with one stone....LOL

[Marcol]

Hmm?

@ jwright650 the OP has a bit of a history with pop-up calendars.

He has been told more than once how to find Tools > Additional Controls and much more, glad I've just migrated to 2007 and don't have 2010...

[jwright650]

Ah, OK....I also see that royUK has posted a link in there for an alternative for him too.
I thought by using code to build a calendar, it would get rid of these quirky situations...

[jwright650]

I ended up using one of royUK's Userform calendars...I'll see if that satisfies the OP or not. If not, I'm all out of ideas. Seems to work fine on my machine and others here in the office<fingers crossed>.

[MarvinP]

Hi John,

I've standardized on the DTPicker from Microsoft. I delivered an Excel app that didn't work on a machine or two. I fixed it by http://danielcurran.com/instructions...ontrol-60-sp4/
This put the .ocx in windows/system32. Then I was able to add it to my controls in the developer tab an use it.
Worked fine after that.

[ChemistB]

http://www.excelforum.com/excel-gene...19-tables.html

Can someone with Excel 2003 help this guy/gal? I went from 2000 to 2007 so am not sure what options he/she has to work with.

[jwright650]

Thanks for the reply Marvin....man, I've tried a bunch of those datepicker type calendar controls now...I have got to settle down to just one that will work anywhere, anytime....LOL

I think that I am having trouble getting royUK's Userform to work on one machine here at work now....this one computer has given me a fit with everything that I have ever tried to use on it.
I can run this stuff on my machine and it runs flawlessly day after day, no matter which date picker I use....drives me crazy when it won't work on other machines, especially after I have spend lots of time building forms and things for people here at work to use.

[NBVC]

http://www.excelforum.com/excel-gene...19-tables.html

Can someone with Excel 2003 help this guy/gal? I went from 2000 to 2007 so am not sure what options he/she has to work with.

I am confused on what he really wants.. He can just play with formatting.. you can change border colours, styles and you can change colours, fonts...

Is that what he wants?.

[ChemistB]

Yeah, I had that problem too NBVC but, yes, I think he's looking for formatting options in 2003. Does "List" give you options like "Table" does in 2007?

[MarvinP]

There used to be a MSCAL activeX control that was no longer supported by MSFT. The rumor was it had some security issues. The "standard" now is the DTPicker and I've used it on my last 3 projects.

Find 2 examples where I've used it. See if you need to find the ocx to get them to work.
One does dates (not very well as I borrowed a userfomr) and the other does time.

[NBVC]

Yeah, I had that problem too NBVC but, yes, I think he's looking for formatting options in 2003. Does "List" give you options like "Table" does in 2007?

the LIST function is not as flexible as the Table is. If you try to apply conditional formatting to colour every other row, for example, the formatting applies only to the formula cells... for some reason it doesn't format the first column (text) until you physically re-apply the formatting...

[jwright650]

Marvin, I gotta run and pick up my kids from school...but I will test these out and get back to you. If it will run on that cursed piece of a machine here at work, I suppose it will run anywhere...LOL...have a good evening guys.

[jwright650]

Marvin, The second file works fine...I had a problem with the first..probably due to opening it in the temp internet folder. I went back to the file that I posted in post#11 of that thread where the OP was trying to get something to work in XL2010...it worked in XL 2003 and XL 2010 for me.

[jwright650]

Figured out the calendar problem......

Every instance of "Date" and "Format" in the code had to be changed to

"VBA.Date" and "VBA.Format"

[romperstomper]

That implies a missing reference.

[jwright650]

RS,
I went into the objects library and found that for Date or Format...I had to add the " VBA. "... before intellisense would pickup and give me some options to choose from.

[romperstomper]

Yep - in my experience, that is always due to a reference problem.

[ChemistB]

http://www.excelforum.com/excel-gene...tatements.html

I am not sure what he/she is talking about.

[grizzly6969]

http://www.excelforum.com/excel-gene...el-2007-a.html

I'm at a loss to help

[snb]

http://www.excelforum.com/excel-gene...el-2007-a.html

I'm at a loss to help

Just provided this suggestion:

Please Login or Register  to view this content.

[teylyn]

snb, thanks for stepping in. I'm sure the OP and grizzly6969 appreciate it. It's not necessary to post the actual suggestion in this thread, though.

[grizzly6969]

thanks snb I hope that was what he/she was looking for -- but whether or not it helped I like it Thanks

[jwright650]

Got one here that I'm not sure what to do with....

[jwright650]

Romper,
thanks for helping me out in that thread.

[TMS]

Can anyone offer any thoughts on this:

http://www.excelforum.com/excel-gene...-new-page.html

If I view the doccument on my laptop with 2007 it's OK; if I view it, also on my laptop, with 2003 there's a blank page.

It looks to me like 2003 doesn't like the watermark but the OP is using 2007 and it's a 2007 format extension.

Thanks

[jwright650]

TMS,
The file opens for me in XL2003 and the watermark(rectangle shapes) are not visible, but when I look at the print preview they are there(I didn't try to print it).

The file opens for me in XL2010 also but the watermarks are visible in the regular Excel view and in print preview.



Edit: added print to pdf from XL 2003
Edit#2:added print to pdf from XL 2010

[TMS]

This gets even more strange ... your 2003 print has only one page, complete with watermark yet the 2010 print has two pages, the second having only the watermark.

I'm beginning to think this has something to do with printer drivers. My default printer is an HP 4100 Series Mono Laser Printer. But I don't know if that has any bearing on what the .pdf file looks like.

Interesting how much bigger the 2010 based pdf file is.

Thanks for your efforts.

[teylyn]

Hi, I'm a bit pressed for time. Work is getting in the way, dang. Can someone take a look at this: http://www.excelforum.com/excel-char...ting-cell.html
Cell is updated via web query every minute. Needs a macro to append each value change to a table, so the history of the value can be charted.

thanks

[Marcol]

This one has me beaten.

http://www.excelforum.com/excel-prog...-in-cells.html

I worked in the window/door manufacturing for some time and thought the sheets would be easy to follow, but it seems to me that the language is a Croatian/Bosnian dialect or Tech-Speak, I did my best to translate through Google translate, but there are to many terms it cannot handle and I can't guess at.

The sheets and the question asked don't seem to match.

Anybody able to understand the language might be able to help. Zbor?

[Marcol]

Could someone with a mac and 2008 try downloading the file in this thread Post #8

http://www.excelforum.com/excel-prog...down-menu.html

The OP thinks it has slowed drastically, but it seems fine to me and others.

If you could just check the drop-downs in column AA orange cells for speed and post your findings in the thread.

Cheers
Alistair

[Marcol]

Re: Post #68 & "Autofill data by Dropdown menu"
Thanks for the help out romperstomper

[romperstomper]

No worries - we don't have many Mac users that I know of, so I thought I'd take a look.

[Marcol]

Re: Post #67 & "sum of all "L" in cells "
Well done and thanks NBVC I got in a real mess with that one, and I thought I knew the business!

[NBVC]

You are welcome... I was getting close to calling it quits too....

When there is a language barrier I try to focus on pictures and try to make sense of it....

[Marcol]

@ NBVC
Aye, I would have approached the problem from another angle.
Use a CAD system to generate the parameters and graphics, say AutoCAD, and then feed to Excel, to ask the questions, from there it's a dawdle.
I can't believe that someone in that industry doesn't use CAD first.

[sweep]

Hi Folks,

I got someway through this thread but can't get the dynamic range to work. Could do with a little help?

[teylyn]

On to it....

[jwright650]

I think I found the problem, the UserForms are not being exported out of the existing Workbook and then imported into the newly created workbook.....but I don't know enough to help with THIS ONE

[sweep]

@ Telyn - Thank you!

[TMS]

@jwright650: not something I do but I'd agree with the thought process.

The other question that springs to mind is, is programmatic access to the VBA project trusted?

Regards

[jwright650]

The other question that springs to mind is, is programmatic access to the VBA project trusted?

Regards

@TMShucks: yup, another good question.

I was searching online for some help on this and found that J-Walk has a page about creating UserForms programmically.
http://j-walk.com/ss/excel/tips/tip76.htm

So...I think what the OP is trying to do is possible...I'm just not sure of the steps or syntax to get it done.

[jwright650]

@TMShucks: The OP is declaring these Userforms (objects) as strings...should they be declared as objects?

EDIT: the intellisense asks for the new file name to be passed as a string, so I guess that the OP has it right.

[jwright650]

is programmatic access to the VBA project trusted?

@TMShucks:
I did a little digging in the "locals" window while I stepped through that module in the OP's workbook.....the answer is NO, VBA project is not trusted.

[TMS]

@jwright650: I assume this is something you have to set somehow/somewhere on each machine. It stopped the code on my machine. I think it may have saved the forms but I couldn't work out where ... maybe in the temporary folder. Cheers

[NBVC]

If anybody is feeling up to it... this OP needs some VBA assistance...

http://www.excelforum.com/excel-prog...f-vlookup.html

[alansidman]

Hey guys and girls;
I've hit a stumbling block trying to help the OP on this thread. If you have a few moments, please read it through and offer up any suggestions.

http://www.excelforum.com/excel-work...formation.html

Alan

[TMS]

Hi, can anyone help with this one. I am completely not getting it!

http://www.excelforum.com/excel-prog...-workbook.html

Thanks for any help you can offer.

[ChemistB]

Trying to extract email addresses from gibberious. I think this will require VBA.

http://www.excelforum.com/excel-gene...addresses.html

[TMS]

@ChemistB

OK, so far, I have these two extract formulae.

Domain name:

=MID(A1,FIND("@",A1),FIND(MID(A1,FIND(".",A1,FIND("@",A1)+1)+1,3),A1)-FIND("@",A1)+3)

and the data between the two "@" signs:

=MID(A1,FIND("@",A1),FIND("@",A1,FIND("@",A1)+1)-FIND("@",A1))


The end of the second extract formula has the user name ... but I have yet to work out how to pick that out.

Anyway, something to be going on with. I have suggested the OP tests it on live data and see if it works consistently.


Regards

[TMS]

@ChemistB

This will get the name with some arbitrary junk on the beginning:

=CLEAN(SUBSTITUTE(MID(A1,FIND("@",A1),FIND("@",A1,FIND("@",A1)+1)-FIND("@",A1)),MID(A1,FIND("@",A1),FIND(MID(A1,FIND(".",A1,FIND("@",A1)+1)+1,3),A1)-FIND("@",A1)+3),""))

Still trying to determine if I can get just the name.

Regards

[jwright650]

^^^ TMS, that's a tough one. I think the OP should rethink the import method...LOL

[TMS]

@John: I did ask the OP where the data comes from, how it's exported and how it's imported. So far, no comeback. The format seems inconsistent too with some cells being much longer than others.

I was going for the intellectual challenge but now I'm getting bored. :-)

[ChemistB]

With the number and variety of extraneous characters, I can't see cleaning it up. You're doing very well though. LOL

[TMS]

@ChemistB: Close as I can get ...

... without code ... and even then I'm not sure what to do.

Please Login or Register  to view this content.

This will convert:

The problem remains of how to get rid of the dross before the email address. As there's no consistency in content or length, it's going to be very difficult. You can't just exclude non-alpha-numeric characters as because some of the leading rubbish *are* alpha-numeric characters.

Any thoughts?

Regards

[tigeravatar]

This is for thread Removing junk from cells and retaining email addresses:

I was unable to use a formula to get a good cleanup, so I went to VBA. With a macro and a function, I was able to clean it up, but I'm not 100% sure that it will work for EVERY case (it worked for all of them in the example workbook, but if periods or "@" symbols appeared in specific places, it could case the macro to fail).

Notes about the macro:
-The macro calls a function I created titled IsLetter. I couldn't find a built-in equivalent to IsNumeric for checking if a character is a-z, so I created a function to do so. If somebody knows the equivalent built-in function, just post it so that the macro can be updated to be more efficient.
-The macro searches for the "@" symbol and then searches to the right for the first non-period character. It puts that string (including the @) as the Domain
-It then searches to the left of the "@" symbol for the first non-period character. It puts that string (excluding the @) as the Email
-The then outputs Email & Domain to give the cleaned up email address
-It does this for each item in Column A

Here's the macro and function:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[jwright650]

http://www.excelforum.com/excel-prog...tch-error.html

HELP!.....Not sure how to use the SUMPRODUCT() in VBA in this situation.....maybe one of you can help the OP out here, then explain to me what you did...LOL

[jwright650]

Thanks for sending the help in that thread. Was guessing and not getting anywhere for the OP.

[martindwilson]

http://www.excelforum.com/excel-gene...in-a-cell.html
op needs help with some code, i made a suggestion but now i'm drowning!

[Mordred]

Hi all,

I have no idea how to solve this person's problem but he/she may have been skipped over from you gurus because I told him/her to use code tags. The Link

[ChemistB]

This one's going to take an array function and I can't wrap my head around it. Also try to talk him/her into using multiple cells.

http://www.excelforum.com/excel-gene...le-values.html

[ChemistB]

Having a foggy day.

http://www.excelforum.com/excel-gene...-w-istext.html

[TMS]

Could someone have a look at this thread, please?

http://www.excelforum.com/excel-gene...statement.html

I'm sure the required formulae shouldn't be that difficult, I'm just having trouble understanding the "rules"

Thanks

[martindwilson]

http://www.excelforum.com/excel-gene...-on-a-mac.html
any one use a mac? and can help here

[Domski]

Anyone with 2007 able to help this guy:

http://www.excelforum.com/excel-2007...ml#post2520917

Dom

[Marcol]

Can someone see why the OPs' file in Post #14
http://www.excelforum.com/excel-gene...rmat-date.html
is returning a #VALUE! error?

See my reply in Post #17.

This one has me completely beaten.

Slainte
Alistair

[Marcol]

Thanks romperstomper & teylyn for responding to my plea.

I must be more aware of regional settings in future, us Scots can be a bit insular at times!

[romperstomper]

Can anyone make any sense of this? I'm giving up since the OP ignored my request for a coherent explanation (at least I think he did).

[NBVC]

If anyone has input here... by all means...

[TMS]

Can anyone help with this ... just gone way out of my depth ;-)

http://www.excelforum.com/excel-prog...reference.html

Thanks

[TMS]

@tigeravatar, romperstomper, snb: thanks for picking this up. Cheers

@romperstomper: yours was the solution I had in mind but didn't have the confidence to offer

@snb: could you explain (in the thread) how your approach works and if there are any dependencies.

Thanks again

[broro183]

howdy everyone,

I don't like leaving threads dangling, but I'm tempted at the moment, so if anyone wants to have a go at this one (http://www.excelforum.com/excel-gene...no-change.html) then go for it!

It may just need a set of new eyes & different wording. I could certainly have used a different wording in the thread...!

Thanks
Rob

[JBeaucaire]

I ran out of ideas on this thread, if anyone can offer some more insight:

http://www.excelforum.com/excel-prog...ml#post2523072

[NBVC]

Does anyone have any idea what is happening here:

http://www.excelforum.com/excel-2007...functions.html

The OP says he/she can't create formulas that use ranges when dealing with 2 workbooks.... I don't think I have ever seen that before....

[ChemistB]

Looking for a VBA solution. Should be simple but I've never worked with a case where the target trigger is a Clearcontents. Thanks

http://www.excelforum.com/excel-gene...ive-count.html

[Colin Legg]

Looking for a VBA solution. Should be simple but I've never worked with a case where the target trigger is a Clearcontents. Thanks

Clearing the contents will raise the worksheet's Change event - as will entering a number into a cell - so you can use the Worksheet_Change() event handler. Target will hold a reference to the range that was changed so, in this case, you want to check if Target holds a reference to any of the cells in A1:A10. Then, if you increment the value in A11 within the event handler, be sure to temporarily set Application.EnableEvents to False before doing so, otherwise the event handler will call itself.

I'll let you have a pop at it first.

[broro183]

re post # 109,
RS & Snb, thanks for popping in to http://www.excelforum.com/excel-gene...no-change.html :-)
It's now just a case of waiting to see if the OP is happy - fingers crossed!

Edit: Thanks to the Speedy Mod (Teylyn?) for moving post 109 into this thread :-)

Rob

[day92]

howdy everyone,

I don't like leaving threads dangling, but I'm tempted at the moment, so if anyone wants to have a go at this one (http://www.excelforum.com/excel-gene...no-change.html) then go for it!

It may just need a set of new eyes & different wording. I could certainly have used a different wording in the thread...!

Thanks
Rob

I stopped looking at that one when the BOLD appeared.

[day92]

Can someone help with this one? Ive been trying to define a name range "mysheets" to include all worksheets but cant seem to get it to work.

I moved all the last names to the full list sheet and then tried to do a vlookup.

http://www.excelforum.com/excel-gene...olidation.html

Here is what I have so far.

[NBVC]

Your thread has a reply, day92

[tigeravatar]

day92,

The only way I could get a vlookup formula to work was by chaining iferrors:

Please Login or Register  to view this content.

I couldn't get it to work with a named range either So I used a macro and mapped it to a button instead.

~tigeravatar

[day92]

Tigeravatar - thanks for your assistance on that one, the vlookup works but the Macro is really slick. I would have never thought of that. I hope it resolves the OP issue.

NBVC - thanks for moving the thread over and I apologize for the duplicate post.

[alansidman]

I am not even sure the ask on this is possible with Excel without some very complex VBA. If you are up to the challenge, take a look at this request as it is way beyond my capabilities.

http://www.excelforum.com/excel-gene...rom-table.html

Thanks and have a great weekend.
Alan

[day92]

Alan, I took a stab at it. I think this is what he is looking for.

[alansidman]

We'll see. Thanks.

Alan

[ChemistB]

Working with Time, not my strong point.

http://www.excelforum.com/excel-gene...psed-time.html

[TMS]

Can anyone have a look at this thread, please:

http://www.excelforum.com/excel-misc...port-page.html

I understand (now) what is required ... I just can't get my head round what would need to be done to make it happen.

Please can you cast yor eyes over it.

Regards

[ChemistB]

I think my VBA works but OP says it's not.

http://www.excelforum.com/excel-gene...fic-value.html

[Colin Legg]

Hi Chemist,

When you delete rows one at a time, you have to start at the bottom and work upwords.

When a row is deleted, the rows underneath are shifted upwards. This means that, if you start at the top and work downwards, some rows can get missed in your loop. Starting at the bottom and working upwards negates this shift effect.

Deleting rows one at a time can be quite slow, because when each row is deleted the calculation tree has to be rebuilt. Setting calcs to manual won't make any difference to this, so it might be preferrable to delete them all in one go. This approach also works around the shifting rows problem mentioned earlier.

[ChemistB]

I thought I took that into consideration because I reset "i" everytime I deleted a row. I don't know how I would delete them all at one go. :-/ So much to learn. So much to learn.

[TMS]

@ChemistB: so, you've created a test workbook, populated it with some data, developed and tested some code ... and it doesn't do what the OP expects it to? Why am I not surprised? ;-)

And still the OP has not shared with you what it needs to operate on??

[TMS]

@ChemistB: with the "for each cell in ..." construct, the cells will be deleted and moved up as they are processed from the top left cell. By the time you get down to some of the lower rows/cells, the values you are looking for will have been moved up.

I set up some test data, cells A1 to S26, mainly filled with 5s but 20 0s and 14 1s. Your code told me it deleted 52 cells but there were still some 0s and 1s left ;-)

[Cutter]

Anyone care to have a look at this thread:

http://www.excelforum.com/excel-new-...s-of-data.html

It is a file to compare Greek electricity rates and provide potential customers with best package available from various providers based on customers' needs. Although it is mostly in Greek there are some English translations but I'm not seeing what is being asked.

[martindwilson]

http://www.excelforum.com/excel-gene...ique-rows.html
i'm thick i think can any one see what op wants here?
and @cutter...grief!!!

[ChemistB]

Okay, I see where this person is going with this and I am not sure if it is possible.

http://www.excelforum.com/excel-gene...t-average.html

[Cutter]

Could someone with VBA expertise have a look at this thread

http://www.excelforum.com/excel-gene...wo-sheets.html

[Dave H9]

Can anyone help the OP on this one -

http://www.excelforum.com/excel-gene...ml#post2531589

I've heard of goal posts moving but this one moved the ball park

Thanks

Dave

[abousetta]

Hi,

Can anyone help brainstorming a solution to this problem? Its regading multiple users of a shared workbook. I thrown everything I can think of at it (including the kitchen sink) but to no avail.

Thanks.

abousetta

[broro183]

hi Abousetta,

I reckon that may be the actions should be reversed...

Rather than "throwing everything at it (including the kitchen sink)", perhaps you should try "throwing it at everything including the bin under the kitchen sink"!

(I'm not sure about others but I've had some corruption problems with excel's shared workbooks & it has made me a little negative about the functionality.)

Rob

[Mordred]

Hi all that venture here,

I started with this person but I cannot finish because I don't know how to run code when 'OK" is selected from a message box. See here.

[tigeravatar]

Can somebody with experience converting to PDF files take a look at this thread please?

I uploaded a sample workbook that has a working filterloop, but I don't really work with PDF files so I was unable to get the save as PDF part working.

~tigeravatar

[zbor]

Finally I got into the system, but can someone now write a macro for: http://www.excelforum.com/excel-general/776934-vb-code-or-excel-formula-help-for-unique-rows-3.html



Start from the last post, that's where I try to explain the process and formulas in the sheet are not base for solution, just for explanation...

Thx

[zbor]

Noone? (10)

[abousetta]

Thanks Rob. I am about to try and use Shared Workbooks in my everday work before I came across this thread and your post. It has made me think a million times (or more realistically forget about this) especially since all our computers already have Access installed on them. Now I just have to find enough time to learn how to work with Acess and I should be ready to go.

Thanks for intervening on this one.

abousetta

[Marcol]

Hi zbor

Please Login or Register  to view this content.

Seems to fit the bill for the first column of results (165 rows/results), but I'm struggling to get my head round the rules in the second column.

I'm off for a beer or two.......
It might just clear my head.

I'll look at it again if no one else beats me to it, using VBa if needs be...

[zbor]

Hi Marcol,

I'm affraid that approach might be wrong too... It is now 165 and that's correct but in some cases it might not work (I guess when both B or C columns are already used all of their unique values).

So VBA definitely needed:

Take A1. Is it unique? If not go to A2.
If yes, take B1. Is it unique? If not go to A2.
If yes, take C1. Is it unique? If not go to A2.
If yes print result in E1, F1, G1 (or somewhere).
Go to A2.

Do until A<>""

[teylyn]

I wouldn't have a clue how to even get started on this one ... http://www.excelforum.com/excel-prog...-of-excel.html

Who's up to it?

[TMS]

Please can someone take a look at this thread:

Printing array in he worksheet from VBA - does not work

It appears to be a specific issue relating to, what seems to me, a generic problem of UDFs returning arrays of values.

Thanks

[ChemistB]

http://www.excelforum.com/excel-gene...-function.html

Thanks

[jeffreybrown]

Could I get a hand here...

http://www.excelforum.com/excel-prog...using-vba.html

...my limited VBA knowledge has been trumped...

Don't really understand why the second If statement (I removed it and it seemed to run fine) and also for some strange reason G8 seems to cause problems as it is replaced by a value I can't find...

[alansidman]

OP needs some help with Pivot Table Calculation. I don't have a clue on this one. Do you?

http://www.excelforum.com/excel-2007...ml#post2538469

Thanks
Alan

[MarvinP]

Hey Alan,

I think I got this one.

In Pivot Tables you can pull the same column name to the summation box many times. Then you can show the values as different formulas based on all kinds of different stuff.
This is close to what I did http://excel-diva.blogspot.com/2010/...fferences.html
Here is the list in 2010 and it is very easy to do.
http://office.microsoft.com/en-gb/ex...101821011.aspx

[alansidman]

@MarvinP
Thanks for jumping in.
Alan

[NBVC]

Here is one for anyone so inclined.......

http://www.excelforum.com/excel-work...reference.html

[NBVC]

One day I will attack these.. but this one involves Pivot Table and VBA and Greek... deadly combo....

http://www.excelforum.com/excel-gene...selection.html

[Mordred]

Hi all, the code I have provide to this Link works for me but not the OP. I think it must be compatability issues between my version (2003) and the OPs version (2010). At any rate, I don't know what else to do with this.

[broro183]

hi everyone,

I'm off to bed now, but if anyone wants to help out in this thread (http://www.excelforum.com/excel-gene...ml#post2541028) we can hopefully get the OP sorted quicker. I think I may have originally seen it here in the Cavalry thread but I'm not sure.

Thanks
Rob

[daddylonglegs]

Poster here wants VBA to calculate working days between two dates/times (excluding weekends). I've suggested a formula that should give the correct result......

[TMS]

@DLL: I've created a function but the output is not quite right.

Please have a look at the thread and see if you can spot the (deliberate ;-) ) mistake (I wish).

[jeffreybrown]

Could I get a little help here.

http://www.excelforum.com/excel-prog...e-actions.html

I wrote a macro to create a summary sheet of all due dates by name, but what I can't seem to get is how to find a date within a span. The date to search for is 7 days on both sides of today's date.

If I change all of the hightlighted dates to 6/12/2011 the macro runs fine, but again I need to look at a between range date.

I'm not quite finding/seeing how to look at the range.

Any thoughts and thank you very much?

[TMS]

@Jeff: VBA code provided. No error checking so it might need tweaking but the basic code is there. See thread. TMS

[jeffreybrown]

@TMS: Thank you very much...this seems to be the right answer I hope the OP agree's.

[jeffreybrown]

Anybody have an idea how to fix up this chart? Seems I can struggle through some charts, but evidently not this one.

http://www.excelforum.com/excel-char...ock-chart.html

[teylyn]

Jeff,

AFAIC, no can do. Posted comment.

[snb]

Feedback to the cavalry:

I gave some suggestions according to this urge

[Mordred]

Hi all,
I've been working on a pagesetup.printarea for an OP and figured it out but on his/her last post he/she changed the criteria to print ranges from two sheets instead of one. I've tried different ways to set the print area for printing and while I can set the print area, the print preview only shows on sheet's worth of data. The Link I think this might work if there was a "Print Sheet" with the appropriate data loaded into it based on his/her criteria but if the two ranges from two different sheets can be printed as one then I figured you gurus would know about it.



Edit: I think I got this one afterall.

[Mordred]

Hi again, I need someone to look at this. I solved his/her problem, at least his requirements work for me on both 2003 and 2010. Either that or I am totally missing what he/she wants done. What ever it is, I am spent on this one.

[Whizbang]

Would someone mind taking a look at this one:
http://www.excelforum.com/excel-prog...ary-macro.html

I helped the OP with the original issue, which was to improve an existing macro. However, the macro is merely to clean up a copy/paste from a webpage, and the OP would like to replace the existing macro with one that draws the data directly from the webpage, if possible. Webquery doesn't seem to work because the webpage is in javascript. I know there is a way to query the web tables without Webquery using VBA, but I don't know how.

The OP mentions MS Forms 2.0 Library, but I really don't know what it is, who suggested it to him or why.

[romperstomper]

The Forms library is the one that contains userforms and forms controls as well as the DataObject which allows (limited) access to the clipboard. I suspect it's the latter that made it relevant to the OP.

[zbor]

Neead a macro here: http://www.excelforum.com/excel-2007...ext-field.html

that will strip out biggest number from mixed text (I belive that will solve thing out):

example: 530007 from

M.Venkateswarlu,Do.No:-37-7-26 ,Satya Nagar ,Opp 5Th Town Police Station,Visakhapatnam-530007....

(but number is not always at the end)

[daddylonglegs]

I'm not sure that taking the largest number will work because the OP says

"if customers send address with error zip of 7 digit then it should not show any result"

[Mordred]

Hi all, can you please check this thread out. I fulfilled the first part of the OPs requirements but cannot figure out the additional requirements as posted in post #5 and 6. Thank you.

[Mordred]

Hi all of you that are way smarter than me. I've tried with this but I am running a different version than what his user profile states (me 2003, him 2007) and cannot open an .xlsm from work. Find it here. Thanks.

[romperstomper]

I've posted a small tweak to your code.

[Domski]

Anyone able to have a look at this?

http://www.excelforum.com/excel-prog...tain-cell.html

I've finished work and am out this evening.

Dom

[TMS]

can someone offer suggestions on this: protect only two columns


Thanks

[Paul]

TM.. I added a little info for the user, and provided a basic macro to build from if needed.

[Mordred]

Hi all,

I've been having a hard time with the following code not producing duplicate entries:

Please Login or Register  to view this content.

I feel a little over my head with regards to having the code know if there has been a new entry made by the OP and thus copy the data. As it is, the code will update the whole range - aRng everytime the code is run. You can find the thread here. Thanks so much!

EDIT: I have left out what I have tried so far to not allow duplicates but it doesn't work so why bother bombarding you all with useless code.

[Mordred]

Alrighty I am at another stumbling block with another person locate here I started this but now I cannot wrap my head around the logic side (mayhap I will be able to later but for now I am spent). Please help if you can.

Thanks!

[jeffreybrown]

Well this one has me stumped.

[TMS]

Edit: OK, you can stand down on this one. Seems I should have had a little more confidence as the OP is happy with the solution. Thanks, TMS


Can someone have a look at this for me please?

Extract Data from a URL stored in a Cell

I've put together a routine to get the source code and extract the header.

However, I'm not 100% confident as the output varies. I'm guessing it's a timing problem and I've tried a few things to overcome it, the last being to navigate to the page twice.

I did try creating a new instance of IE and killing it for each cycle, but that didn't work ... though that's not to say it should be ruled out, it just didn't work with the version of code at the time.

You'll see the various attempts.

I'm probably missing something very basic but I just don't know what it is ;-(

Thanks, TMS

[Mordred]

Hi all, I have started to help someone to fix a recorded macro but am having troubles with using

Please Login or Register  to view this content.

. If someone could look at this I would appreciate it as it is now 2:22 am for me and I can no longer think

Thanks

[davegugg]

I can't replicate this guy's error, can anyone else?

[martindwilson]

http://www.excelforum.com/excel-gene...on-sheets.html
needs a bit of code done!

[Mordred]

Hi, can someone help this person and his/her thread out? Maybe my thinking cap is crooked today because I am not fully understanding him/her.

[Huron]

Please could someone help out with this one? It's beyond my limited coding skills.

http://www.excelforum.com/excel-prog...lp-needed.html

[zbor]

Macro guru wanted... Married or alive...

http://www.excelforum.com/excel-prog...o-columns.html

[tigeravatar]

Married or alive...

Hehe, I love that married is as good as dead

[Marcol]

@ Huron
Seem your OP is happy!
http://www.excelforum.com/excel-prog...lp-needed.html

[Mordred]

Hi all, can someone look at this? I have looked at the workbook and tried to understand the criteria but I just can't wrap my head around this. I don't even know what questions to ask.

[davegugg]

Hi all, can someone look at this? I have looked at the workbook and tried to understand the criteria but I just can't wrap my head around this. I don't even know what questions to ask.

I find it hard to get motivated to help someone when they are basically asking me to do the work for them. I looked at that thread but moved on quickly without even looking at the attachment because there was no question asked, just "If you could finish my work for me that'd be great." You're gutsy to take that on Mordred

[Domski]

I had a quick look and ran away too!

Dom

[Mordred]

I find it hard to get motivated to help someone when they are basically asking me to do the work for them. I looked at that thread but moved on quickly without even looking at the attachment because there was no question asked, just "If you could finish my work for me that'd be great." You're gutsy to take that on Mordred

I tried because this person has been PM'ing me a fair bit to do so but I really haven't the slightest clue what his/her requirements are. I don't mind doing some work for people but I need actual factuals to work with otherwise my little brain just doesn't comprehend!

[Mordred]

I had a quick look and ran away too!

Dom

That's funny Domski. I'm thinking I might run and never look back!

[Domski]

I'm quite tempted to change my settings to Receive Private Messages only from Contacts and Moderators but haven't done it yet.

Dom

[Mordred]

I've never delved that deep into my settings to even know that I could do that!

[martindwilson]

hi all could someone help this chap?
http://www.excelforum.com/excel-gene...ed-ranges.html
i believe vba is the way to go as data validation only likes a contiguous range

[Marcol]

Hi Martin
Re: Post #194
I've offered a non-VBa workabout, maybe it will help.

[martindwilson]

cheers mate

[Mordred]

Just in case no one looks in on this, I have no real idea what this person's code is doing so therefore I am passing on this. Please help him/her and if you are so inclined, please either give a brief description of what the function is doing or PM me as I am curious. Thanks a lot!

[tigeravatar]

All,

Here's the thread: http://www.excelforum.com/excel-prog...ion-macro.html

I'm not quite sure where the disconnect is for this user. On all my tests, the code works as intended, but the user states that when he tries to run it, simply nothing happens. Any help would be appreciated.

~tigeravatar

[ChemistB]

Tiger,
looks like you persevered and found out that the OP wasn't putting the code in the right module. I'm thinking you solved it.

[tigeravatar]

ChemistB,

I actually didn't think of that issue. Credit goes to TMShucks for that