Loop through Excel Column

[Trancedified]

Hello,

Is there a way to loop through an Excel Column using VB.NET? What im trying to do is something like:

If Column("Date") = Date.Now() Then Todays date
Write data to Column("# of Accounts") on the same row

End If

Any ideas?

Chris

 

[Mike_R]

I am going to assume that you have two Named Ranges that named "Date" and "Num_Accounts" and both consist of a single column each.

In thas case, your code could look something like this:

Dim i As Long

With xlApp.Range("Date")
    For i = 1 To .Count
        If .Item(i).Value = Date.Now() Then
            xlApp.Range("Num_Accounts").Item(i).Value = "SomeData"
        End If
    Next i
End With

Of course what the "SomeData" is, is up to you.

-- Mike

 

[Trancedified]

Wow that seems to be what Im looking for, code wise, but how do assign a Named Range through code (Im creating a brand-new Excel file) any parameters?
Thanks for posting!
Chris

 

[Mike_R]

Oh, ok... your use of quotes to designate Ranges looks like you were using Named Ranges. I didnt realize that it was pseudo-code! (Ok, well, I realized a little, but it still looked like a Named Range.)

If the whole thing is to run from Code, then there is no reason to use a Named Range unless you want the User to be able to see these designations or otherwise use these names on the "Excel side" of things, such as in a Worksheet Function. But since it sounds like your whole process is to be on the VBA side of things, then its cleaner to maintain a Range variable in your code.

Something like this:

Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet
Dim DateRng As Excel.Range
Dim AccountsRng As Excel.Range
Dim i As Long

WB = xlApp.Workbooks.Add
WS = WB.Worksheets("Sheet1")

DateRng = WS.Columns(2)      Change the 2 as necessary.
AccountsRng = WS.Columns(3)  Change the 3 as necessary.

With DateRng
    For i = 1 To .Count
        If .Item(i).Value = Date.Now() Then
            AccountsRng.Item(i).Value = ######   <-- "SomeData"
        End If
    Next i
End With

Mind you, I didnt know which column had the Dates nor which had the Number of Accounts, and not a clue what your "SomeData" was, but I think you can fill that in...

-- Mike

 

[Trancedified]

Mike,

I tried the code, but now that I look at it, this code is creating a new Excel file, while Im trying to add to an existing one..... so this line of code returns this error:

An unhandled exception of type System.Runtime.InteropServices.COMException occurred in mscorlib.dll

Additional information: Bad variable type

In this section:

        With WB.Worksheets("Sheet1")
            With DateRng
                For i = 1 To .Count
                    If .Item(i).Value = Date.Now Then <------- HERE
                        AccountsRng.Item(i).Value = "######"  
                    End If
                Next i
            End With
        End With

Couldnt we replace

        WB = xlApp.Workbooks.Add
        WS = WB.Worksheets("Sheet1")

with

        WB = xlApp.Workbooks.Open("C:\Documents and Settings\CChang\My Documents\blah.xls")

Any ideas?
Thanks!

Chris

 

[Mike_R]

Oh, absolutely! You really didnt show much code in Post #1, so I did not know what to assume about your data. So, yes, my code is creating a New Workbook from scratch... However, if you have a Workbook or Template already made, then by all means, call WB = Workbooks.Open("..") and then proceed from there...

I do believe that you should still keep the Worksheet designation with:

WS = WB.Worksheets("Sheet1")

I just think that it will help keep things clearer.

,
Mike

 

[Mike_R]

Oh, my bad, I missed that you also had a problem with:

If .Item(i).Value = Date.Now Then

Hmm... That looks fine to me. It could be a Coercion issue though, I guess. I would try:

If CDate(.Item(i).Value) = Date.Now Then

You should also strongly consider using Option Strict On. This will force you to use CType() at a number of locations which will help straighten out these Casting and Coercion issues at Compile Time instead of failing at Run Time.

The good news is that the IDE will tell you exactly which lines need a CType() added, so you really dont really have to worry about it. For example, within your code I am certain that the IDE/Compiler will tell you that the following line:

With WB.Worksheets("Sheet1")

will need a CType() added. What you need to do in this case is force the Cast to a Worksheet object by changing the above line to:

With CType(WB.Worksheets("Sheet1"), Excel.Worksheet)

I know that this looks positively silly, for the Worksheet Collection should return only a Worksheet object anyway, but unfortunately this is not 100% true. The Worksheets collection is actually polymorphic and can return an Excel 4.0 Macro sheet. So, technically, the compiler does not know which object type you are referring to at Compile time. Using CType() tells it. And using Option Strict On at the top of your Module enforces that CType() will be required any time there is an ambiguity.

I do not know for sure if Option Strict On would flag down your line:

If .Item(i).Value = Date.Now Then

but I suspect that it would. The more of these things that you can track down at Compile Time instead of at Run Time, the better...

-- Mike

 

[Trancedified]

Mike,

Right on just what I was looking for! Thanks for the advice!

Chris

 

[Mike_R]

No problem
Good luck out there..!