VBA申报阵列和创建列 [英] VBA Filing Arrays and creating columns

问题描述

我需要编写捕获两个数组现有列表，然后创建客户姓名的两个新的阵列和投入金额为客户谁花了至少$ 500分。经过这些新的阵列已经排满，我得把它们写在列D和E。

I need to write a sub that captures the existing lists in two arrays and then create two new arrays of customer names and amounts spent for customers who spent at least $500. After these new arrays have been filled, I have to write them in columns D and E.

所以说，第1列从A3：A50，这有客户的姓名
第2栏是从C3：50的销售价格，顾客购买

So say column 1 is from A3:A50 and this has the customer's names and column 2 is from C3:50 with the sales price that the customer purchase.

我无法写code，它通过排序数组，并决定如果销售价格高于$ 500的一部分。有人能指出我在哪里，它的问题呢？

I am having trouble writing the part of the code that sorts through the arrays and decides if the sales price is greater than $500. Can someone point me where its going wrong?

这是我有这么远，但它不工作：

This is what I have so far but it does not work:

Sub ProductSales()
' These are inputs: the number of customers, the customer's name,
' and the dollar amount of each sale.
Dim nCustomers As Integer
Dim namesData() As String
Dim dollarsData() As Integer

' The following are outputs: the customer name found over 500, and the number
'of customer over 500
Dim customerFound() As String
Dim customerCount() As Integer

' Variables used in finding if sale is over 500
Dim isOver As Boolean
Dim nFound As Integer

' Counters.
Dim i As Integer
Dim j As Integer

' Clear any old results in columns E to G.
With wsData.Range("E2")
    Range(.Offset(1, 0), .Offset(0, 2).End(xlDown)).ClearContents
End With

' Find number of customers in the data set, redimension the namesdata and
' dollarsData arrays, and fill them with the data in columns A and C.
With wsData.Range("A2")
    nCustomers = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
    ReDim namesData(1 To nCustomers)
    ReDim dollarsData(1 To nCustomers)
    For i = 1 To nCustomers
        namesData(i) = .Offset(i, 0).Value
        dollarsData(i) = .Offset(i, 2).Value
    Next
End With

' Initialize the number of names found to 0.
nFound = 0

' Loop through all sales.
For i = 1 To nCustomers

    ' Set the Boolean isOver to False, and change it to True only
    ' if the sale is over 500
    isOver = False
    If nFound > 0 Then
        ' Loop through all customer names already found and add to new list
        ' and exit loop
        For j = 1 To nFound
            If dollarsData(i) > 500 Then
                isOver = True
                customerCount(j) = customerCount(j) + 1
                Exit For
            End If
        Next
    End If

    If isOver Then
        ' The current product code is a new one, so update the list of
        ' codes found so far, and initialize the transactionsCount and dollarsTotal
        ' values for this new product.
        nFound = nFound + 1
        ReDim Preserve customerFound(1 To nFound)
        ReDim Preserve customerCount(1 To nFound)
        customerCount(nFound) = namesData(i)
        customerCount(nFound) = 1

    End If
Next

' Place the results in columns E to G.
For j = 1 To nFound
    With wsData.Range("E2")
        .Offset(j, 0).Value = customerFound(j)
        .Offset(j, 1).Value = customerCount(j)

    End With
Next

结束小组

推荐答案

Excel VBA中有一行写范围到一个数组的能力极强。这是非常快速，省去了写他/她自己的迭代code作为你已经完成了开发。数组被声明为变和语法是：

Excel VBA has a great capability of writing a Range to an array in one line. It's extremely quick and saves the developer having to write his/her own iteration code as you have done. Array is declared as a Variant and syntax is:

readArray = Range("A3:A50").Value2

这同样适用于所述阵列写入的片材。语法是：

The same applies to writing the array to your sheet. Syntax is:

 Range("A3:A50").Value = writeArray

所以在你的项目的一部分，你只需要阅读的两列。循环通过他们找到你的目标的项目，然后填充你的输出数组。你需要标注输出数组所以在这个例子中，我使用了收藏存储一个找到的项目的各项指标和尺寸仅仅是 Collection.Count 。

So in this part of your project, you'd simply need to read the two columns. Loop through them to find your target items and then populate your output array. You do need to dimension the output array so in this example I've used a Collection which stores each index of a found item and the sizing is simply Collection.Count.

下面硬codeS样品的尺寸范围内，但它应该给你如何简化自己的code一个想法：

The sample below hard-codes your range dimension but it should give you an idea of how to simplify your own code:

Dim ws As Worksheet
Dim namesData As Variant
Dim dollarsData As Variant
Dim output() As Variant
Dim foundIndexes As Collection
Dim i As Long
Dim v As Variant

'Set the worksheet object
Set ws = ThisWorkbook.Worksheets("Sheet1") 'change to your sheet name

'Read the data
With ws.Range("A3:A50")
    namesData = .Value2
    dollarsData = .Offset(, 2).Value2
End With

'Find the target customers
Set foundIndexes = New Collection
For i = 1 To UBound(dollarsData, 1)
    If dollarsData(i, 1) > 500 Then
        foundIndexes.Add i
    End If
Next

'Size the output array
ReDim output(1 To foundIndexes.Count, 1 To 2)

'Populate the output array
i = 1
For Each v In foundIndexes
    output(i, 1) = namesData(v, 1)
    output(i, 2) = dollarsData(v, 1)
    i = i + 1
Next

'Write array to sheet
ws.Range("D3").Resize(UBound(output, 1), UBound(output, 2)).Value = output

这篇关于VBA申报阵列和创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！