找到你要的答案

Q:VBA Error 1004 in function

Q:VBA错误1004功能

I'm trying to write a function which returns the value of a specific cell located on the same column as the one I give in argument, (lig= row number, col=column number), but everytime I run it, I get an error '1004", here's my code:

Function week(lig As Integer, col As Integer) As Integer
    Dim i As Integer
    i = 0
    Do Until Cells(lig - i, 1) = "Row labels"
        i = i + 1
    Loop
    week = Cells(lig - i, col)
End Function

The line in which the error appears is :

 Do Until Cells(lig - i, 1) = "Row labels"

I know that I test the values of cells containing integers before getting to this one, I suspeect a type error, but I can't fix it. Could anyone please help ?

我想写一个函数,它返回一个特定的细胞位于柱作为一个我在争论同样的价值,(LIG =行数、列数、列=)但每次我运行它,我得到一个错误的“1004”,这是我的代码:

Function week(lig As Integer, col As Integer) As Integer
    Dim i As Integer
    i = 0
    Do Until Cells(lig - i, 1) = "Row labels"
        i = i + 1
    Loop
    week = Cells(lig - i, col)
End Function

出现错误的行是:

 Do Until Cells(lig - i, 1) = "Row labels"

I know that I test the values of cells containing integers before getting to this one, I suspeect a type error, but I can't fix it. Could anyone please help ?

answer1: 回答1:

i=0 will cause an error as the cells(0,1) does not exist in the sheet. You may also want an exit clause for if your logic is never found as you will get an error when you hit the end of the sheet. if you pass this lig =1 you will also get an error (as lig - i (1-1) would result in 0)so you may also want to handle that scenario

Function week(lig As Integer, col As Integer) As Integer
    Dim i As Integer
    i = 1'Changed to 1
    Do Until Cells(lig - i, 1) = "Row labels"
        i = i + 1
        if i > 1000000 then exit do 'Exit clause
    Loop
    if i < 1000000 then 
        week = Cells(lig - i, col)
    else
        week = 0'Handle 0's in calling code
    end if
End Function

我= 0将作为细胞引起误差(0,1)中不存在的表。您也可能需要一个退出子句,如果您的逻辑始终没有找到,因为您将得到一个错误,当您击中结束的表。如果你把这光= 1你还将得到一个错误(如韧带-我(1-1)会导致0)所以你可能还需要处理的情况

Function week(lig As Integer, col As Integer) As Integer
    Dim i As Integer
    i = 1'Changed to 1
    Do Until Cells(lig - i, 1) = "Row labels"
        i = i + 1
        if i > 1000000 then exit do 'Exit clause
    Loop
    if i < 1000000 then 
        week = Cells(lig - i, col)
    else
        week = 0'Handle 0's in calling code
    end if
End Function
answer2: 回答2:

The error isn't a type error. The problem is that you are trying to access a cell that doesn't exist. Your loop evidently fails to reach a cell that holds the value "Row labels" and eventually tries to access Cells(0,1) -- which triggers error 1004. As to why this is happening -- you haven't provided enough details to say.

错误不是类型错误。问题是你试图访问一个不存在的单元格。你的循环显然未能达到细胞持有价值“行标签”并最终试图访问细胞(0,1),从而引发错误1004。至于为什么会这样,你还没有提供足够的细节来说明。

answer3: 回答3:

I suspect that the value in the cell is actually "Row Labels" or "ROW LABELS" or Row labels " or something else that doesn't actually match exactly. Try this:

Do Until Trim(Ucase(Cells(lig - i, 1))) = "ROW LABELS" 

Or if you simply want to stop at row one use this:

Do Until lig - i = 1

我怀疑单元格中的值实际上是“行标签”或“行标签”或“行标签”或其他实际上不匹配的值。试试这个:

Do Until Trim(Ucase(Cells(lig - i, 1))) = "ROW LABELS" 

或者如果你只是想停在行一:

Do Until lig - i = 1
answer4: 回答4:

You might like to consider re-writing as follows, which I think is clearer.

Function week(lig As Integer, col As Integer) As Integer

    Dim i As Integer

    ' Thsi function will return 0 if no row with the text is found
    week = 0

    For i = lig To 1 Step -1

        If Cells(i, 1) = "Row labels" Then
            week = Cells(i, col)
            Exit For
        End If

    Next i


End Function

' EVEN BETTER USE THIS (or similar)!

Function week(MyCell As Range) As Integer

    Dim i As Integer

    week = 0

    For i = MyCell.Row To 1 Step -1

        If MyCell.Parent.Cells(i, 1) = "Row labels" Then
            week = MyCell.Parent.Cells(i, MyCell.Column)
            Exit For
        End If

        ' Note
        ' MyCell.Parent. returns the sheet containing the cell
        ' Just using Cells(i, 1) (wihtout preceeding with "MyCell.Parent." will pick up
        ' cells on the currently active sheet, which may not be the sheet with the cell!
        '

    Next i


End Function

您可能会考虑重新写如下,我认为是更清楚。

Function week(lig As Integer, col As Integer) As Integer

    Dim i As Integer

    ' Thsi function will return 0 if no row with the text is found
    week = 0

    For i = lig To 1 Step -1

        If Cells(i, 1) = "Row labels" Then
            week = Cells(i, col)
            Exit For
        End If

    Next i


End Function

' EVEN BETTER USE THIS (or similar)!

Function week(MyCell As Range) As Integer

    Dim i As Integer

    week = 0

    For i = MyCell.Row To 1 Step -1

        If MyCell.Parent.Cells(i, 1) = "Row labels" Then
            week = MyCell.Parent.Cells(i, MyCell.Column)
            Exit For
        End If

        ' Note
        ' MyCell.Parent. returns the sheet containing the cell
        ' Just using Cells(i, 1) (wihtout preceeding with "MyCell.Parent." will pick up
        ' cells on the currently active sheet, which may not be the sheet with the cell!
        '

    Next i


End Function
vba  types