vba - Excel "Do While" Loop Not functioning correctly after first iteration -

-

i have sub light formatting, , need evaluate , count whether column contains "1" or nothing, , if column has header isnumeric or not.

first iteration of do...until loops functions should. however, if try run second time, throws active cell way rightmost column in worksheet (xfd). have total of 114,000 rows need loop through.

please see code below, first loop; need nested inside loop cycling through rows:

sub totalbookcountsprocess()      dim ws excel.worksheet     dim numberedbooks integer 'total number of physical books     dim virtualbooks integer 'total number of virtual books     dim firstbookcol integer 'first column book number     dim ispeeccol integer 'ispec column     dim lastworksheetcol integer 'last column in worksheet after adding total book count columns     dim loopcoloffset integer  'offset column amounts new row reset after loop     dim lastitem string 'last item number in last row of worksheet      activecell.end(xldown).select     lastitem = activecell.value     activecell.end(xlup).end(xltoright).select     activecell.offset(0, 1).select     activecell.value = "total numbered books"     activecell.offset(0, 1).select     activecell.value = "total cs books"     lastworksheetcol = activecell.column      columns.autofit      numberedbooks = 0     virtualbooks = 0      cells.range("1:1").find("ispec").select      ispeccol = activecell.column     firstbookcol = ispeccol + 1     activecell.offset(1, 1).select      loopcoloffset = ((lastworksheetcol - firstbookcol) * -1)  until activecell.end(xlup).value = "total numbered books"     if activecell.value = 1 , isnumeric(activecell.end(xlup).value) = true         numberedbooks = numberedbooks + 1         activecell.offset(0, 1).select     elseif activecell.value = 1 , isnumeric(activecell.end(xlup).value) = false         virtualbooks = virtualbooks + 1         activecell.offset(0, 1).select     else         activecell.offset(0, 1).select     end if loop      activecell.value = numberedbooks     activecell.offset(0, 1).select     activecell.value = virtualbooks     activecell.offset(1, loopcoloffset).select    end sub

any insights appreciated.

the reason lies in 

activecell.end(xlup).value = "total numbered books"

as ending condition of loop

your real goal end row loop activecell column 1 "total numbered books" value in first row

but

  • activecell.end(xlup).value refer first non empty cell above activecell

  • from second iteration on, cell column first row value "total numbered books" has cell right above filled numberedbooks value

  • so keeps skipping next column till end of columns...

your code follows:

option explicit  sub totalbookcountsprocess()      dim ws excel.worksheet     dim numberedbooks integer 'total number of physical books     dim virtualbooks integer 'total number of virtual books     dim firstbookcol integer 'first column book number     dim ispeeccol integer 'ispec column     dim lastworksheetcol integer 'last column in worksheet after adding total book count columns     dim loopcoloffset integer  'offset column amounts new row reset after loop     dim lastitem string 'last item number in last row of worksheet     dim ispeccol long      activecell.end(xldown).select     lastitem = activecell.value     activecell.end(xlup).end(xltoright).select     activecell.offset(0, 1).select     activecell.value = "total numbered books"     activecell.offset(0, 1).select     activecell.value = "total cs books"     lastworksheetcol = activecell.column      columns.autofit      numberedbooks = 0     virtualbooks = 0      cells.range("1:1").find("ispec").select      ispeccol = activecell.column     firstbookcol = ispeccol + 1     activecell.offset(1, 1).select      loopcoloffset = ((lastworksheetcol - firstbookcol) * -1)      numberedbooks = 0     virtualbooks = 0     until cells(1, activecell.column) = "total numbered books"         if activecell.value = 1             if isnumeric(cells(1, activecell.column))                 numberedbooks = numberedbooks + 1             else                 virtualbooks = virtualbooks + 1             end if         end if         activecell.offset(0, 1).select     loop      activecell.value = numberedbooks     activecell.offset(0, 1).select     activecell.value = virtualbooks     activecell.offset(1, loopcoloffset).select loop until cells(activecell.row - 1, 1) = lastitem   end sub

where added rows loop

but sure real solution avoiding selecting/activating


Comments

Post a Comment

Popular posts from this blog

ios - RestKit 0.20 — CoreData: error: Failed to call designated initializer on NSManagedObject class (again) -

-
this error has shown on many times, seems. prominent answer issue seems restkit 0.20 — coredata: error: failed call designated initializer on nsmanagedobject class however, solution posted doesn't work me. let's start beginning: i want upload file server. want parse response make sure uploaded. if not, try again later. model has boolean variable called nsnumber *senttoserver purpose (in coredata, bool saved nsnumber ) . because don't know whether sending going successful, save entity coredata model first before sending via restkit. when post request successful, server returns json string this: {"id":14,"created":true} notice that, of course, id not set on coredata model yet, both id , created new informations model. here's model: @interface recording : nsmanagedobject @property (nonatomic, retain) nsstring * audiofilepath; @property (nonatomic, retain) nsdate * createdat; @property (nonatomic, retain) nsstring * deviceid; @...
Read more

java - Digest auth with Spring Security using javaconfig -

-
so i'm trying create digest authentication spring following documentation trying translate xml "requirements" in java requirements. let's have xml in docs: <bean id="digestfilter" class= "org.springframework.security.web.authentication.www.digestauthenticationfilter"> <property name="userdetailsservice" ref="jdbcdaoimpl"/> <property name="authenticationentrypoint" ref="digestentrypoint"/> <property name="usercache" ref="usercache"/> </bean> <bean id="digestentrypoint" class= "org.springframework.security.web.authentication.www.digestauthenticationentrypoint"> <property name="realmname" value="contacts realm via digest authentication"/> <property name="key" value="acegi"/> <property name="noncevalidityseconds" value="10"/> </b...
Read more

laravel - PDOException in Connector.php line 55: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) -

-
my .env: app_env=local app_debug=true app_key=myappkey app_url=http://localhost db_connection=mysql db_host=localhost db_port=3306 db_database=gestiontaller db_username=root db_password='root' cache_driver=file session_driver=file queue_driver=sync redis_host=127.0.0.1 redis_password=null redis_port=6379 mail_driver=smtp mail_host=mailtrap.io mail_port=2525 mail_username=null mail_password=null mail_encryption=null i've tried password ", ' , without, restarting server every time. i've tried changing localhost 172.0.0.1. yes, password correct, running mysql -u root -p introducing password root in prompt can go mysql. users table: +------------------+-----------+ | user | host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | | root1 | localhost | +------------------+-----------+ i've added homestead.yaml this: database_port: 3306 ...
Read more