Hi all i am trying to make this work..
i have to loop some timers in cells.. in another sheet..
with row number from the pool of numbers in the range in this sheet...
It my first time tryng to use this style and it would help me a lot for many other uses..ty !!
Please Login or Register to view this content.
Last edited by ZuluNation; 04-19-2024 at 01:06 PM.
Just use value without Set:
Please Login or Register to view this content.
if i remove set...i have errror..Originally Posted by zbor
ERROR 91 object variable or with block variable not set
on
Xy = aWX.Range("BM" & aWR.Range("AY" & i)).Value
Yes, same for other.
Set assigns an object reference to a variable or property.
Here you are dealing with numbers so you should declare Xy and RX as numbers and use just as I wrote in previous post, without Set.
Setting sheets at the start of the code is fine.Please Login or Register to view this content.
RX is a "x" value so i setted as String...
anyway now i have ...
run-time error 1004 method range of object worksheet failed
on
Xy = aWX.Range("BM" & aWR.Range("AY" & i)).Value
AW1 and AX1 must be numbers.Please Login or Register to view this content.
i will loop from AW value to AX value (i.e. AW1 = 5, AX1 = 8).
And then also AYi must be a number.
So BM+number must also be a number.Please Login or Register to view this content.
It would be easier to see your excel file. I've tried to enter numbers in all those fields and it works for me.
AW1 and AX1 must be numbers.Please Login or Register to view this content.
i will loop from AW value to AX value (i.e. AW1 = 5, AX1 = 8).
And then also AYi must be a number.
So BM+number must also be a number.Please Login or Register to view this content.
It would be easier to see your excel file. I've tried to enter numbers in all those fields and it works for me.
AW1 and AX1 and AYi are numbers..
BM+number could be BLANK.....
i have a sheet with few timers in column BM... for dynamic excel...
but using a for next to find if the timer is active ... all the raws (10 000 ) is some cpu expensive....
if it was possible to have an excel function that returns all the non blank row numbers in a range or column..
then make a for next using all those values as i ....
To return all non-blank cells, you can use this formula:
=FILTER(A2:A500,A2:A500,<>"")
or for a whole column:
=FILTER(A:A,A:A,<>"")
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
As per zbor...
I am very certain that if you supply a sample file showing your requirement, it will be much easier to offer solutions...perhaps even more simplistic...It would be easier to see your excel file. I've tried to enter numbers in all those fields and it works for me.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
SAMPLE_1.xlsx
here is the sample file..
just have to make a fully indipendent sub timer on column M
FOR each cell in column M (or range M2:M5000)
if the cell is not blank , THEN add 2, IF > 20 Then ClearContents
i would like to use only non empty cells in the FOR NEXT Loop
because of cpu Time..
could use an intermediate solution using INDEX FORMULA that returns all the non empty cell RAW NUMBERS.. in Range or column
Last edited by ZuluNation; 04-20-2024 at 09:26 AM.
So after adding 2 do you then check if > 20 or only on the next run?if the cell is not blank , THEN add 2, IF > 20 Then ClearContents
Have no idea what this file and requirement has to do with your initial posted code and further remarks...Please Login or Register to view this content.
Anyway depending on data size...storing into array first will be faster...
Please Login or Register to view this content.
Last edited by sintek; 04-20-2024 at 09:32 AM.
-maybe "check if >20" on next run is even more efficient.. ?? in this case yes !!!
- i tryed a cervellotic road to find the solution by myself !!!
I am testing it.. i have to give the name of the sheet..
and i need it for maybe 30 / 40 000 rows..
with simple for next i can do it by myself but cpu usage would be indecent
with my originary system I have the row numbers by precedent calculation or i have them ready in a Range anyway.... (but its a moving range..)
Last edited by ZuluNation; 04-20-2024 at 09:50 AM.
-maybe "check if >20" on next run is even more efficient.. ?? in this case yes !!!have to give the name of the sheet..Please Login or Register to view this content.
Last edited by sintek; 04-20-2024 at 09:51 AM.
ok really ty very much in the meantime..
i need some time to test it ..
Okie Dokie...Happy Coding...
actually all counters stuck on 1... and not moving...
i call it in my main sub...
Call TimerJ3v16
no errors...
active row count is actually about 25 000 and the whole column is blank cells by itself... except some..few cells where can appear 1
certainly i used the correct name of the sheet and of the range...
Last edited by ZuluNation; 04-20-2024 at 01:42 PM.
i have to fix this..
wrong number...
(.Rows.Count, 13)
NOW IT SUPEROOOOK !!!! the cpu usage seems to be about 0.03 0.05 more
Last edited by ZuluNation; 04-21-2024 at 04:37 AM.
cpu usaage some more ..about 0.10...doubles up my current use (when needed i could use many oldest cpu)
maybe could use excel function to retrieve rows numbers...??
i have to fix this..
wrong number...
(.Rows.Count, 13)maybe could use excel function to retrieve rows numbers...??
Perhaps if you can explain in more detail it's use and supply a larger dataset one could try and simplify...
just if the excel function is more efficient than the vbe
then loop thru the raw numbers from the function..
I don't quite undestand your request...
Explain to me waht the issue is and I am sure someone will be able to resolve...Upload a larger dataset explaining this request...
i just want to try to use the INDEX or LOOKUP formula to retrieve the array of the non empty cells in the columns
and loop thru these results.. with for next in visual basic..
updating the formula each N seconds..maybe i can gain a few cents of CPU...
Code in post 16 takes 0.02 seconds to run through 50000 rows of data...
Making use of a formula to find non blank cells and then to loop only these will take much much longer...
No way of this happening...updating the formula each N seconds..maybe i can gain a few cents of CPU...
Here is another option...faster than your formula idea and looping but slower than post 16...
Please Login or Register to view this content.
Last edited by sintek; 04-23-2024 at 02:12 AM.
ok ty i will try this last one !!
1st 1 is doing great
i am still chaing some the architecture
have to make some copy paste and few fixes
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks