| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FIND function i VB
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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
Try the Instr function in VB
"Peter Hesselager" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
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. -- HTH... Jim Thomlinson "Peter Hesselager" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
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: 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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
If SERIES is a named range then try something more like this...
DevOnline = Application.WorksheetFunction.VLookup(Online, Range("SERIES"), 1) - Online -- HTH... Jim Thomlinson "Peter Hesselager" wrote: 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: 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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
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 wrote: 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: 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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
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: 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 wrote: 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: 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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
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. Peter Hesselager wrote: 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: 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 wrote: 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: 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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
FIND function i VB
ps. You may want to post your current code--and include a little more. Like
how you get Series and online. Peter Hesselager wrote: 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: 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 wrote: 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: 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" wrote: 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 -- Med venlig hilsen Peter Hesselager-Olesen SBS-IT Aps -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Function | Excel Discussion (Misc queries) | |||
| If find function to not find anything | Excel Programming | |||
| Find function in VBA | Excel Programming | |||
| Find Function | Excel Worksheet Functions | |||
| backwards find function to find character in a string of text | Excel Programming | |||
Hybrid Mode
