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, Jan 06 '17 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, Jan 06 '17 at 17:46
what is the value of `FreeFile` ? since `intFile = FreeFile` and later you check if `ElseIf Cells(intFile, "CU") = "0" Then` — Shai Rado, Jan 06 '17 at 17:47
@YowE3K seems to be right. Most likely you are trying to use Excel's Cells function without a reference to Excel. — cyboashu, Jan 06 '17 at 17:48
Wait - I recognise the line that says `If CDate(rst(3)) >= Date Then`! This is Access - I answered a question that recommended that line be entered. — YowE3K, Jan 06 '17 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, Jan 06 '17 at 17:49
Possible duplicate of [How to refer to Excel objects in Access VBA?](http://stackoverflow.com/questions/5729195/how-to-refer-to-excel-objects-in-access-vba) — cyboashu, Jan 06 '17 at 17:52
If you are trying to not write the record if `rst(98) = 0` then modify the previous line to be `If CDate(rst(3)) >= Date And rst(98) <> 0 Then` — YowE3K, Jan 06 '17 at 17:52
@YowE3K will try that now! and please create an answer so that i can approve it! :) — John Tipton, Jan 06 '17 at 17:53
@JohnTipton That is not going to solve the issue. Your problem is that you are referencing excel objects but have not created an Excel Application and workbook object. You then try and reference Sheets and Cells. Are you Familiar with adding in a reference? — Sorceri, Jan 06 '17 at 17:57
@Sorceri I see what you are talking about. yes I do. Thank you for your input! :) — John Tipton, Jan 06 '17 at 17:58
@Sorceri - John isn't trying (intentionally) to reference Excel objects - he's just trying to not write out certain records to the csv file he is creating, but has used Excel functionality to do it instead of Access functionality. The lines referencing `Cells` and `Sheet1` are new lines that have been added, which should have just been done using an amendment to the `If` statement preceding it. — YowE3K, Jan 06 '17 at 18:04
@YowE3K thanks! and I haven't not tested yet.. in the middle of making a fake file to test it out. as we don't have a back order yet. thank you in advance! please leave an answer below! — John Tipton, Jan 06 '17 at 18:10
"back order" - your previous code was excluding any records where the date was prior to today. Your new code will exclude all of those, and **also** exclude any others where field 98 is zero. Is that what you want? Or are you trying to **not** exclude some of the ones where the date is prior to today? — YowE3K, Jan 06 '17 at 18:14
@YowE3K Correct, for the former! For now :) Thanks for a very good question :) You are truly amazing! — John Tipton, Jan 06 '17 at 18:34

YowE3K · Accepted Answer · 2017-01-06T18:25:46.590

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.

vba- If else statement within a for loop

1 Answers1