FIND function i VB

[Peter Hesselager]

Hi Group.
Within a VB routine, I'm serching for a dash in a StockNumber.
I Tried
NrSlut = Find("-", ActiveCell.Offset(Line, -6), 1)
But that certainly did not work.

The VB Help suggests to implement the WorkSheetFunction, but I don't seem to
understand it.
Any help would be highly appriciated

 

[Mike]

Try the Instr function in VB

 

[Jim Thomlinson]

I think you want instr. Something like this...

NrSlut = instr(1, ActiveCell.Offset(Line, -6), "-")

Which will return the position where the dash is found.

 

[Mike H]

Hi,

Do you mean the position og a - with in string, if so try

NrSlut = InStr(1, ActiveCell.Offset(0, 1).Value, "-")

interesting variable name!!

Mike

 

[Peter Hesselager]

Hi , You guys got me going !
alas - just to the next problem.
(And I believe, that I have looked into the naming this time ! ( In danish,
NrSlut is a very decent and boring name -really ! )

DevOnline = Application.WorksheetFunction.VLookup(Online, SERIES, 1) - Online
I'm trying to perform a Vlookup function as you see.
SERIES is a named range on the next Sheet.

Trying to run, I get the 1004 error -
can't supply the property VlookUp for the class WorkSheetFunction ( my
translation).

Any good suggestions ??
--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Mike H" skrev:

 

[Jim Thomlinson]

If SERIES is a named range then try something more like this...

DevOnline = Application.WorksheetFunction.VLookup(Online, Range("SERIES"),
1) - Online

 

[Dave Peterson]

Drop the .worksheetfunction in that line.

Dim DevOnLine as Variant 'could be an error
....
DevOnline = application.vlookup(....)
if iserror(devonline) then
'what should happen?
else
if isnumeric(devonline) = false then
'what should happen
else
devonline = devonline - online
end if
end if

======
Since you're only looking at the first column, you could use:
application.match(), too.

See excel's help for those parms.

 

[Peter Hesselager]

Sorry - none of the 2 solutions did work.
Could it have something to do with the data ?
I have a Timestamp ( Online), that I'm checking up against a table of
allowed deliverytimes ( SERIES).
The format is the same in both og the datasets
--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Dave Peterson" skrev:

 

[Dave Peterson]

How did it not work?

Can you get the =vlookup() formula to work in a cell? If yes, then post what
formula worked.

But I thought that there were enough checks in my suggestion to stop any error
from popping up. I checked with iserror to see if there was a match. I checked
with isnumeric before adding.

 

[Dave Peterson]

ps. You may want to post your current code--and include a little more. Like
how you get Series and online.

 

[Peter Hesselager]

Hi!
Maybe it is the referring to my named space, that is causing my pain.
I simply named the table area in the worksheet w/Insert Name
It is not declared in any way in the code.

This is from the code
-------------
'Extracting part of the Order-number
NrSlut = InStr(4, ActiveCell.Offset(Linje, -6), "-")
RekvNr = Mid(ActiveCell.Offset(Linje, -6), 4, NrSlut - 4)
' Finding timestamp (Online) for the incomming order (and some more stuff)
Online = ActiveCell.Offset(Linje, 2) '+ ActiveCell.Offset(Linje,
3)
Bekræft = ActiveCell.Offset(Linje, 8) + ActiveCell.Offset(Linje,
9)
Færdig = ActiveCell.Offset(Linje, 11) + ActiveCell.Offset(Linje,
12)
If Online Then
' Now validate Online aginst the "Office is open" table ( which is SERIE),
and find Deviation (AfvigOnline)
AfvigOnline = Application.VLookup(Online, SERIE, 2)
Status = 1
End If

OnLine format is : 04-02-2008 11:51
and the ErrorCode assigned to AfvigOnline is 2042

Pausing the code JUST before executing the line with AfvigOnline, the OnLine
value is 04-02-2008 11:51, and SERIE reports empty.
AFTER executing, ErrorCode assigned to AfvigOnline is 2042
I think, I didn't get my table SERIE declared OK ??

--

Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Dave Peterson" skrev:

 

[Dave Peterson]

SERIE is a worksheet named range--not a variable in your code?

First, add
Option Explicit
to the top of your module.

Each of your variables will have to be declared and your code won't run if you
don't.

I think I'd try:

Dim Serie as range
....
set serie = activeworkbook.worksheets("somesheetname").range("Serie")

Then try it.

Hi!
Maybe it is the referring to my named space, that is causing my pain.
I simply named the table area in the worksheet w/Insert Name
It is not declared in any way in the code.

This is from the code
-------------
'Extracting part of the Order-number
NrSlut = InStr(4, ActiveCell.Offset(Linje, -6), "-")
RekvNr = Mid(ActiveCell.Offset(Linje, -6), 4, NrSlut - 4)
' Finding timestamp (Online) for the incomming order (and some more stuff)
Online = ActiveCell.Offset(Linje, 2) '+ ActiveCell.Offset(Linje,
3)
Bekræft = ActiveCell.Offset(Linje, 8) + ActiveCell.Offset(Linje,
9)
Færdig = ActiveCell.Offset(Linje, 11) + ActiveCell.Offset(Linje,
12)
If Online Then
' Now validate Online aginst the "Office is open" table ( which is SERIE),
and find Deviation (AfvigOnline)
AfvigOnline = Application.VLookup(Online, SERIE, 2)
Status = 1
End If

OnLine format is : 04-02-2008 11:51
and the ErrorCode assigned to AfvigOnline is 2042

Pausing the code JUST before executing the line with AfvigOnline, the OnLine
value is 04-02-2008 11:51, and SERIE reports empty.
AFTER executing, ErrorCode assigned to AfvigOnline is 2042
I think, I didn't get my table SERIE declared OK ??

--

Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps

"Dave Peterson" skrev:

 

[Peter Hesselager]

I went to the books this morning, and yes same suggestion, you came up with.
Tested in my actual workbook with standard values, yes it works.
Put in my own values ( remember the date & time format ? ) , and it didn't
work.
Made a workaround (be ready to laugh ! ) with putting this value down into a
cell in the sheet, and then reading it back from a neigbor cell with Default
format into another variable as a simple value - and it works.
SO - I believe, I can't get my code to work, untill I get my variable
transformed into default-values instead of Time&Date.

How is the code for making this transform "on the fly" reading values into
OnLine.

--This is the present code, and it works - but how to get rid of the AA6-AA7
"thing" ?
Dim Serie As Range
Set Serie = Worksheets("Kalender").Range("B7:e95")
Range("AA6") = Online 'FROM 04-02-2008 00:00:00
Opslag = Range("AA7") 'TO 39482,000

If Online Then
AfvigOnline = Application.WorksheetFunction.VLookup(Opslag,
Serie, 1) - Online
Status = 1
End If


------
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Dave Peterson" skrev:

 

[Dave Peterson]

I don't see anything that's using AA6.

I see that you didn't remove the .worksheetfunction. I'd remove it.

AfvigOnline = Application.VLookup(clng(Opslag), Serie, 1) - Online

But I'd add those other checks, too.

 

[Peter Hesselager]

Hi Dave

I'm qouting from below
Actually the stuff is running now, but to me, it looks as if the
VLookUp-value cannot be of Time and Date format, but must be a normal figure.
, so therefore this embarrassing method with setting the value into AA6, and
setting AA7= AA6, and format AA7 to Number Format.
I don't know how to convert this in VB ( and that's the question for now )
I don't in depth understand, why you don't like the .worksheetfunction, but
assume, that I could build a better errorhandling this way ??

Hope, you can help me with the variable format.



--
Med venlig hilsen
Peter Hesselager-Olesen
SBS-IT Aps



"Dave Peterson" skrev:

 

[Dave Peterson]

If you use worksheetfunction in the vlookup call and there is no match, you'll
get a run time error.

Your code would have to look more like this:

Dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(...)
if err.number <> 0 then
'No match
err.clear
else
'match
end if
on error goto 0

By dropping the worksheetfunction, you could just check for a returned error:

dim res as variant
res = application.vlookup(...)
if iserror(res) then
'no match
else
'match
end if

I never saw anything in your posted code that would check for that runtime
error.