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: 9666
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

This answer was rated:

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

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 4 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

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 4 years ago.

Hi John Here is link;


 


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


 

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 4 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?

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 4 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?

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

 

 

Customer: replied 4 years ago.

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

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 4 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.

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 4 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 :-)


 

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 4 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?

Ok

 

Customer: replied 4 years ago.

John.


 


Please see amended question on


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


 


Thanks Rob

Ok will review and get back to you

 

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: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

Hi John I made an interim payment...

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?

 

 

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

 

 

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

 

 

 

 

 

 

 

Customer: replied 4 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.


 

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

 

 

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: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and other Microsoft Office Specialists are ready to help you
Customer: replied 4 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 4 years ago.

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

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 4 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!


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 4 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

 

Ok will modify the code and get back to you

 

 

Customer: replied 4 years ago.
Thanks

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: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and other Microsoft Office Specialists are ready to help you
Customer: replied 4 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 4 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

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/

 

 

 

 

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 4 years ago.

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

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 4 years ago.

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

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: 9666
Experience: Bachelor of Science - Engineering Consultant
John D and other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

Excellent, well done!