Использование оператора select-case с ячейками в диапазонах
Моя идея состоит в том, чтобы заполнить ячейки в столбце H в зависимости от того, что содержит соответствующая ячейка в диапазоне A: A, D: D, F: F.
Я получаю ошибку времени выполнения 13 (несоответствие типов) в строке Case "Done", хотя я не уверен, почему, так как и выбранный диапазон, и ввод переменной являются строками. Я всегда использовал циклы if, это первый раз, когда я использую select case, но, несмотря на то, что я прочитал ссылку, я все еще не знаю, что я делаю неправильно.
Второй вопрос заключается в том, как определить последнюю заполненную строку диапазона как конец нового диапазона. Прямо сейчас с помощью newRange.Value я присваиваю значение всему столбцу, но я пытаюсь убедиться, что оно применяется только к соответствующей ячейке.
(Для пояснения, если, например, ячейка A3 содержит значение, это означает, что ячейки D3 и F3 пусты, поэтому каждая строка в диапазоне A:A,D:D,F:F содержит только одно значение.)
Sub setStatus()
Dim dataRange As Range
Dim newRange As Range
Set dataRange = Range("A:A,D:D,F:F")
Set newRange = Range("H:H")
Select Case dataRange.Value
Case "Done"
newRange.Value = "Completed"
Case "WIP"
newRange.Value = "In Progress"
'In reality there are many different cases,
'hence the select case instead of an if loop
End Select
Next
End Sub
Может, попробовать это? : заменить "Готово" на ИСТИНА. Получите диапазон ячеек со значением TRUE (специальная ячейка, логика), сместите его на H, заполните значением «Completed», замените обратно TRUE на «Done». Используя Select-Case, я думаю, вам все равно нужно сделать цикл. Я думаю, что Select-Case не должен получать все ячейки, которые имеют значение в упомянутом случае.
Не могли бы вы поделиться скриншотом вашего рабочего листа с видимыми заголовками строк и столбцов? Только вершина этого.
Ответы 1
Application.Match Применяется к массиву вместо Select Case
Sub SetStatus()
' Constants
Const SOURCE_FIRST_ROW As Long = 2
Const DESTINATION_COLUMN As Long = 8
' Arrays
Dim sCols() As Variant: sCols = VBA.Array(1, 4, 6) ' only one column has data
Dim Cases() As Variant: Cases = VBA.Array( _
"Done", "WIP")
Dim Values() As Variant: Values = VBA.Array( _
"Completed", "In Progress")
' Worksheet
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Write the values from the source range to an array.
Dim srg As Range: Set srg = ws.UsedRange
Dim rOffset As Long: rOffset = SOURCE_FIRST_ROW - 1
Dim rCount As Long: rCount = srg.Rows.Count - rOffset
Set srg = srg.Resize(rCount).Offset(rOffset)
Dim Data As Variant: Data = srg.Value
Dim cUpper As Long: cUpper = UBound(sCols)
' Write the matching results to the 1st column of the array.
Dim r As Long
Dim c As Long
Dim cString As String
Dim cIndex As Variant
Dim HasDataInRow As Boolean
For r = 1 To rCount ' rows of the array
For c = 0 To cUpper ' given columns of the array
cString = CStr(Data(r, sCols(c)))
If Len(cString) > 0 Then
cIndex = Application.Match(cString, Cases, 0)
If IsNumeric(cIndex) Then
Data(r, 1) = Values(cIndex - 1) ' found in Cases
Else
Data(r, 1) = Empty ' not found in Cases
End If
HasDataInRow = True
Exit For
'Else ' is blank; do nothing
End If
Next c
If HasDataInRow Then
HasDataInRow = False
Else
Data(r, 1) = Empty ' the row was blank
End If
Next r
' Write the values from the first column of the array
' to the destination range.
Dim drg As Range: Set drg = srg.Columns(DESTINATION_COLUMN)
drg.Value = Data
MsgBox "Status set.", vbInformation
End Sub
Спасибо, работает отлично! Я пытаюсь понять весь код, но у меня возникли проблемы с этим фрагментом: cIndex = Application.Match(cString, Cases, 0) If IsNumeric(cIndex) Then Data(r, 1) = Values(cIndex - 1) Если я правильно понял, cIndex возвращает относительные положения ячеек в строке r и столбце c (из массива sCols), которые соответствуют любому элементов массива Cases[]. Почему второй аргумент в Data() является константой 1? Разве тогда не будет действительным только первый столбец массива sCols?
Я использую тот же массив и перезаписываю первый столбец результатами. В последней строке я записываю только этот результирующий столбец в диапазон из одного столбца (Set drg = srg.Columns(DESTINATION_COLUMN)), т.е. когда у вас слишком большой массив, только количество первых (самых левых) столбцов равно количеству столбцов массива. диапазон будет скопирован. То же самое касается рядов (конечно, самых верхних), но в данном случае они равны.
Значение, которое вы получаете из datarange.value, представляет собой массив (Variant (вариант (от 1 до 1048576, от 1 до 1)). Select case может принимать только отдельные значения. Вам нужно будет инкапсулировать оператор case в цикле For Each, который перечисляет значения в dataRange.Value. Возможно, вы захотите пересмотреть выбранный вами диапазон, поскольку я подозреваю, что вы действительно не хотите обрабатывать значения 1048576.