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 Richard Your Own Question

Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 30103
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

I have a "bit of code" that I run on cells to sort

Customer Question

I have a "bit of code" that I run on cells to sort the contents - I would like to be able to specify a range (column of data) to have this "bit of code" run on each cell in the range - however I tried a number "loop coding" possibilities nothing works.
Submitted: 5 months ago.
Category: Microsoft Office
Expert:  Zabo04 replied 5 months ago.
I am attaching an example of using the VBA object Selection. It is not a range, but can be assigned to a Range variable which will allow all Range functions to be available for your use. All this function does now is to fill the selected cells with 1, but it is an example of how the user can select the cells to run the code over, determine the selection in VBA and run that code on the selected cells. Here is a link to the example, select the download option and check the file extension is xlsm and not xlsx (mediafire has a few issues lately): If you want to attach your file so I can look at it directly, use a web upload site (such as wikisend) and paste a link here to the file, since xlsm files cannot be directly attached to these chat sessions. Let me know what else you need to know for this question.
Customer: replied 5 months ago.
Poor response - all I need is something to act on the range of cells I specify - I certainly don't need this!
Customer: replied 5 months ago.
Take your advice and give it to someone else.
Expert:  Zabo04 replied 5 months ago.
This acts on the range of cells you select. If you would specify how you want to specify the cells I can help you more. I need more details though.
Expert:  Zabo04 replied 5 months ago.
What I showed your was a selection using no textboxes or otherwise. Based on your description I have no way of knowing how you are specifying the selection. For an average user using the mouse and a command button is the easiest method. It did not sound like you wanted a hard coded solution of range.
Customer: replied 5 months ago.
The column of cells has various values examples: A1345, A1127, A987 (in the cell) the code runs to sort the values within the cell - now imagine a column of cells all with different values in them - I want to loop through each cell and sort the values within it - once applied to a cell in the column go to the next cell and sort the values in it.
Customer: replied 5 months ago.
Hang in there we'll get it.
Expert:  Zabo04 replied 5 months ago.
when I first read the question I thought you had a VBA function to do the sorting. Which meant you needed to get the selection of cells to work on. So you would specify the range and then loop through the range using for next to advance through the cells. Do you need just to loop through the cells or do you need the vba to do the sorting as well? Could you attach a three cell example to this chat so I can see on one sheet a start and on a second sheet the result you want. That will allow me to see exactly what you want (three cells should be fine). How do you want to select the cells to run the code on? The Selection keyword gets the selected cells from the mouse, would you rather enter it in two cells (one cell for start cell and one for end cell)? Or is it a fixed column everytime (to allow hard coding in the VBA code)?
Customer: replied 5 months ago.
example attached
Expert:  Zabo04 replied 5 months ago.
my entering plan would be to write the VBA code that will split on commas, sort, then change the cell value, re-inserting commas into the sorted list. I would use the mouse/cursor to select the three cells and use the Selection keyword to access those cells. I think a command button would work to execute the actual vba on the selected cells.
Customer: replied 5 months ago.
The code I had provided did the sorting - all I'm looking for is something to loop through a range of cells in a column - that's it.
Expert:  Zabo04 replied 5 months ago.
It's not letting me accept the request.
Customer: replied 5 months ago.
I can select the cells or input the range either way will be fine.
Customer: replied 5 months ago.
OK let me check it out and see if if there are any issues.
Expert:  Zabo04 replied 5 months ago.
in the xlsm file I provided you, it loops through the cells you select with the mouse. These two lines of code create a range to loop over.Dim selectRange As RangeSet selectRange = Selection This loop will loop through all cells in the range. Put your code between the for and next for it to run on each cell in the range.For Each cCell In selectRange ' Put your code here to execute the sort on the current cell, which is the variable cCell. Next cCell
Expert:  Zabo04 replied 5 months ago.
If you can use wikisend to send me the xlsm with the sort code, I can implement it for you.
Customer: replied 5 months ago.
I need to check to see if this will work - I used number of things to loop through range of cells nothing worked - thanks for you help. There may be more of this in the future - thanks again.
Expert:  Zabo04 replied 5 months ago.
Keep asking questions. I put revision numbers on my files, because the first attempt is rarely the full answer.
Expert:  Zabo04 replied 5 months ago.
If you know the cells you are going to use, and they won't change (which we all say when we program VBA then it changes and you have to edit VBA code). This code loops through A1 to D10.For Each c in Worksheets("Sheet1").Range("A1:D10") ' Code to work on cell C here.Next c If it is a column you can loop through every row in a column, below would be column A (so you do not have to know the last used cell):For Each c in Worksheets("Sheet1").Range("A:A") ' Code to work on cell C here.Next c
Customer: replied 4 months ago.
code still does not work properly - it only sorts the first cell and does not continue down the column of selected cells.
Expert:  Zabo04 replied 4 months ago.
I do not have your code to look at, so I cannot evaluate where the error is. I also leave through 5/31 in 30 minutes, so I am opting out for another expert to answer.
Expert:  Richard replied 4 months ago.
Hi there and welcomeSo to check, you mean you want to sore the data within an actual Cell correct? As you have 3 values in one cell, and you want to sort these three values?You then want to specify the range of cells and it would then go through and do this?Please let me know

Related Microsoft Office Questions