How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

Auto select,copy & place images and data in excel

Resolved Question:

Hi John You recently provided a solution to my Excel question. I now have another problem I need a solution for.


 


I have prepared an example SS, the smallest size I can get it down to is 2Mb. I need to upload the file to you however the paperclip icon is not visible?


 


Regards


 


Rob

Submitted: 2 years ago.
Category: Microsoft Office
Expert:  Rachel-Mod replied 2 years ago.

Hi, I am a Moderator for this topic. I sent your requested Professional a message to follow up with you here, when they are back online. If I can help further, please let me know. Thank you for your continued patience.

Customer: replied 2 years ago.

Hi,


 


I am waiting for JohnD to respond, however I know he is 6 hours behind GMT.


 


In the meantime, I need to upload my spreadsheet however your browser dialog box doesn;t have the 'attachment' paperclip? I have tried with both Firefox and I.E.? How can I attach my file?


 


Regards

Expert:  John D replied 2 years ago.

Hi Rob

 

To upload the file go to http://www.wikisend.com/ and upload it there using the Browse button (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.

 

If the file has sensitive information let me know before you upload it

 

 

 

Customer: replied 2 years ago.

Hi John Here is link;


 


http://wikisend.com/download/441626/PaintingsSample070912.xlsm


 

Expert:  John D replied 2 years ago.

Ok I looked at the file and I don't understand what needs to be done. Anyway this looks like a pretty complex issue which requires a lot of time to understand and set up and I don't think I can do it for $11

 

 

 

 

 

 

Customer: replied 2 years ago.

Hi John


I don't know how the 'monthly' fee thing works. I have signed up to a £56 fee after 7 days? What shall I do?

Expert:  John D replied 2 years ago.

The monthly subscription is purely between the Customer and JustAnswer, probably regarding only the initial deposit but not payment for the answers. It has nothing to do with the question value, with paying for answers, or with what experts get paid. You still have to assign a value to each question depending on what you think the expert would accept to take the question for. Separate payment must be made by rating the answer for every question answered.

 

 

Customer: replied 2 years ago.

Ok, I need to cancel the monthly fee and do what we did last time? On the last job in August we agreed £36 and then I added a bonus?

Expert:  John D replied 2 years ago.

Sounds good. Let me know when you have made these adjustments

 

 

Customer: replied 2 years ago.

So, shall i start a new question and abort this one?

Expert:  John D replied 2 years ago.

You can, or we can stay on this page and add bonus amount later to bring up the amount to the target figure

 

Customer: replied 2 years ago.

Hi, OK that's fine with me. I chose you because of the last job you did for me.


 


I have created the sample SS by taking my actual one and stripping out everything to leave a basic functional sample covering the areas concerning my question. The comments in the WS pages explain how I currently use the SS and what I want to achieve.


 


If you've any questions please just ask.

Expert:  John D replied 2 years ago.

I took a look at the sheet and I reckon that I need to spend a great deal of time just reading and absorbing all that detailed instructions before I even can determine if I can help you with this, and of course all that time would be wasted if I can't understand or find a solution. Already spent about an hour on this thread gratis.You may need to hire a local programmer on time basis for this

 

If you need help with a specific and clear task let me know.

 

 

 

 

Customer: replied 2 years ago.

Hi John


 


I think you are missing something.


 


OK I will revise my explanation and re-upload it the file. I've literally spent hours preparing this so you would understand what I want, I suspect I have included too much information that isn;t needed for the question. Sorry :-)


 

Expert:  John D replied 2 years ago.

You may need to hire a local programmer on time basis for this

 

I am sorry I cannot spend more time on this without some kind of compensation for the initial time to review and understand, as well as clear amount later if and when the task gets completed

 

Will opt out if that is not agreeable to you

 

Thanks

 

 

 

 

 

 

 

 

 

Customer: replied 2 years ago.

Hi Please just let me re-word my question first? I am not changing the SS at all, just the way I put the question to you?

Expert:  John D replied 2 years ago.

Ok

 

Customer: replied 2 years ago.

John.


 


Please see amended question on


http://wikisend.com/download/452212/PaintingsSample080912.xlsm


 


Thanks Rob

Expert:  John D replied 2 years ago.

Ok will review and get back to you

 

Expert:  John D replied 2 years ago.

No it's still not 100% clear. Need some clarification. As I said at this point if you want me to continue you need to make some payment for my time so far

 

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.

Hi John I made an interim payment...

Expert:  John D replied 2 years ago.

Thanks

 

Ok let me recap what I understand so far

 

- User selects the three criteria in the 3 drop down boxes in row on Check sheet

- User clicks a button to run the macro

- Macro goes to the Detail List sheet

- Macro looks for a match of the three criteria (i.e, Location, Type, and Size)

- When found, macro copies that block of data

- It then pastes it in the first available block on Check (looking for the available space by rows first then columns)

 

Correct?

 

 

Expert:  John D replied 2 years ago.

Did you not get my last message. I am waiting for your answer so I can start with the code

 

 

Expert:  John D replied 2 years ago.

I think there is a major issue with the site today. I am not seeing any replies from you (if any)

 

 

 

 

 

 

 

Customer: replied 2 years ago.

Hi John


 


I was online until 8.00pm yesterday so it must be time zones.


 


- User selects the three criteria in the 3 drop down boxes in row on Check sheet = Yes. Any combination.


- User clicks a button to run the macro = Yes. Button on the 'Check' sheet please.


- Macro goes to the Detail List sheet =Yes


- Macro looks for a match of the three criteria (i.e, Location, Type, and Size)=Yes


- When found, macro copies that block of data=Yes. Must include the image in that block.


- It then pastes it in the first available block on Check (looking for the available space by rows first then columns)=Yes - I would also need a 'clear all' button on the 'Check' sheet which when pressed clears the 'Check' sheet. This will enable me to get a new set of results, OR to append results on the end of the previous search.


Also, on my working 'Check' sheet I will ensure to have ample blocks to cope with the macro result.



Note - the cell range in my working 'Detail List' currently reaches AB2340 and this is expanding so if you tell me which line to tweak in the final code to extend the range I will be able to do that.


 

Expert:  John D replied 2 years ago.

Ok thanks, XXXXX XXXXX on it this morning and get back to you (I am in the US pacific time zone)

 

 

Expert:  John D replied 2 years ago.

Ok here you go

 

http://www.filesharesite.com/files/201209/1347210811PaintingsSample080912a.xlsm

 

Two macros, two buttons

 

Hope this is Ok. Let me know if you have any questions, and thank you for applying the balance payment if you're happy with the work

 

 

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.

Hi John


 


It's not quite yet working for me, but you're getting there... I have some snags;


 


The 'clearbuttons' pops up message 'all paintings buttons have been removed' but nothing else seems to happen? I wanted it to clear the data and images from the 'Check' sheet.


 


I then manually cleared the 'Check' sheet and tried the 'GetPainting'.


 


On testing it only returns the 1st matched result into the 'check' sheet each time, even when there are several data blocks in 'Detail List' that match the criteria.


 


 


Also, when I selected the empty field (blank) in 'Type' & 'Size' it wouldn't run.


 


I realise I need to be able to choose to ignore the 'Type' & 'Size' criteria. Maybe put 2 tickboxes (or something similar) so I can turn off the 'Type' and/or 'Size' criteria and they be ignored in the search if 'deselected'?


 


i.e. If I select 'STORAGE' in 1st dropdown, but leave 2nd & 3rd dropdown blank (or deselected?) then it should return 6 sets of data [because 6 paintings match 'STORAGE'].


 


I've done an interim payment. Thanks!


 


 

Customer: replied 2 years ago.

I am trying to pay a bonus but the webpage went off? don't worry I will pay it!!

Expert:  John D replied 2 years ago.

Hi Rob

 

You said remove the "buttons" and that what the macro does. It removes all drop down buttons leaving the text in the cells

 

Are you saying that the macro should clear the sheet and remove all text and images (i.e. blank sheet except for row2)?

 

Yes it gets the first match only. There was no instructions otherwise

 

Yes it only works if all three fields have data in order to avoid user missing any data. There was no instructions otherwise

 

No problem I will modify the macros to take care of these points

 

 

Customer: replied 2 years ago.

Hi John


 


 



You said remove the "buttons" and that what the macro does. It removes all drop down buttons leaving the text in the cells. > I think a misunderstanding I did say; I would also need a 'clear all' button on the 'Check' sheet which when pressed clears the 'Check' sheet. This will enable me to get a new set of results, OR to append results on the end of the previous search.


 


Are you saying that the macro should clear the sheet and remove all text and images (i.e. blank sheet except for row2)? YES Please.


 


Yes it gets the first match only. There was no instructions otherwise. - A misunderstanding, I tried to show this in my sample, but yes please.


 


Yes it only works if all three fields have data in order to avoid user missing any data. There was no instructions otherwise. - My fault! I realised this point when testing.


 


No problem I will modify the macros to take care of these points.


 


Thank you!


Expert:  John D replied 2 years ago.

Just letting you know that I am on it and its taking some time as the entire code needs to be re-written for continuing the match and ignoring blank fields

 

Will post the file as soon as I'm done

 

UPDATE

 

Here you go

 

 

http://www.filesharesite.com/files/201209/1347247816PaintingsSample080913.xlsm

 

 

Please test it and let me know if needs any tweaking

 

Thank you

 

 

 

Customer: replied 2 years ago.

Hi John

 

Very impressive!

I put your macro into my working SS, I have just one question how do I extend the range of the search in the 'detail list'? My current detail list goes to 2341 and is still expanding. I have tried tinkering with your macro but I can't do it! Please can you indicate on your code here?

' Get Painting details from search criteria
Sub GetPainting_3()
Set sh1 = Sheets("Detail List")
Set sh2 = Sheets("Check")
zL = sh2.Range("B1")
zT = sh2.Range("F1")
zS = sh2.Range("O1")
If zL = "" And zT = "" And zS = "" Then
MsgBox "Error! All 3 Drop Downs are empty"
Exit Sub
End If


Application.EnableEvents = False
n = 0
For r = 3 To 113 Step 10
For c = 2 To 23 Step 7
'sh1.Cells(r, c).Select
t1 = sh1.Cells(r, c + 4)
t2 = sh1.Cells(r + 2, c + 4)
t3 = sh1.Cells(r + 4, c + 5)
If zL = "" Then t1 = ""
If zT = "" Then t2 = ""
If zS = "" Then t3 = ""

If t1 = zL And t2 = zT And t3 = zS Then
n = n + 1
rr = 0
cc = 0
For rr = 3 To 139 Step 7
For cc = 2 To 26 Step 8
If sh2.Cells(rr, cc + 4) = "" Then
r2 = rr
c2 = cc
GoTo 111
End If
Next cc
Next rr
111

Many thanks Rob

 

Expert:  John D replied 2 years ago.

Ok will modify the code and get back to you

 

 

Customer: replied 2 years ago.
Thanks
Expert:  John D replied 2 years ago.

Ok done!

 

http://www.filesharesite.com/files/201209/1347291375PaintingsSample080914.xlsm

 

This version detects the last row that has data and sets up this row as the last row for the search

 

Hope this helps

 

Thank you

 

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.

Hi John


 


Thats great! A minor bug in the clearall though, It throws an error but not every time..


I've put data in the Detail List that makes it throw the error - there are only 4 paintings- search 'STORAGE', Size '8x20', gives 4 results OK, BUT the ClearAll macro hangs up?


http://wikisend.com/download/443046/PaintingsSample080914.xlsm


 


 


Thanks

Customer: replied 2 years ago.

John, I just tried again to pay a bonus and it accepted/confirmed my bonus but then after a few moments 'processing' took me through to the new question page, I don't think the bonus went through? Please confirm and I will contact JustAnswer admin immediately.


Thanks

Expert:  John D replied 2 years ago.

The bonus did go through. Thanks so much Rob :)

 

I am not able to get that file (wikisend site seems to be down at the moment).. Could you try uploading it here

 

http://www.filesharesite.com/

 

 

 

 

Expert:  John D replied 2 years ago.

I am not able to get that file (wikisend site seems to be down at the moment).. Could you try uploading it here

 

http://www.filesharesite.com/

 

 

 

Customer: replied 2 years ago.

http://www.filesharesite.com/files/201209/1347298960PaintingsSample080914.xlsm

Expert:  John D replied 2 years ago.

Ok there are some issues with some of the uploaded images which prevent them from being deleted as pictures. In the following file I used another method of deletion which should work on all images and objects

.

 

http://www.filesharesite.com/files/201209/1347300442PaintingsSample080914NEW.xlsm

 

 

Hope this is ok now

 

 

Customer: replied 2 years ago.

Almost there... it clears the buttons on the dropdown lists on row1 :-)

Expert:  John D replied 2 years ago.

Ok please check this one (with some other enhancements)

 

http://www.filesharesite.com/files/201209/1347303647PaintingsSample080914NEW1.xlsm

 

 

Hope this is ok. Let me know if it needs any other tweaking

 

John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9658
Experience: Bachelor of Science - Engineering Consultant
John D and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 2 years ago.

Excellent, well done!

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional