vba- If else statement within a for loop

Question

I'd like to add the following that deletes row that contains 0

    If Cells(intFile, "CU") = "0" Then           
        Sheet1.Rows(r).EntireRow.Delete    
    End If

to my code below.

   If CDate(rst(3)) >= Date Then

       For intCount = 0 To rst.Fields.Count - 1
           strHold = strHold & rst(intCount).Value & "|"

Currently on 'Cells' ElseIf Cells(intFile, "CU") = "0" Then I'm getting compile error: sub or function not defined. what should i put instead of Cells?

My reference: https://msdn.microsoft.com/en-us/library/752y8abs.aspx

Function invocations are analyzed along with the passed arguments (search for overloading concept). I guess that you are calling the right function with the wrong set of arguments. — FDavidov
– FDavidov, Commented Jan 6, 2017 at 17:46
Is this [access-vba] or [excel-vba]? You are using CurrentDB which is MSAccess VBA, and Cells which is Excel VBA. — YowE3K
– YowE3K, Commented Jan 6, 2017 at 17:46
@YowE3K seems to be right. Most likely you are trying to use Excel's Cells function without a reference to Excel. — cyboashu
– cyboashu, Commented Jan 6, 2017 at 17:48
@YowE3K you can tell it is access due to the Option Compare Database statement at the top, User needs to add the reference then fully qualify the Excel Object - Also it looks like the user is opening a csv file but not using Excel to do so but openingit as a text file: Open strFilePath For Output As #intFile — Sorceri
– Sorceri, Commented Jan 6, 2017 at 17:49
Possible duplicate of How to refer to Excel objects in Access VBA? — cyboashu
– cyboashu, Commented Jan 6, 2017 at 17:52

YowE3K · Accepted Answer · 2017-01-06 18:25:46Z

The issue here is that there are many "flavours" of Visual Basic, and they aren't interchangeable.

Your application is running in MSAccess, and it will have certain objects, functions, etc, that are specifically applicable to that environment. The Cells object is something that is specifically applicable to the MS Excel environment and therefore Access VBA has no understanding of what a Cells object is.

(And the documentation you referred to is regarding the syntax for VB.Net - but the specific topic you were looking at is treated the same in all these different versions of "Visual Basic". But it does appear from that web page that the Then in an If statement is an optional keyword in VB.Net, while it is required in VBA, so there's one difference on even such an elementary piece of coding.)

If you change your code from

If CDate(rst(3)) >= Date Then
ElseIf Cells(intFile, "CU") = "0" Then
    Sheet1.Rows(r).EntireRow.Delete

to be

If CDate(rst(3)) >= Date And rst(98) <> 0 Then

I believe you will be doing what you set out to do, i.e. exclude all records where the 4th field (3 due to zero-base) is before today and also exclude all records where the 99th field is 0.

Collectives™ on Stack Overflow

vba- If else statement within a for loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related