vba 对于工作表中的每个单元格（“xyz”）查询

For each cell in worksheets("xyz") query

提问by s0up2up

Idea behind the code is that it is supposed to find customer names, then find the level of sales to them throughout the year then paste all this data into another sheet.

代码背后的想法是它应该找到客户名称，然后找到他们全年的销售水平，然后将所有这些数据粘贴到另一个表中。

Getting a Run-Time error '1004' Application-defined or object-defined error from the following line. I have asterisked the line where I am getting the error.

从以下行获取运行时错误“1004”应用程序定义或对象定义的错误。我在出现错误的那一行加了星号。

Sub Import_CustomerData()

Dim strMonth As String
Dim rngMonth As Range

Dim DataImportColum As Integer
Dim DataImportRow As Integer

Dim strFirstCustomer As String
Dim strSecondCustomer As String
Dim strThirdCustomer As String
Dim strFourthCustomer As String
Dim strFifthCustomer As String

Dim lngFirstCustomerSales As Long
Dim lngSecondCustomerSales As Long
Dim lngThirdCustomerSales As Long
Dim lngFourthCustomerSales As Long
Dim lngFifthCustomerSales As Long
Dim lngTotalSales As Long

Dim cell As Range
Dim x As Integer

'Finding Data for clients
For Each cell In Worksheets("Data entry").Range("A1:A99")

If cell.Value = "Customer Sales" Then

    strFirstCustomer = cell.Offset(1, 0).Value
    strSecondCustomer = cell.Offset(2, 0).Value
    strThirdCustomer = cell.Offset(3, 0).Value
    strFourthCustomer = cell.Offset(4, 0).Value
    strFifthCustomer = cell.Offset(5, 0).Value

End If

Next

'Extracting Data from Customer sheet

***For Each cell In Worksheets("Client_Customer").Range("B83:86")***

'First Customer
If cell.Value = strFirstCustomer Then
    lngFirstCustomerSales = Val(cell.Offset(0, 1))
End If

'Second Customer
If cell.Value = strSecondCustomer Then
    lngSecondCustomerSales = Val(cell.Offset(0, 1))
End If

'Third Customer
If cell.Value = strThirdCustomer Then
    lngThirdCustomerSales = Val(cell.Offset(0, 1))
End If

'Fourth Customer
If cell.Value = strFourthCustomer Then
    lngFourthCustomerSales = Val(cell.Offset(0, 1))
End If

'Fifth Customer
If cell.Value = gxdfg Then
    lngFifthCustomerSales = Val(cell.Offset(0, 1))
End If

'Total Customers Sales
If cell.Value = "Total:" Then
    lngTotalSales = Val(cell.Offset(0, 1))
End If

Next

'Importing it into Data Customer Monthly 2013 sheet.

'Determing month of client system reports
    strMonth = Sheets("Client_Customer").Range("B8").Value

If strMonth = "" Then

    frmEnter_month.Show

Else

    iLenMonth = Len(strMonth)
       x = iLenMonth - 5
            strLeftMonth = Left(strMonth, x)

End If

'To find Column of Customer imput
For Each cell In Range("B4:M4")

        If cell.Value = strLeftMonth Then
            DataImportColumn = cell.Column

        End If

Next

For Each cell In Worksheets("data customer monthly 2013").Range("A3:A9999")

'First Customer
If cell.Value = strFirstCustomer Then
        DataImportRow = cell.Row

** 2 ** lngFirstCustomerSales = Cells(DataImportRow, DataImportColumn).Offset(0, 2).Value ** 2 **
End If

'Second Customer
If cell.Value = strSecondCustomer Then
        DataImportRow = cell.Row

        lngSecondCustomerSales = Cells(DataImportRow, DataImportColumn).Offset(0, 2).Value
 End If

'Third Customer
If cell.Value = strThirdCustomer Then
        DataImportRow = cell.Row

         lngThirdCustomerSales = Cells(DataImportRow, DataImportColumn).Offset(0, 2).Value
 End If

'Fourth customer
If cell.Value = strFourthCustomer Then
        DataImportRow = cell.Row

    lngFourthCustomerSales = Cells(DataImportRow, DataImportColumn).Offset(0, 2).Value 
 End If

'Fifth Customer
If cell.Value = strFifthCustomer Then
        DataImportRow = cell.Row

    lngFifthCustomerSales = Cells(DataImportRow, DataImportColumn).Offset(0, 2).Value
 End If

'Total Sales
If cell.Value = "Total Sales" Then
        DataImportRow = cell.Row

    lngTotalSales = Cells(48, DataImportColumn).Value 
End If

Next

DeleteClientSheets

End Sub

Sorry for the large amount of code but does anyone have any suggestions? Couldn't find anything else that help explain the question as cell has been defined as a range.

抱歉有大量代码，但有人有任何建议吗？由于单元格已定义为范围，因此找不到任何其他有助于解释问题的内容。

EDIT1:

编辑1：

Second question: After Silenxor's brilliant solution, I am getting code on the line with the following indicator: ** 2 **

第二个问题：在 Silenxor 出色的解决方案之后，我得到了带有以下指标的代码：** 2 **

The error I am getting is the same as the first error.

我得到的错误与第一个错误相同。

回答by Silenxor

With regards to your asterix line

关于您的 asterix 系列

For Each cell In Worksheets("Client_Customer").Range("B83:86")

Try

尝试

For Each cell In Worksheets("Client_Customer").Range("B83:B86")