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................Miss ing
.........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................Miss ing
.........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


--
Dolemite
------------------------------------------------------------------------
Dolemite's Profile: http://www.excelforum.com/member.php...o&userid=26136
View this thread: http://www.excelforum.com/showthread...hreadid=400976