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?
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.
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?
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
Hi John Here is link;
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
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.
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
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
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.
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
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?
Please see amended question on
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
Hi John I made an interim payment...
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)
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)
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
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
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!
I am trying to pay a bonus but the webpage went off? don't worry I will pay it!!
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
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.
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
Here you go
Please test it and let me know if needs any tweaking
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 criteriaSub 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 = "" ThenMsgBox "Error! All 3 Drop Downs are empty"Exit SubEnd IfApplication.EnableEvents = Falsen = 0For 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 rr111
Many thanks Rob
Ok will modify the code and get back to you
This version detects the last row that has data and sets up this row as the last row for the search
Hope this helps
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?
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.
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
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
Hope this is ok now
Almost there... it clears the buttons on the dropdown lists on row1 :-)
Ok please check this one (with some other enhancements)
Hope this is ok. Let me know if it needs any other tweaking
Excellent, well done!