Hello,
I'm wondering if someone could help me with a loop.
Now I'm hardcoding the ranges (marked with red) in a loop and it takes a long time to go through all rows.
First range: ("A2:U2")
Second : ("A3:U3")
etc.
It should loop Until every cell in range("A*:U*") IsEmpty.
Here is the code I have so far:
Any help would be much appreciated
Please Login or Register to view this content.
Add a variable to determine the last row, ie:
then substitute the variable lr for the last row, ie:Please Login or Register to view this content.
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Alan,
Thanks a lot for answering my question.
First I must tell you I'm a noob to VBA so please have patience with me.
I put the variable one line above the "Do While Len(FileName)>0" loop and changed the red marked ranges with Range("A2:U" & lr).
The code runs perfect the first range ("A2:U2") but then stops.
Can you please tell me how to loop it down the ranges from ("A2:U2") to ("A*:U*") until next range (all cells in ("A*:U*")) is empty.
I guess this must be a new loop "outside" my first loop? (Loop in loop)
Thanks again
it would be very helpful in understanding what you are attempting to do if you post a sample worksheet and explain what you wish to happen versus trying to understand what you want in your code with the current explanation.
I think this is what you mean:
This code should do that but I haven't tested it. It's not practical to build a test file from scratch, plus this is only part of your code and I don't know what else you have. If you attach your file I would be happy to test it.Please Login or Register to view this content.
Please Login or Register to view this content.
Thanks a lot for your code Jeff, but,
I get Run-time error '438':
Object doesn't support this property or method, in line:
wbk.Sheets(1).Range("B39:V39") = wbdest.Worksheets("Sheet1").Rg.Value
Here comes the full code with an explanation:
From workbook viktutv.xlsm, Sheet1, code copy first range ("A2:U2") to first workbook, Sheet1 Range("B39:V39") in "Test3" folder.Please Login or Register to view this content.
Then code "copy" the value in cell "R19" or "P19", in that workbook.
Copied value is "pasted" into workbook viktutv.xlsm, column "W".
This is repeated for all workbooks in "Test3" folder and so far everything works fine.
After all workbook's "R19 or P19" values are pasted, code calculates the average and paste result in column "V" adjacent to range in use, i.e. If it is first range ("A2:U2") then it paste the result in "V2", second range ("A3:U3") in "V3" etc. etc.
First row in workbook "viktutv.xlsm" have headers in column A-Y.
Second to n-th row have numbers in many cells but not all, in column A-U.
After n-th row, comes a row with emty cells from column "A" to "U" and the loop shall stop.
As it is now, I have to manually change from first range ("A2:V2") to second range ("A3:V3") etc etc (see highlighted code in red).
I hope this clarify what I try to accomplish with the code.
Thanks again
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
I think this should beOriginally Posted by Sluggoslabang
I can't offer anything else unless you attach your file, as suggested very wisely by alansidman.Please Login or Register to view this content.
Jeff,
Same error with that line.
Now I have attached a workbook that should make it easier to understand what I try to accomplish.
Thanks again both of you
That is not a macro-enabled workbook.
6StringJazzer,
Hello and thanks for working with my problem.
Yes, I know it's not macro-enabled. It's only for visualizing the problem.
Because of confidentiality, I can't upload the real workbooks.
Try this:
Create four new workbooks in a folder. Use my filenames and add values in cell R19 or cell P19 (Sheet1), depending on date in filename.
Change paths for folder and "viktutv.xlsm" in my code, to suit yours.
Copy and paste my code into a new module, in "viktutv.xlsm".
Run the code.
Now you will see my code only do the first range (A2-U2).
I want it to do all of them (down to row 14).
Hope this is understandable.
Hi,
Rg is a variable and therefore not a property of a worksheet. Either you must use:
or something akin to thisPlease Login or Register to view this content.
However from my brief perusal of the code I am not certain that Rg is being set using the correct worksheet.Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi xlnitwit,
Thanks for help.
I tried both and both do the copying inside wbk.
How to make it copy from wbdest?
Both of the Rg assignments should be changed from
to readPlease Login or Register to view this content.
or more simplyPlease Login or Register to view this content.
Please Login or Register to view this content.
xlnitwit,
Yes, we are getting closer,
Code get the ranges (from viktutv.xlsm) as intended but it test it only on the first workbook.
It should be the other way round.
Code should use one range (from viktutv.xlsm) at a time, on every workbook in wbk and then calculate the average.
So, before code starts with the second range it must calculate the average and put that number in column "V" adjacent to it's range.
I am sorry for my english but I hope you can understand what I'm trying to do.
Btw, the loop doesn't stop at first range with empty cells, so it can't continue with the rest.
I'm afraid I do not follow what you are saying. Can you please post the full code that you have now and state clearly what is happening, as well as what should be happening?
Hi xlnitwit,
Ok, here is another explanation:
In Workbook "viktutv.xls(m)", (as you can download from this thread) you find the "ranges" I am talking about.
You find them in "viktutv.xls(m)", and they are:
First range is: Column "A" Row 2 to Column "U" Row 2, ("A2:U2").
Second range is: Column "A" Row 3 to Column "U" Row 3, ("A3:U3").
Last range is: last row with numbers in any cell, in column "A" to "U".
In this example, row 14 is the last row, but must be dynamic in the code.
In my "Test3" folder, I have many workbooks (*.xlsm).
My code paste first range ("A2:U2") from "viktutv.xlsm", in first workbook in folder "Test3", in cells ("B39:V39").
Extracts filename from first workbook in folder "Test3", extracts date from that filename.
Then copying Cell R19 or P19 in first workbook in folder "Test3", and paste that value in column "W" in "viktutv.xls(m)", adjacent to its filename in column "X".
Then code loop and do exactly the same with second workbook in folder "Test3",
and so on, until the last workbook in folder "Test3".
After deleting the highest value in column "W" in "viktutv.xlsm", my code calculates the average (of those values) and copy that value to cell "V2", adjacent to its range ("A2:U2").
So far my code is working good.
Here comes my problem:
(I think we need another loop here for the ranges)
Now I want the code to change from first range in "viktutv.xls(m)", to second range in "viktutv.xls(m)", and start from beginning again with first workbook in folder "Test3" (see above), and so on until all workbooks in folder "Test3", are processed with the second range.
After that, paste the new average, adjacent to its range ("A3:U3").
And so on, until the last range in "viktutv.xls(m)" is processed on all workbooks in folder "Test3".
I hope this made everything crystal clear (joking)
Here is my original code:
Please Login or Register to view this content.
Last edited by Sluggoslabang; 09-06-2016 at 11:41 AM.
Given that the original code does what is required, we can in theory simply add another loop like so
This is extremely inefficient since you are opening and closing workbooks repeatedly but I fear I do not currently have sufficient time to refactor the code.Please Login or Register to view this content.
WOW!!!
You are an absolut genius.
After a couple of modifications it works like a charm.
A simple thanks is not enough, but what can I say!
I agree, code is extremely inefficient. It takes a long time to process all workbooks but what to do.
THANK YOU!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks