excel, vba and vlookup

[Guest]

i am trying to write a function to return the cost of samples

the arguments are the range of cells that either contain "Sold" or "Sample"
text (in my case K26:K39)
the function declaration so far is:

<BEGIN VB CODE>

Public Function CostOfSamples(s As Range)
' calculate and return the cost of samples

Dim pricing As Range
Set pricing = Worksheets("Master Price List").Range("B7:044")

CostOfSamples = 0
For Each cellVal In s.Cells
If cellVal.Value = "Sample" Then
CostOfSamples = CostOfSamples +
Application.WorksheetFunction.VLookup(cellVal.Offset(0, -8).Value, pricing,
6, False) * cellVal.Offset(0, -5).Value

End If
Next

End Function

<END VB CODE>

this constantly returns a #VALUE when used in excel

what am i missing?

i tested this using
CostOfSamples = cellVal.Offset(0,-5).Value
and it returned the correct value, but when i added the vlookup it stops.

also, (being new to vba) am i correct in thinking that in each iteration of
the loop, cellVal wil update to the next cell in the range 's'?

thanks!

 

[Niek Otten]

No Idea. But certainly Range("B7:044") should read Range("B7:O44"), seems to
me.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Job]

make sure that the range your looking up doesn't have misc characters or
spaces. Use the trim function;

Sub TrimIt()
'David McRitchie 2000-07-03 mod 2000-08-01
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Cell.Value)
Next Cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

This works really well especially if the data your using came from various
outside source databases etc.

 

[Fredrik Wahlgren]

also, (being new to vba) am i correct in thinking that in each iteration of
the loop, cellVal wil update to the next cell in the range 's'?

thanks!

Yes.

/ Fredrik

 

[Guest]

(hides in shame)

thanks Niek

No Idea. But certainly Range("B7:044") should read Range("B7:O44"), seems to
me.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

no extraneous spaces or miscellaneous characters.... it was the fat finger
hitting the zero instead of the 'o'....

thanks!

 

[Guest]

Thanks Fredrik!

J

Yes.

/ Fredrik