有一个更好的方法吗?VBA脚本 [英] Is there a better way to do this? VBA script

问题描述

我在这里有一个跟踪列表，用于给水泵.我们必须考虑库存水平，因此我做到了这一点.当您将泵输入表格时，什么也没有发生.当您在上面加上患者姓名时，该行会变成粉红色，表示泵已离开我们的库存.

我试图将一个脚本/宏放在一起，以便对我们仍然拥有的泵(即白行，e列)进行计数，并向用户显示列表(表将来会很长)./p>

我的代码:

它在每个"cll"的c列(序列号)中循环，一条if语句检查右侧第2列的单元格中是否有任何内容(患者姓名，如果有患者姓名，则表示泵已发出)，以及e列中是否有值(序列号).它在一系列消息框中显示满足条件的序列号.

该代码有效，我很高兴将其推出，因为这不是一个过于重要的问题，我将把它留给一群技术恐惧症患者.但是，我想知道这有点笨重吗?

我不喜欢单独的消息框，但是我找不到在excel VBA中创建数组的任何信息，而且我不喜欢使用offset属性检查IF.

我无法检查cll.Interior.color/colorIndex是否有效，因为excel似乎认为这些单元格都是相同的颜色，而不管它们实际上是什么颜色.(?导致问题的条件格式).

希望这很有意义，

代码为文本:

 私人子CommandButton1_Click()昏暗的cll作为范围对于范围中的每个cll("c6:c200")如果IsEmpty(cll.Offset(0，2))并且cll.Value>0然后MsgBox泵序列号:"&vbNewLine&vbNewLine&值万一下一个cll结束子 

解决方案

1. 在循环中连接序列号，然后在循环后显示连接的字符串.
2. 使用值查找最后一个单元格，并仅循环到该行.
3. 迭代一个变量数组而不是范围，它更快

---

  Private Sub CommandButton1_Click()昏暗的cll作为变体cll = ActiveSheet.Range("C6"，ActiveSheet.Cells(Rows.Count，3).End(xlUp).Offset(，2)).ValueDim str As Stringstr ="Dim delim As Stringdelim ="昏暗的我只要对于i = 1到UBound(cll，1)如果不是IsEmpty(cll(i，1))和IsEmpty(cll(i，3))然后str = str&德林&cll(i，1)如果delim ="然后delim = vbNewLine万一接下来我MsgBox泵序列号:"&vbNewLine&vbNewLine&力量结束子 

What I have here is a tracking list, for feeding pumps. We have to account for stock levels, so I made this. When you enter a pump, into the table, nothing happens. When you put a patient's name against it, the row goes pink to indicate the pump has left our stock.

I was trying to get a script/macro together that could count the pumps that we still had (i.e the white rows, e column), and display the list (table will get quite long in future) to the user.

My code:

It loops through the c column (serial numbers) for each "cll", an if statement checks if there is anything in the cell 2 columns to the right (patient name, if there is a patient name, it means the pump has been given out) AND if there is a value in the e column (serial numbers). It displays serial numbers that fulfill the criteria in a series of message boxes.

The code works, and I'm happy to roll it out, as this isn't an overly important issue, and I'll be leaving it with a group of technophobes. However, I'm wondering, it's a little clunky?

I don't like the seperate message boxes, but I can't find any info on making an array in excel VBA, and I don't like checking the IF using the offset property.

I couldn't make checking the cll.Interior.color/colorIndex work, as excel seemed to think the cells are all the same color, regardless of what color they actually are. (?conditional formatting causing issues).

Hope this makes sense,

EDIT:

Code as text:

    Private Sub CommandButton1_Click()

Dim cll As Range


For Each cll In Range("c6:c200")
If IsEmpty(cll.Offset(0, 2)) And cll.Value > 0 Then
MsgBox "Pump Serial number: " & vbNewLine & vbNewLine & cll.Value
End If


Next cll


End Sub

解决方案

1. concatenate the serial numbers in the loop and then after the loop show the concatenated string.
2. find the last cell with a value and only loop to that row.
3. Iterate a variant array instead of the range, it is quicker

---

Private Sub CommandButton1_Click()
    Dim cll As Variant
    cll = ActiveSheet.Range("C6", ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Offset(, 2)).Value
    
    Dim str As String
    str = ""
    
    Dim delim As String
    delim = ""
    
    Dim i As Long
    For i = 1 To UBound(cll, 1)
        If Not IsEmpty(cll(i, 1)) And IsEmpty(cll(i, 3)) Then
          str = str & delim & cll(i, 1)
          If delim = "" Then delim = vbNewLine
        End If
    Next i
    
    MsgBox "Pump Serial number: " & vbNewLine & vbNewLine & str
End Sub

这篇关于有一个更好的方法吗?VBA脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！