VBA - 如果列B中的单元格不为空，则列A = 1 [英] VBA - If a cell in column B is not blank the column A = 1

问题描述

这是我的情况：

列A为空。
列B是客人的房间号码
列C是该房间中的客人的姓名

Column A is empty. Column B is the room number of a guest Column C is the Name of the guest in that room

我正在计算多少房间被占用所以我把一个计数公式，但结果是0.我不知道为什么..

I am trying to count how many room are occupied. so I put a count formula but the Result is 0. I don't know why..

这是代码：

Sheets("Champagne").Select
Range("B2").AutoFill Destination:=Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
LastRow = Range("A2").End(xlDown).Row
Cells(LastRow + 2, "A").Formula = "=SUM(A2:A" & LastRow & ")"
LRowA = [A4200].End(xlUp).Address
Range("A:A").Interior.ColorIndex = xlNone
Range("A2:" & LRowA).Interior.ColorIndex = 33
Range("A:A").HorizontalAlignment = xlCenter

所以我试图用一个公式来表示列B是否为Number（房间号），它将在列A中算作1。然后放一个A列结尾处的总和。

so then I was trying to put a formula to say if Column B as any Number (the room number), it will count as 1 in the column A. And then put a Sum at the end of Column A.

这是我试图放置的代码，但它将123456放在列C中。

Here is the code that I am trying to put but it puts 123456 in the column C.

Sheets("Champagne").Select

For Each Cel In Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
If Cel.Value <> "" Then Cel.Offset(1, 0).Value = "123456"
Range("A2").AutoFill Destination:=Range("A2:A" & Cells(Rows.Count, 2).End(xlUp).Row)
LastRow = Range("A2").End(xlDown).Row
Next

Range("B2").AutoFill Destination:=Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row)
LastRow = Range("A2").End(xlDown).Row
Cells(LastRow + 2, "A").Formula = "=SUM(A2:A" & LastRow & ")"
LRowA = [A4200].End(xlUp).Address
Range("A:A").Interior.ColorIndex = xlNone
Range("A2:" & LRowA).Interior.ColorIndex = 33
Range("A:A").HorizontalAlignment = xlCenter

如果您有第一个代码的答案，我也采取它....

If you have an answer with the first code, I take it as well....

推荐答案

要计算有多少房间（B列）被占用，我将使用如下代码：

To count how many rooms (column B) are occupied I would use code like:

Function CountOccupiedRooms(sheetname As String) As Long
    Dim j As Long
    dim c As Range
    With Worksheets(sheetname)
        'Check that some data exists
        If IsEmpty(.Range("B2").Value) Then
            CountOccupiedRooms = 0
            Exit Function
        End If

        For Each c In .Range("B2", .Cells(.Rows.Count, "B").End(xlUp))
            If Application.WorksheetFunction.CountIf(.Range("B2:B" & c.Row), c.Value) = 1 Then
                j = j + 1
            End If
        Next
    End With
    CountOccupiedRooms = j
End Function

然后，假设你想把这个数字放在一个单元格的某个地方，该代码可以被调用您的主要代码为

and then, assuming you wanted to put that number in a cell somewhere, that code could be called in your main code as

Worksheets("Summary").Range("C5").Value = CountOccupiedRooms("Champagne")
Worksheets("Summary").Range("C6").Value = CountOccupiedRooms("ChocoStrawb")

目的地工作表名称（摘要）和位置（C5和C6）仅仅是为了说明的目的 - 您可以使用任何你喜欢的。

The destination worksheet name ("Summary") and the locations ("C5" and "C6") were just made up for illustration purposes - you can use whatever you like.

这篇关于VBA - 如果列B中的单元格不为空，则列A = 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！