vba – Цитирование по количеству строк и сохранение значения переменной

как могут одни и те же коды привести к разным результатам?

Этот код:

Sub test() i = 1 For d = 1 To 4 i = i + 1 Debug.Print i Next d End Sub 

печать
2
3
4
5

который является совершенным, но этот

 Sub test2() rcount = 34 For d = 1 To 4 rcount = Sheets("sheettest").Range(Range("A" & rcount + 4), Range("B" & rcount + 4).End(xlDown)).Offset(3, 0).Rows.Count Debug.Print rcount Next d End sub 

печать
203
34
203
34

что я не ожидаю

почему второе значение 34 снова?

Я понимаю, как вы пытались это сделать, и я не мог мгновенно коснуться проблемы, но я считаю, что это приведет к тому же самому по-другому.

 Sub ListSectionCounts() Const colToCheck = 1 'check column A Dim sh As Worksheet, sectionRows As Long, sectionCount As Long, rw As Range Set sh = ActiveSheet 'use the currently selected sheet For Each rw In sh.UsedRange.Rows 'loop through all rows that have data If sh.Cells(rw.Row, colToCheck) <> "" Then 'this row has data so count it sectionRows = sectionRows + 1 Else 'this row does not have data If sectionRows > 0 Then 'if this is the 1st row without data then print the count and reset the counter sectionCount = sectionCount + 1 Debug.Print "section #" & sectionCount & " row count: " & sectionRows sectionRows = 0 End If End If Next rw 'check if there's one more section to list If sectionRows > 0 Then sectionCount = sectionCount + 1 Debug.Print "section #" & sectionCount & " row count: " & sectionRows End If Debug.Print "Finished (" & sectionCount & " sections)" End Sub 

(Попробуйте это как есть, но тогда вам может потребоваться изменить лист из Activesheet, и если вам нужно его проверить столбец, кроме «A», то измените colToCheck .)


Изменить: в дополнение к комментариям, это тот же код, тот же результат, но смущен и сложнее понять, что происходит:

 Sub secCnt() For Each rw In ActiveSheet.UsedRange.Rows If Cells(rw.Row, 1) <> "" Then sRw = sRw + 1 ElseIf sRw > 0 Then Debug.Print "rows:" & sRw: sRw = 0 End If Next rw If sRw > 0 Then Debug.Print "rows:" & sRw End Sub 

«Как я устраняю …»

Я знаю, что вы использовали мое «альтернативное решение», но вы все еще задавались вопросом, что не так с вашим кодом, в первую очередь, как и я. Поскольку я не писал это, я не был уверен, как именно вы пытались выполнить до тех пор, пока я не разложил проблему на гораздо меньшие части, не перешел через нее и не вернул ее обратно. Я стараюсь хранить временный файл Notepad ++ при поиске и устранении неполадок, поэтому я решил, что я поделюсь, а еще пару человек спросили меня о моем процессе устранения неполадок.

Имейте в виду, что я самоучка (Google, детка!), Поэтому я, вероятно, нарушу некоторые стандарты кодирования. Стандартизация в кодировании существует по уважительным причинам, и я всегда учусь, но в конце дня я считаю, что «все, что делается на работе», является правильным способом в конце. Я вовсе не говорю, что «мой путь – это правильный путь», потому что существует множество «правильных» способов решения проблемы.

(Пока я вставляю свои частично отформатированные заметки, и я вернусь и переформатирую позже!)

Я не хотел захватить вашу нить, но это был отличный пример, который я хотел бы поделиться с другими, кто может быть заинтересован!


 'Option Explicit 'How I trouble-shot the issue (explained the long way for future illustration purposes) 'Note I replaced `Sheets("sheettest")` with `ActiveSheet` for testing purposes; you may need to change that back 'I need to return the values: 11, 9, 7, 4 (based on my sample dataset) 'The problem code below returns: 11, 20, 11, 20 ...but why? Sub test2_orig() rcount = 34 For d = 1 To 4 rcount = ActiveSheet.Range(Range("A" & rcount + 4), Range("B" & rcount + 4).End(xlDown)).Offset(3, 0).Rows.Count Debug.Print rcount Next d End Sub 

Сначала я хотел увидеть ТОЧНО, что происходит на каждом шагу. Это может быть достигнуто с помощью « Просмотр » переменных (VBE Debug Menu), но я, как правило, тяжело использую Debug.Print .

 Sub test2_1() rcount = 34 For d = 1 To 4 Debug.Print "d=" & d Debug.Print "rcount (before calculation)=", rcount rcount = ActiveSheet.Range(Range("A" & rcount + 4), Range("B" & rcount + 4).End(xlDown)).Offset(3, 0).Rows.Count Debug.Print "the calculation:", , "debug.print ActiveSheet.Range(Range(""A" & rcount + 4 & """), Range(""B" & rcount + 4 & """).End(xlDown)).Offset(3, 0).Rows.Count" Debug.Print "rcount (after calculation)= ", rcount Debug.Print "range (used in calculation)=", ActiveSheet.Range(Range("A" & rcount + 4), Range("B" & rcount + 4).End(xlDown)).Offset(3, 0).Address Debug.Print Next d End Sub 

OUTPUT (Sub test2_1):

 d=1 rcount (before calculation)= 34 the calculation: debug.print ActiveSheet.Range(Range("A15"), Range("B15").End(xlDown)).Offset(3, 0).Rows.Count rcount (after calculation)= 11 range (used in calculation)= $A$18:$B$37 d=2 rcount (before calculation)= 11 the calculation: debug.print ActiveSheet.Range(Range("A24"), Range("B24").End(xlDown)).Offset(3, 0).Rows.Count rcount (after calculation)= 20 range (used in calculation)= $A$27:$B$37 d=3 rcount (before calculation)= 20 the calculation: debug.print ActiveSheet.Range(Range("A15"), Range("B15").End(xlDown)).Offset(3, 0).Rows.Count rcount (after calculation)= 11 range (used in calculation)= $A$18:$B$37 d=4 rcount (before calculation)= 11 the calculation: debug.print ActiveSheet.Range(Range("A24"), Range("B24").End(xlDown)).Offset(3, 0).Rows.Count rcount (after calculation)= 20 range (used in calculation)= $A$27:$B$37 

Все еще немного неясно, на ваш намеченный алотизм, лучше всего разбить процесс на наименьшие возможные шаги, чтобы определить, где лежит проблема. Не Loop , просто постарайся получить первый номер справа, с тяжелыми комментариями, чтобы поговорить

 Sub test2_2() 'get to first section (store row number in it's own variable) Dim rowNum As Integer rowNum = 34 'move down 4 rows to skip header rowNum = rowNum + 4 ' = 38 'start range at A38 (and store it in a range object) Dim rgStart As Range Set rgStart = Range("A" & rowNum) 'end range at B.."End(xlDown)" (store it in a range object) Dim rgEnd As Range Set rgEnd = Range("B" & rowNum).End(xlDown) 'combine them into a single range object Dim rg As Range Set rg = Range(rgStart, rgEnd) 'count rows in the range (store separately) Dim sectionCount As Integer sectionCount = rg.Rows.Count Debug.Print rgStart.Address & " to " & rgEnd.Address & " Row.Count = " & sectionCount 'this returned 11 (correct with my dataset) 'now we want to skip three rows and get the next range ' (at this point I realized 1 of 2 issues with your original code, but I'll get back to that!) 'Rownum is still 38. RowNum + sectionCount + 3 will be the start of the headers of the next section: rowNum = rowNum + sectionCount + 3 Debug.Print "next rowNum=" & rowNum 'theoretically we should be able to loop now, starting with the "skip 4 header rows" line... '...instead of changing this sub, we can make a 2nd copy, saving this one for reference ("last working version") as we proceed '...while tidying, we relocate all our variable declarations to the top of the new sub , and ensure Heavy Commenting for the sake of self & others End Sub 

,

 'Our "tidied" version: (only cosmetic changes for now) Sub test2_3() Dim rowNum As Integer 'the current row number Dim rgStart As Range 'start of the range for the current section Dim rgEnd As Range 'end of the range for the current section Dim rg As Range 'the entire range for the current section Dim sectionRows As Integer 'count of the rows for the current section rowNum = 34 'get to first section (begin loop AFTER here) 'BEGIN LOOP: rowNum = rowNum + 4 'skip header (4 rows) Set rgStart = Range("A" & rowNum) 'start range at A38 Set rgEnd = Range("B" & rowNum).End(xlDown) 'end range at column B, bottom row of this section Set rg = Range(rgStart, rgEnd) 'combine into single range object sectionRows = rg.Rows.Count 'count rows in the range Debug.Print rgStart.Address & " to " & rgEnd.Address & " Row.Count = " & sectionRows 'for now stick with just debug.print'ing RowCount '(a manual count tells me I'm expecting results: 11 -> 9 -> 7 -> 4 -> 2 using my sample dataset) rowNum = rowNum + sectionRows + 3 'skip to top of next section Debug.Print "next rowNum=" & rowNum '************* Does it still work? Run it again; execution will `stop` ('break') on this line: Stop '******** Drag the yellow marker up to the line after "BEGIN LOOP" and F5 to continue execution from there. End Sub 

Ура, цифры были правильными! Теперь для автоматизации цикла. Для … Неверные счетчики не подходят для этого, так как данные имеют тенденцию к изменению. Вместо этого в конце каждой итерации цикла мы проверим, есть ли данные на 4 строки ниже «rgEnd». (Если это так, «Loop» снова.)

 Sub test2_4() 'Semi-Final Version: Dim rowNum As Integer 'the current row number Dim rgStart As Range 'start of the range for the current section Dim rgEnd As Range 'end of the range for the current section Dim rg As Range 'the entire range for the current section Dim sectionRows As Integer 'count of the rows for the current section rowNum = 34 'get to first section (begin loop AFTER here) Do 'BEGIN LOOP: rowNum = rowNum + 4 'skip header (4 rows) Set rgStart = Range("A" & rowNum) 'start range at A38 Set rgEnd = Range("B" & rowNum).End(xlDown) 'end range at column B, bottom row of this section Set rg = Range(rgStart, rgEnd) 'combine into single range object sectionRows = rg.Rows.Count 'count rows in the range Debug.Print rgStart.Address & " to " & rgEnd.Address & " Row.Count = " & sectionRows 'for now stick with just debug.print'ing RowCount '(a manual count tells me I'm expecting results: 11 -> 9 -> 7 -> 4 -> 2 using my sample dataset) rowNum = rowNum + sectionRows + 3 'skip to top of next section Loop Until rgEnd.Offset(4, 0) = "" 'loop until there ISN'T data 4 rows below rgEnd Debug.Print "Done!" End Sub 

Теперь у нас есть функционирование, как мы и предполагали. Нет причин, по которым он не может оставаться таким, но многие разработчики (включая меня) могут быть особенно полезны в сокращении кода. Это происходит гораздо более сглаженным образом: просто не забывайте поддерживать комментарии как можно больше и не преувеличивайте!

 'Final Version: Sub test2_5_Final() Dim rowNum As Integer, sectionRows As Integer, sectionNum As Integer 'counters: current row, rows in section, section number Dim rgStart As Range, rgEnd As Range, rg As Range 'start/end/complete ranges for "current section" rowNum = 34 'first section's top row Do rowNum = rowNum + 4 'skip header Set rgStart = Range("A" & rowNum) 'section top-left Set rgEnd = Range("B" & rowNum).End(xlDown) 'section bottom-right Set rg = Range(rgStart, rgEnd) 'whole section sectionRows = rg.Rows.Count 'count rows in section sectionNum = sectionNum + 1 'increase count of sections Debug.Print "Section #" & sectionNum & " = " & sectionRows & " rows" 'store the values somewhere else if need be rowNum = rowNum + sectionRows + 3 'skip to top of next section Loop Until rgEnd.Offset(4, 0) = "" 'loop until there's no data 4 rows down Debug.Print "Done!" End Sub 

Наконец, благодаря устранению неполадок и перезаписи я понял проблемы с вашим исходным кодом:

  1. Переменная rcount использовалась как для текущей строки #, так и для подсчета строк в текущем разделе, таким образом чередуя каждый прогон

  2. Правая скобка на неправильном сайте Offset ссылалась на весь диапазон, а не на дно.

    • Свойство Range.Offset (Excel)
    • Объект Range (Excel)

Решение : Исправлена ​​скобка и добавлена ​​переменная. Вышеприведенный код, вероятно, будет более надежным, но просто для него:

 Sub test2_orig_fixed() Dim d As Integer, rcount As Integer, secCount As Integer rcount = 34 For d = 1 To 4 secCount = ActiveSheet.Range(Range("A" & rcount + 4), Range("B" & rcount + 4).End(xlDown).Offset(3, 0)).Rows.Count Debug.Print secCount - 3 rcount = rcount + secCount + 4 Next d End Sub 

Привыкание методического устранения неполадок небольших вопросов, подобных этому примеру, будет полезным только при решении больших проблем в будущем.

Каждый мозг решает логические проблемы по-разному. Так я справляюсь с этим, но есть множество других методов (и есть даже курсы университетского уровня по Теории устранения неполадок.) Кому нужны головоломки, когда у нас есть код!

Давайте будем гением компьютера.