Ranges VBA

[bjcowen9000]

Hello, I was wondering if anyone could help me out with ranges? I would like to have a specific range chosen for each worksheet for the following function to be applied. The range needs to be
X2:AI. the reason there is not a number after AI is because I need it to go down to the last row of data on each sheet. So you could maybe reference where column A stops? The amount of rows is different on each sheet and I would like to loop through all of my worksheets except the one titled "Summary" Can anyone help me please?

Please Login or Register  to view this content.

[arlu1201]

Untested -

Please Login or Register  to view this content.

[bjcowen9000]

Hmmm that does seem like a very good start! When I go to cell AJ and type =GetCells(X2:AA2) it gives me the error: "Duplicate declaration in current scope"
If I use my code and do the getcells formula it works, but the problem is that I have to go to each sheet and highlight each selection. Do you have any idea why your code produces that error?

[bjcowen9000]

Does anyone know how to fix an error that says "Duplicate declaration in current scope"

[bheanloh]

I assumed that the user doesn't get to select the cells

Please Login or Register  to view this content.

[bjcowen9000]

Ok I see. No more error but when I go to col AJ and type =getcells(X2:AI2) it says #Value. Any ideas why?

[bheanloh]

u try =getcells() and see if this is what you want

[bjcowen9000]

It is getting really close! But it is just storing the data from the first worksheet and when I do get cells, it does all of them, instead of just the row i want :/

[arlu1201]

In that case, you need to specify the myrange to be the range you want. You do not have to calculate the lastrow to specify the range.

[bjcowen9000]

I am still not really getting it to work. When I use the code I posted and then go to excel and use the get cells formula, it gives me the #value error.

[arlu1201]

It may help if you explain what you are trying to do with the function.

[bjcowen9000]

I have 80 worksheets. On each worksheet I have column F that has a sentence. However, some of these sentences are too long. So I send text to columns over columns X:AI where I can edit individual words and make them shorter or delete some. Then, I concatenate them into column AJ. Then in column AK I show the character length of each cell in column AJ and if it is greater than 31, I highlight it. does this make sense? I have two codes that somewhat work but both have problems. The first is the following but if I edit any data, it doesn't automatically update column AJ (I have to run macro each time).

Please Login or Register  to view this content.

I also have the following but it's not the best because I have to highlight each range in each worksheet I want, and then type =getcells() in column AJ:

Please Login or Register  to view this content.

[arlu1201]

Why do you need a function for getcells? You can directly automate the whole process where the columns are copied over to X onwards and text to columns performed and the length checked and then highlighted.

If you upload a sample file, i can try and help you with the pending portions.

[bjcowen9000]

Well, i also need them to be concatenated and the issue there is spacing. I need just the concatenated cells to insert a space after every word except the last one. Can that be done also? I know it can, but just want to clarify. How do i attach a file? And I do not have to use the getcells formula. The first code works amazingly, it just doesnt update unless i rerun it

[arlu1201]

Yes, through code we can try and get as much as possible automated.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[bjcowen9000]

Well, i also need them to be concatenated and the issue there is spacing. I need just the concatenated cells to insert a space after every word except the last one. Can that be done also? I know it can, but just want to clarify. How do i attach a file?

[bjcowen9000]

I just need a way to send all sheets to columns, be able to edit those columns, concatenate so that it is a sentence with the right spacing and do everything for all sheets at once.

[arlu1201]

Regarding the concatenation, you have opened a thread http://www.excelforum.com/excel-prog...problem-2.html. Didnt it get resolved here?

[bjcowen9000]

I cannot get the values in AJ to update still unless I rerun the macro manually. How can I fix this? I tried everything. He also said it would only work if a single cell in AI is changed. I can use his code if you can figure out a way to make the code run when any cell in X:AI is changed

[bjcowen9000]

Ok, I figured this out but can I do it for all sheets rather than current selection. That way I do not have to right click every single tab:

Please Login or Register  to view this content.

[bjcowen9000]

Is there a way to have this code run for all worksheets instead of having to right click on each one and click view code

Please Login or Register  to view this content.

[taps]

Hi,

Try within Thisworkbook method


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub



Regards
taps

[bjcowen9000]

I am not sure what you mean. do i just create a new macro and put that? can you me exactly waht you mean?

[taps]

Excel VBA Editer.jpg

Hi write your entire codes within the sub shown in picture.

Regards
taps

[bjcowen9000]

But in my code I have set ws = active sheet. wont that only work on 1 sheet?

[Norie]

Use Sh which is the sheet argument passed to the sub.

[taps]

Testing is essential for every programming.
Please try, hope it will work.

[bjcowen9000]

now when i run waht you said, it doesnt even update the current worksheet that i am on. maybe i am missing something? I have:

Please Login or Register  to view this content.

[taps]

Hi,

Could you please attached an excel sample file for testing?

Regards
taps


Again:::: Sorry, I have missed out your previous post. Please don't attached the sample file.

[Norie]

Change this,

Please Login or Register  to view this content.

you this.

Please Login or Register  to view this content.

Or remove the line and change ws to Sh throughout the code.