Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Rose Howard   on Nov 26 In MS Office Category.

  
Question Answered By: Barabas Cohen   on Nov 26

> intStore = Val("strStore")

is trying to extract a number  from the string literal "strStore".
There isn't one there, of course. It should be extracting it from
the contents of the variable  strStore:

> intStore = Val(strStore)

and your code  then works.

Note that your second Hlookup

> strLocation = _
> Application.HLookup(5, _
> Range("b3:f5"), 2, False)

actually fails in my test, because there was no store  5 in your data
example. The error is "Type Mismatch". The reason for this is that
it is trying to put an error code into strLocation, which is a
string variable.

If there is any possibility of the lookup returning an error code,
you will need to declare the destination variable as Variant.

Note that this will also be one of the reasons you were having
trouble with the direct use of strStore in the lookup. The other
reason, presumably, is that HLookUp won't match a string containing
a "1" with a cell containing a 1, for some reason.

Share: 

 

This Question has 3 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on VBA & Hlookup from a newbie Or get search suggestion and latest updates.


Tagged: