Newbie needs VBA help

[as mellow as a horse]

I'm not a newbie programmer (I usually use C++ or Java), but I am new to
VBA.

I'm trying to write a simple VBA procedure to sum the values in an Excel
worksheet column, but only if the corresponding cell in the next column is
non-zero. The logic of how to do this is not very hard, but VBA is bitching
at me about a type mismatch error. The code I've written is:

Dim rwIndex As Integer
Dim auctionProfit As Currency
Dim currentVal As Currency

auctionProfit = 0
For rwIndex = 5 To 54 ' hard-coded row values will do for now....
If Cells(rwIndex, 10) > 0 Then ' if adjacent cell in next row non-zero
currentVal = Cells(rwIndex, 9).Value
If currentVal > 0 Then auctionProfit = auctionProfit + currentVal
End If
Next rwIndex

MsgBox ("Total Completed Auction Profits = " & CStr(auctionProfit))

It's the 'Cells(rwIndex, .....' bit that VBA doesn't like. If I hard-code a
valid value it'll run OK (but will count one row 50 times of course!).

I tried making rwIndex a variant, but it still doesn't work. What data-type
is used by Cells?

 

[Flybean]

You can try to use EXCEL's Macro recorder. I often does this.

 

[George Nicholson]

The Cells property requires a object: Worksheets("Sheet 1").Cells(r,c) or
MySheet.Range.Cells(r,c)

Although a syntax violation would not seem to go with a Type Mismatch error,
you might try being a bit more specific as to what you intend Cells to refer
to and see it that helps.

I think the compiler is accurately telling you which line it's having
problems with but misleading you as to what the nature of the problem is.

 

[as mellow as a horse]

Thanks for the reply.

I see what you're trying to say, but I don't think that is the problem.

If I replace 'Cells(rwIndex, 10)' with 'Cells(54, 10)' it DEFINITELY
captures the right cell when I refer to its' Value property, even though I'm
not specifically referring to a specific sheet. It just won't have it when
I use a variable. That's why given 'Cells(r, c)', I wondered if 'r' and 'c'
needed to be a particular type (maybe VBA is casting '54' to the right type
for me?). I've tried integer and variant with the same effect i.e. type
mismatch error.

I've tried prefixing the cells with the worksheet name and it makes no
difference.

The compiler doesn't give a line number BTW, it just doesn't bitch when I
comment out the Cells line(s) or swap rwIndex for a fixed number.

 

[Michael Hopwood]

Works for me!





Option Explicit

Sub test()

Dim rwIndex As Integer
Dim auctionProfit As Currency
Dim currentVal As Currency

auctionProfit = 0
For rwIndex = 5 To 54 ' hard-coded row values will do for now....
If Cells(rwIndex, 10) > 0 Then ' if adjacent cell in next row non-zero
currentVal = Cells(rwIndex, 9).Value
If currentVal > 0 Then auctionProfit = auctionProfit + currentVal
End If
Next rwIndex

MsgBox ("Total Completed Auction Profits = " & CStr(auctionProfit))

End Sub

 

[Oliver Beck]

"as mellow as a horse" <[email protected]> had a problem with

Works for me!

I can't see anything wrong with the code but, if the value of Cells(rwIndex,
10) is not numeric then you will get a type mismatch. Suggest you look at
the data in col 10 and look for non-numeric entries.

OllyB

 

[as mellow as a horse]

"as mellow as a horse" <[email protected]> had a problem with


I can't see anything wrong with the code but, if the value of Cells(rwIndex,
10) is not numeric then you will get a type mismatch. Suggest you look at
the data in col 10 and look for non-numeric entries.

OllyB

When you say "non-numeric" are you talking about the underlying format of
the data, or the formatting as seen by the user? The data in that column is
all in currency format, but I'm trying to assign the value to a Currency
variable so it shouldn't be a problem, should it? The problem from VB's
point of view as I've said elsewhere isn't retrieving the value from a cell,
but using a counter to index into a particular cell i.e. 'Cells(rwIndex, 10)
doesn't work, but 'Cells(15,10)' or 'Cells(50, 10)' will both work. I need
to iterate through any number of cells, so I need the former.

 

[George Nicholson]

I see you are still having problems. As far as i can see the code should
run. If it doesn't you may have entered a twilight zone.

Some suggestions: 1) try declaring rwIndex as Long, or not specifiying a
type at all and see what happens. 2) create a new variable and substitute it
for rwIndex. This shouldn't make any difference, but sometimes (very
rarely) i find the VBE gets an idea that something is wrong and it takes
some smoke and mirrors to convince it otherwise. 3) comment out the
offending code, compile, save, reopen, uncomment and try again.

Sorry I couldn't contribute something more concrete than this voodoo.

Good Luck,