syntax question - cell references in VB code

Given the following code (found on this site..)

    With ActiveSheet.Range("B:B")
        Set FoundCell = .Find(what:="whatever", _
                            after:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, LookAt:=xlPart, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False)
    End With

How do I tell VB to look in Cell H1  for the what:="whatever" ?

ie.  I want to  use the value found in the Cell H1 not the text "whatever"

I've tried every combination of H1, H1:H1, $H1, Range(H1), Range(H1:H1), 
Range("H1"),etc....

All generate compile errors, syntax errors, or runtime errors....

Forgive the simple question, but I'm not VB literate and I don't have a 
manual handy. 

0
3/2/2005 8:33:10 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
466 Views

Similar Articles

[PageSpeed] 43

One way:

    With ActiveSheet.Range("B:B")
        Set FoundCell = .Find(What:=ActiveSheet.Range("H1").Value, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
    End With



In article <5467E2AF-52E7-4888-8F8B-C509C7FFB5FC@microsoft.com>,
 needyourhelp <needyourhelp@discussions.microsoft.com> wrote:

> Given the following code (found on this site..)
> 
>     With ActiveSheet.Range("B:B")
>         Set FoundCell = .Find(what:="whatever", _
>                             after:=.Cells(.Cells.Count), _
>                             LookIn:=xlFormulas, LookAt:=xlPart, _
>                             SearchOrder:=xlByRows, SearchDirection:=xlNext, _
>                             MatchCase:=False)
>     End With
> 
> How do I tell VB to look in Cell H1  for the what:="whatever" ?
> 
> ie.  I want to  use the value found in the Cell H1 not the text "whatever"
> 
> I've tried every combination of H1, H1:H1, $H1, Range(H1), Range(H1:H1), 
> Range("H1"),etc....
> 
> All generate compile errors, syntax errors, or runtime errors....
> 
> Forgive the simple question, but I'm not VB literate and I don't have a 
> manual handy.
0
jemcgimpsey (6723)
3/2/2005 8:52:34 PM
Reply:

Similar Artilces:

Syntax to check protected status of a worksheet?
I've used ActiveWorkbook.ProtectStructure to return a True/False value indicating that a workbook is password protected. Does similar syntax exist for the active sheet? I have tried a number of variations but can't find it, and for some reason (reason conversion to XL 2003?) I don't get those popup windows that suggest my next choice of code words. Any ideas? Thanks. You can protect a few different things on a worksheet. Dim wks as worksheet set wks = activesheet If wks.ProtectContents _ Or wks.ProtectDrawingObjects _ Or wks.Prote...

Syntax for testing the value of a boolean in integration manager
Hi All I'm imnporting customers to GP10 with IM and I want to set the address ID of the billing address based on a bit field in my DB. I have a field called "InvoiceAddress" which has a user-defined name (e.g. "Billing") I want to put a script on the Bill To Address filed, that checks if the InvoiceAddress field is true. If it is, it sets the address id to the the user defined name (e.g. Billing). I can't figure out how to test if the bit field is true. I've tried: If SourceFields(ImportSource.InvoiceAddress) = True Then .... End if Also tried = -1 = '...

Yet another address book question
Just move to Outlook ought three and t'was my good luck to see a posting here simply saying the you could no longer automatically put people you reply to in your address book. That's what I was looking for. Followed the link to some code to work around this but there was no explanation about how to make the code work. So, what's the real work around for this handy feature (automatically adding people to your phone book)? Do I just use OE or move to some other email program? Thanks, Becky If you are referring to the sample code at http://www.outlookcode.com/d/code/autob...

Is there anything C# can do and VB.NET cannot
I am learning VB.NET for sometime now and curious to know what am I missing by not learning C#. Also wanted to know is there anything VB.NET can do and C# cannot. Looks like C# getting popular day by day. Thanks for your guidence. Dan Dan wrote: > I am learning VB.NET for sometime now and curious to know what am I missing > by not learning C#. > Also wanted to know is there anything VB.NET can do and C# cannot. > > Looks like C# getting popular day by day. > > Thanks for your guidence. > Dan There are a few obscure things VB.NET can do th...

Error Code 646 11-18-09
While performing windows updates, error code 646 encountered and frequently crashes the internet explorer and Mozila Firefox in the middle of web browsing. Please help me. "Chimi" <Chimi@discussions.microsoft.com> wrote in message news:0D035A3F-C150-4EDD-B071-81F5BCCB1E08@microsoft.com... > While performing windows updates, error code 646 encountered and > frequently > crashes the internet explorer and Mozila Firefox in the middle of web > browsing. > > Please help me. What version of Windows and Internet Explorer do you have? However, ...

Date on X-axis question
I have Dates on the X-axis of a chart. I would like the markers or ticks to be the actually dates. But the way it is now it has the major unit as 7 so it is showing every tick every 7 days. But if the data skips a couple weeks I would not like it to show the ticks inbetween. So I just want the dates on the x-axis Does this make sense? Any ideas of how to fix this? Excel has detected a time series; you want to tell it to treat dates as simple category values. 1)Right click chart; use Chart Option form popup menu 2)Open the Axis tab; under X-axis, click the Category option box (e...

AOL and Microsoft on an exchange question
I hope someone might know the answer to this problem. I have a customer who uses AOL. I use Outlook 2007 and am on an exchange server. My customer has a problem when he forwards me an email from anyone outside of my office. The original email comes to me in the form of an attachment, even tho there were no attachments used. For example, A sends an email to my customer. That email has no attachments. My customer forwards me the email. When I receive the email A's email to my customer is in the form of an attachment. It looks like an envelope. Can we make the forward come to ...

Compacting Design Question
I am using the following code in the on close event of my form: CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Sub I have heard that you should use the compacting event from a command button. Can someone give me the reasons that you should only do this on command rather than every time the form closes? Thanks for your feedback. Why would you want to compact that frequently? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele...

when writing an IF statement what is the syntax for "Not Equal to.
If statement for greater than would be =IF(B12>0,B12) What would it be for NOT EQUAL to ? one way: =IF(B12<>0,B12) which will return FALSE if B12 = 0 In article <2A4F6074-C4A9-4617-8D8E-A4C011B35B6E@microsoft.com>, "NEEDTOKNOW" <NEEDTOKNOW@discussions.microsoft.com> wrote: > If statement for greater than would be =IF(B12>0,B12) > > What would it be for NOT EQUAL to ? ...

tricky send question
I didn't get any responses when I first posted this so I'm trying again, Help, please!! I've got a problem that I just can't solve. I'm using WinXP, Outlook XP (2002), Zonealarm, and McAfee. I've already scanned (and rescanned) the whole pc for MyDoom, it's clean. Here is what is happening... I have an e-mail which is incoming only (pop3, no smtp), so outgoing goes nowhere. Everytime I send/receive, even though my outbox is empty, there is an attempted send from my e-mail. This, of course, fails, and there is no history of it anywhere, not in the outb...

Two questions for MS Money Plus, Please help
1). I have read the statement, said the support for MS money only available in United States and Japan. I am living in Australia, does that mean MS Money Plus is not supported for Australia, or simply just do not have money support center in Australia? 2). I am using MS Money 2007 Home & Business editiion, can I just upgrade it to MS Money Plus 2008 Delux edition? That means from higher edtion of older version to lower edition of newer version. Thanks in advance. George Money is very heavily localised to take account of the fiscal rules in the regions where it is sold. So a U...

Problem with Report code
Here is my code Private Sub Report_Activate() If Me![rptCReDriving Licence].Report.Recordset.RecordCount = 0 Then Me.lblnoinfo.Visible = False Else Me.lblnoinfo.Visible = True End If Me.Refresh End Sub I have a main report with a number of subreports. in the above example if the rptCREDriving Licence has no data I want lblnoinfo (which is an unbound label) to appear in its place. However it doesn't work. can anyone see why? Thanks Tony Hi Duane tried your second suggestion and got a prompt box for the name of the report and Access changed the control source to this =IIf...

Macro security question
Greetings, I just moved from W98 to XP Pro. I brought along my spreadsheets with macros I wrote. When I open the workbooks in Microsoft Office Excel 2003 running under XP Pro, I get a message that the macros are disabled and some guidance on how to lower the security from Very high to High and then ..... Well I followed the guidanc and found that my security was already set at High and then the rest of the guidance didn'ty work. I tried help but couldn't find anything that makes sense. Can some one tell me how to tell the software that my macros are ok? TIA Ray, lower it to medium...

Question #4
Recently a person wrote to me with a problem, this was the problem:- He had a doc-view application based on CFormView and had a button on the view, clicking which the frame needs to get hidden and the what needs to remain on the screen is the client area, which is the View.Also the view-area shouldn't increase/decrease in width/height when the button click operation takes place. I wrote out a piece of code and sent it over to him. But since this was a bit unusual , I wanted to know the opinions of guys here on solving this problem. I will post the answer which I gave later, if mine ...

Formatting Code shows up as Header when Cut/Paste from Word Document in Firefox
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Hello, <br><br>Recently, whenever I cut/paste text from a Word doc into a another document, such as e-mail, posting to an online newsletter, etc. I get a header with various streams of info at the top. In e-mail, I've just been deleting it, but I recently posted an event to an online newsletter and when it appeared online, I copied below what showed up before the text. I see it in e-mails, so I can delete it, but in this case, it never showed up as part of the preview. I've tried uninstalling/re-installing Firefox. H...

how to make sure a xsl document has valid xsl syntax? i tried loading it into an xml document but that doesnt show syntax errors inside attributes such as "foo/bar" vs "bar\foo"
how to make sure a xsl document has valid xsl syntax? i tried loading it into an xml document but that doesnt show syntax errors inside attributes such as "foo/bar" vs "bar\foo" Daniel wrote: > how to make sure a xsl document has valid xsl syntax? i tried loading it > into an xml document but that doesnt show syntax errors inside attributes > such as "foo/bar" vs "bar\foo" System.XML performs only checks for the well-formedness of Documents, not for validity. You need a Schema for XSLT wich defines Regular Expressions to check for "sy...

Stupid Pivot Table question
I've spent an hour now pulling my hair out on what I think should have a simple solution... I have one column of dates and times, another of numerical values. I've created a pivot table, grouped by year, month, and day, with the average of the value calculated for each day. I need to get this data out of the pivot table and into regular cells for another program, with one column of dates and a second of values averaged by day. The problem is that the Pivot Table only seems to store the dates as text, with the year and month values only appearing once. I.e., the first row of my PT has...

Date codes
How can i address the problem of date codes: so that 5/6/04 can be correctly entered as either 5th of june, or th 6th of may depending from where the date emanated -- Message posted from http://www.ExcelForum.com It is dependant on the regional setting of the computer, a UK entered date like 25/04/2004 will convert to 04/25/2004 on a US regional setting, Of course if the dates are text it won't work -- Regards, Peo Sjoblom "adn4n >" <<adn4n.158lno@excelforum-nospam.com> wrote in message news:adn4n.158lno@excelforum-nospam.com... > How can i address the pro...

Spam question #3
Hello We run Antigen 9.00.1055 on our Exchange 2003 sever. Lately we have many e-mails that are spam coming in our inboxes. The texts in the e-mails are "ok", meaning not spam related, but they pasted a picture in the actually e-mail. And it is in this pictures the spam is (adds about Viagra etc) Is there anyway that Antigen or Exchange or Outlook, can stop these kind of e-mails? Regards -AA- ...

a strange question about URLDownloadToFile and IE
hey all, i face a strange question about URLDownloadToFile() api function, the funtion always fail to download an URL, but the URL can be opend well in IE, what's more, the function can works well to download the URL after i open the URL on IE, it's really puzzle me. it should be not a question about source code, maybe thing wrong with IE? anybody can help me? thanks. regards, jyan Could be a permission problem. You could try this sample code and see if it works then compare it to yours. I know this one works. http://www.codeproject.com/internet/urldownload.asp Tom "...

Question, rephrased....
What I want to do is create a new view for my document, basically. How is this implemented? The circular links on MSDN don't lead me to any useful info on actual implementation. :-) I have been able to create a new frame but not attach a new view class to it. Also when I close my new frame I get an assertion. I just need a new window to display some text. Hope this is not too vague a question. At's an SDI app using Form View as main view. I'd rather not deal with another dialog with a text control in it if possible. From what I've read this is not so simple to ...

OR syntax
I want to insert 1 in a cell if the value in another cell matches one or more of 5 other cells. I have tried =IF(OR(C20=C5,D5,E5,F5,G5),1,"-") but does not seem to work. Is this syntax is incorrect? May be there is a better way to do this? Your help will be much appreciated. Try it this way: =3DIF(OR(C20=3DC5,C20=3DD5,C20=3DE5,C20=3DF5,C20=3DG5),1,"-") Hope this helps. Pete On Jan 14, 4:00=A0pm, "Dave" <dave@dave> wrote: > I want to insert 1 in a cell if the value in another cell matches one or > more of 5 other cells. =A0I have tried =...

WDDM(display)
hello, what I'm about to to is a developing WDDM driver to support external device such as USB monitor. So I started researching through debugging with R200 samples to understand display framework, and seems quite confusing. here's few questions. 1. as far as I understood, to support multimonitors, let's say we've got two monitors and to use another as extended monitor, WDDM display driver should build multiple present paths(here two paths, for example). So I suspected at first that display driver had to create and add present path for newly attached monitor with ...

XADM Question
All I want to do is grand someone else administrator=20 rights to a single user in exchange. Then All I need to=20 do is go into open additional mailboxes and be able to=20 open his mail box. >-----Original Message----- >Are you wanting to add delegate rights or to grant access=20 as an >administrator? > >In Microsoft Exchange Server 5.5, when you grant Service=20 Account Admin >access rights on the "Site" container to a Microsoft=20 Windows-based account, >you grant that account unrestricted access to all=20 mailboxes. > >Here are some similar articles: &...

Refresh My data view in VB
I'm having trouble making visual basic very visual! :) I'd like to create a visually updating view of some data. So my visual basic macro goes off and grabs an array full of data, it fills some cells on a page with that data, then it goes off and gets some more and refills the cells. I'd like to see the cells change each time (a bit of delaying will likely be required, tweaking to the desired speed ). What happens is the macro goes off, processes all the data fetches from the raw data sheet, and finishes. The screen updates only once (after several seconds) showing me the f...