Countif in VB Script

D

Dolemite

Alright, once again, I am having an issue with syntax, and can't quite
get this right.

Basically I am wanting a listing of unique units that don't have a
corresponding value in a table on another sheet. I have everything
else being done, but can't get the unique list going through a VB
Script...here is a little (or a Lot) of info for background....

I have a worksheet that gets populated by "units"/Codes (whatever) that
are entered by a user in column C. When they are actually entering the
values the worksheet does nothing significant. I have an "update"
button at the top of the sheet that when clicked it starts a macro that
fills in several other columns on the sheet according to what is entered
in columns B & C (B contains quantity, C contains unit). One of the
equations that is entered into a column is a lookup/match formula that
returns the cost for the particular unit that is listed in column C.
However, there are times when new units are entered into this column,
but haven't been entered into the master listing. So obviously the
lookup formula will return an error. I am trying to get a listing of
all the UNIQUE units/codes to list in another column. I have been
trying to do this with the countif statement but can only seem to get
it to work using explicit cell references. But I can't use explicit
cell references with the manner in which I am using it.

For Example...given the following sheet setup (not exactly like mine,
but gets the point across):

.........A.................B...................C...................D................E.......................F
........No...........Qty.................Unit.............Cost.............Total................Missing
.........1..............1.....................A1............
.........2..............1.....................AA1..........
.........3..............2.....................A2............
.........3..............5.....................AA1..........


If AA1 and A2 are missing costs and return an error in column D when I
push the update button, I would like to have the following result

.........A.................B...................C...................D................E.......................F
........No...........Qty.................Unit.............Cost.............Total................Missing
.........1..............1.....................A1................$5.00..........$5.00................AA1
.........2..............1.....................AA1...............N/A.............$0.00................A2
.........3..............2.....................A2.................N/A.............$0.00................

If a unit is missing the cost I am only wanting it to be listed only 1
time. I figured that a countif statement would be more efficient than
a for/if combo for the entire listing (the listing can get extremely
lengthy) Also, there is also information above this listing (in other
words "No." does not reside in cell A1, actually right now it is in
A11)

I have a range defined so that the first cell of the range is C11 where
my first unit is listed. I am then using a for loop to populate the
remaining columns where there is value in Column C. This all works
fine. I just can't get my unique values extracted from the listing in
column C to put in my Missing column.

Here is my code...I have removed some of the big nasty equations that I
am entering into some of the cells...they are irrelevent. The line that
is highlighted in Red is the one that I have not been able to figure
out. I have written so many different versions of it that I finally
confused myself and gave up. Included is a hardcoded version just to
show you what I am wanting to accomplish. I know this is quite a bit
of info, but I figured the more the better. Thanks in advance for any
help.

PS-Please go gentle on my sloppy & inefficient use of code below...I
use what I can to get things accomplished....


Private Sub UpdatePriceButton_click()
On Error GoTo GetOut

Dim i As Integer
Dim j As Integer
Dim unit As Range
Dim CalcMode As Long

Application.EnableEvents = False

'disable autocalculation on sheet
With Application
CalcMode = .Calculation
..Calculation = xlCalculationManual
End With

With ActiveSheet
rowtotal = .Range("h3").Value
lastrow = .Range("h2").Value
dif = .Range("h4").Value
Set unit = .Range("c11")
With unit
For i = 1 To rowtotal
If Not (unit.Offset(i - 1, 0).Value = "") Then 'if there is a value
in column C
If .Offset(i - 1, -1).Value = "" Then 'If the quantity is
blank
..Offset(i - 1, -1) = 1 'set it to be 1
End If '.offset(i-1,-1).Value = ""
..Offset(i - 1, dif) = "=indirect(""c[-8]"",0)"
..Offset(i - 1, dif - 1) = "=indirect(""c[-8]"",0)"
If Left(unit.Offset(i - 1, 0), 1) = Worksheets("Remove
Price").Cells(2, 4) Or Left(unit.Offset(i - 1, 0), 1) = "i" Then
'Determine type of unit from prefix
'Big nasty eq goes here for column D
Else 'if unit doesn't have the I then it is assumed to be a
install unit
If Left(unit.Offset(i - 1, 0), 1) = Worksheets("Install
Price").Cells(2, 4) Or Left(unit.Offset(i - 1, 0), 1) = "n" Then
'install unit is preceeded with "N"
'Big nasty eq goes here for column D
Else 'If unit doesn't have an I or an N, then assumed to be
an install unit
'Big Nasty Eq goes here for Column D
End If
End If
..Offset(i - 1, 2) =
"=if(iserror(indirect(""c[-1]"",0)),0,indirect(""c[-1]"",0))" 'Column
E
Else
..Offset(i - 1, -1) = ""
For j = 1 To 11
..Offset(i - 1, j) = ""
Next j
End If '(Not (unit.offset(i-1,0).Value = "")
Next i

'Turn auto calculate back on
Application.Calculation = xlCalculationAutomatic

Dim count1 As Integer
Dim count2 As Integer
count1 = 1
For i = 1 To rowtotal
If Not (unit.Offset(i - 1, 0).Value = "") Then 'If unit is not
blank
If Not (.Offset(i - 1, 3) > 0) Then 'if the total cost is 0
'.Offset(i - 1, 20) = .Offset(i - 1, 0) 'list the unit in
col W
'check to see if it is a unique unit
row1 = unit.Offset(i - 1, 0).Row
uniq1 = Evaluate("=CountIf(c11:c13, c13)")
'the above line is the one that I can not get to work properly with
dynamic
'values. As I have it above it works. But that doesn't help me much.

'As the above statement is it would be used in row 13.
'Below is one of my attempts that didn't work.
' uniq1 = Application.CountIf(Worksheets("Budget").Range( _
indirect("C11", "C" & unit.Offset(i - 1, 0).Row - 1)),
indirect("c[-1]"))
If uniq1 = 1 Then
'copy the value in C to the next available slot using count1 as a
pointer
End If 'if it is a unique value that doesn't have a cost
End If 'if the total cost is 0
End If 'Not(unit.offset(i-1,0).value = "") then
Next i

End With
End With


'clear message to click update button
Set rng = ActiveSheet.Range("d6")
rng.ClearContents
Set rng = ActiveSheet.Range("l6")
rng.ClearContents

Application.EnableEvents = True
Exit Sub
GetOut:
Beep
Application.EnableEvents = True
Application.Calculation = CalcMode
MsgBox "error" & Err.Number & " " & Err.Description
End Sub
 
J

Jim Cone

Hello D,

Do I understand correctly...
Go down column D and for each error value copy the Column C
value to Column F. However, if the value in Column C is already in
column F then do nothing?

Regards,
Jim Cone
San Francisco, USA

"Dolemite"
<[email protected]>
wrote in message
Alright, once again, I am having an issue with syntax, and can't quite
get this right.

Basically I am wanting a listing of unique units that don't have a
corresponding value in a table on another sheet. I have everything
else being done, but can't get the unique list going through a VB
Script...here is a little (or a Lot) of info for background....

I have a worksheet that gets populated by "units"/Codes (whatever) that
are entered by a user in column C. When they are actually entering the
values the worksheet does nothing significant. I have an "update"
button at the top of the sheet that when clicked it starts a macro that
fills in several other columns on the sheet according to what is entered
in columns B & C (B contains quantity, C contains unit). One of the
equations that is entered into a column is a lookup/match formula that
returns the cost for the particular unit that is listed in column C.
However, there are times when new units are entered into this column,
but haven't been entered into the master listing. So obviously the
lookup formula will return an error. I am trying to get a listing of
all the UNIQUE units/codes to list in another column. I have been
trying to do this with the countif statement but can only seem to get
it to work using explicit cell references. But I can't use explicit
cell references with the manner in which I am using it.

For Example...given the following sheet setup (not exactly like mine,
but gets the point across):

.........A.................B...................C...................D................E.......................F
........No...........Qty.................Unit.............Cost.............Total................Missing
.........1..............1.....................A1............
.........2..............1.....................AA1..........
.........3..............2.....................A2............
.........3..............5.....................AA1..........


If AA1 and A2 are missing costs and return an error in column D when I
push the update button, I would like to have the following result

.........A.................B...................C...................D................E.......................F
........No...........Qty.................Unit.............Cost.............Total................Missing
.........1..............1.....................A1................$5.00..........$5.00................AA1
.........2..............1.....................AA1...............N/A.............$0.00................A2
.........3..............2.....................A2.................N/A.............$0.00................

If a unit is missing the cost I am only wanting it to be listed only 1
time. I figured that a countif statement would be more efficient than
a for/if combo for the entire listing (the listing can get extremely
lengthy) Also, there is also information above this listing (in other
words "No." does not reside in cell A1, actually right now it is in
A11)

I have a range defined so that the first cell of the range is C11 where
my first unit is listed. I am then using a for loop to populate the
remaining columns where there is value in Column C. This all works
fine. I just can't get my unique values extracted from the listing in
column C to put in my Missing column.

- snip -
 
D

Dolemite

That is exactly what I am trying to accomplish.

I have been trying to do this with the countif statement in combinatio
with "indirect" and have been unsuccessful. I am trying to avoid
"for" loop... Any help would be appreciated
 
J

Jim Cone

Dolemite,

Well my favorite VBA tool is the loop.
CountIf can get awfully slow.
One way I would approach this is to loop down Column D checking for error values.
Then add the value from Column C to a VBA.Collection. The Collection will spit out
any values that it already contains. So if no error add the value to Column F.

Regards,
Jim Cone
San Francisco, USA
'-------------------------

Sub DemoOnly()
Dim colCvalues As Collection
Set colCvalues = New Collection

On Error Resume Next
colCvalues.Add vbNullString, CStr(Range("C4").Value)
If Err.Number = 0 Then Range("F4") = Range("C4").Value
On Error GoTo 0

'Do this as soon as you don't need it anymore.
Set colCvalues = Nothing
End Sub
'------------------------


"Dolemite"
<[email protected]>
wrote in message
That is exactly what I am trying to accomplish.
I have been trying to do this with the countif statement in combination
with "indirect" and have been unsuccessful. I am trying to avoid a
"for" loop... Any help would be appreciated.--
Dolemite
 
D

Dolemite

Thank you for the response! It took a little bit of playing around to
figure out how that was actually working...and I can't say that I am
entirely sure...but it is working and working well.


Could you enlighten me as to how & why this statement actually works:

colvalues.add vbNullString, CStr(Range("a1").value)
..add(item, [key], [before], [after])

I just am not getting it....
obviously the vbNullString is the item and the value (as a string) of
A1 is the key
but I can't find any simple explanation of why this works. Why
vbNullString? Or better yet, could you explain to me the item & key.



And thanks again for the code, it does work great!
 
J

Jim Cone

Dolemite,

Glad to hear you got it working. A collection can be very useful.
One way to think about it is to consider how a hospital
identifies/keeps track of its patients.
They could do it by the appearance the patient (age,gender, color) etc.
However, they use a wrist band identifier, which is usually not subject
to interpretation.

A collection is a storage device that can hold almost anything from
objects, strings, numbers to even other collections (The Item).

The collection keeps track what it has stored by using a unique string
identifier for each item (The Key). Only one unique Key is allowed,
so you get an error if you try to duplicate it.

In the example, I provided, every item stored was an empty string,
but each key was unique. There is no need to load up a collection
with other than empty strings unless you have to access the items later.

You will see code samples such as...
colThings.Add Cstr(Range("Al").Value), Cstr(Range("A1").Value)

But if you are only checking for duplicates, what you put in the
item slot makes no difference.

Regards,
Jim Cone
San Francisco, USA



"Dolemite"
<[email protected]>
wrote in message
Thank you for the response! It took a little bit of playing around to
figure out how that was actually working...and I can't say that I am
entirely sure...but it is working and working well.
Could you enlighten me as to how & why this statement actually works:
colvalues.add vbNullString, CStr(Range("a1").value)
add(item, [key], [before], [after])

I just am not getting it....
obviously the vbNullString is the item and the value (as a string) of
A1 is the key
but I can't find any simple explanation of why this works. Why
vbNullString? Or better yet, could you explain to me the item & key.
And thanks again for the code, it does work great!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Convert old wordbasic code into VBA that will run now 0
Removing a repeated words 0
Macro to Select text from a point given to a specific word 2
VBA concatenate ranges into a new document 0
Making a macro only work on certain files 2
Select Text to End of Footnote 9
Sheet to sheet code Type Mismatch 6

Top