compare a value in two w/books

Looking for a simple code for:
If this worksheet $b$2 value=other w/book sheet1 $h$2 value
then msg"value exists" and exit sub() ,if not do something else.(both 
w/books are in same folder)
0
12/24/2005 7:18:01 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
553 Views

Similar Articles

[PageSpeed] 31

If Activesheet.Range("B2").Value =
Workbooks("other.xls").Worksheets("Sheet1").Range("H2") Then
    Msgbox "Value exists"
End if

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:954E6CAF-A203-4571-A63A-F8012AF3F06D@microsoft.com...
> Looking for a simple code for:
> If this worksheet $b$2 value=other w/book sheet1 $h$2 value
> then msg"value exists" and exit sub() ,if not do something else.(both
> w/books are in same folder)


0
bob.phillips1 (6510)
12/24/2005 11:09:46 AM
Thanks ,but it is not working,compile error:expected expression error is 
coming.What to do ?

"Bob Phillips" wrote:

> If Activesheet.Range("B2").Value =
> Workbooks("other.xls").Worksheets("Sheet1").Range("H2") Then
>     Msgbox "Value exists"
> End if
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
> message news:954E6CAF-A203-4571-A63A-F8012AF3F06D@microsoft.com...
> > Looking for a simple code for:
> > If this worksheet $b$2 value=other w/book sheet1 $h$2 value
> > then msg"value exists" and exit sub() ,if not do something else.(both
> > w/books are in same folder)
> 
> 
> 
0
12/26/2005 3:41:01 AM
Probably just NG wrap-around

If ActiveSheet.Range("B2").Value = _
Workbooks("other.xls").Worksheets("Sheet1").Range("H2") Then
    MsgBox "Value exists"
End If


-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:68CF4AB2-7E5A-4593-BC03-5E984369E5E1@microsoft.com...
> Thanks ,but it is not working,compile error:expected expression error is
> coming.What to do ?
>
> "Bob Phillips" wrote:
>
> > If Activesheet.Range("B2").Value =
> > Workbooks("other.xls").Worksheets("Sheet1").Range("H2") Then
> >     Msgbox "Value exists"
> > End if
> >
> > -- 
> >  HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote
in
> > message news:954E6CAF-A203-4571-A63A-F8012AF3F06D@microsoft.com...
> > > Looking for a simple code for:
> > > If this worksheet $b$2 value=other w/book sheet1 $h$2 value
> > > then msg"value exists" and exit sub() ,if not do something else.(both
> > > w/books are in same folder)
> >
> >
> >


0
bob.phillips1 (6510)
12/26/2005 10:07:06 AM
Same error coming !

"Bob Phillips" wrote:

> Probably just NG wrap-around
> 
> If ActiveSheet.Range("B2").Value = _
> Workbooks("other.xls").Worksheets("Sheet1").Range("H2") Then
>     MsgBox "Value exists"
> End If
> 
> 
> -- 
>  HTH
> 
> Bob Phillips
> 
> (remove nothere from email address if mailing direct)
> 
> "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
> message news:68CF4AB2-7E5A-4593-BC03-5E984369E5E1@microsoft.com...
> > Thanks ,but it is not working,compile error:expected expression error is
> > coming.What to do ?
> >
> > "Bob Phillips" wrote:
> >
> > > If Activesheet.Range("B2").Value =
> > > Workbooks("other.xls").Worksheets("Sheet1").Range("H2") Then
> > >     Msgbox "Value exists"
> > > End if
> > >
> > > -- 
> > >  HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote
> in
> > > message news:954E6CAF-A203-4571-A63A-F8012AF3F06D@microsoft.com...
> > > > Looking for a simple code for:
> > > > If this worksheet $b$2 value=other w/book sheet1 $h$2 value
> > > > then msg"value exists" and exit sub() ,if not do something else.(both
> > > > w/books are in same folder)
> > >
> > >
> > >
> 
> 
> 
0
12/27/2005 6:31:02 AM
Reply:

Similar Artilces:

Negative value in Excel
I would like value in a certain rage of cells to always be negativ without adding minus sign when enter the value. Does anyone know how t set this??? Many thanks -- Message posted from http://www.ExcelForum.com Adhanya, One solution is to put the values in straightaway (as positive), and simply make them look as though they're negative. Select the cells, Format, Cells, Number, Custom, and use something like: -General -0.00 Then in any formulas that refer to them, negate them so they will be treated as negative. Or an event macro can negate them as they're entered. -- Earl Kios...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

copy paste value
I have a colum of vlookup numeric data and want to add it up, but each cell may not return a value depending on how many items are needed. I belive I will need to add a colum in and grab the numeric data that is returned and then use a formula to do a copy paste value and if no value equal zero. I am not sure how to do this thoe. here is the vlookup formula i am using. Can you please help =IF(ISNA(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,FALSE)),"",(VLOOKUP(A3,'Inventory Items'!$A$3:$C$888,'Inventory Items'!$C$1,...

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

Value of x axis
I want to create a chart where the X axis displays a letter instead of a number. The repesentation will be constant (e.g. A=5, B=4, C=3). Is there anyway to do this? Thank you, Kate - I answered the same question just this past week: http://www.google.com/groups?as_umsgid=%23jDCZiMsEHA.832@TK2MSFTNGP10.phx.gbl - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Kate wrote: > I want to create a chart where the X axis displays a letter instead of > a number. The repesentation will be constant (...

Auto Label Y Axis Values
I am producing a dynamic chart and would like to be able to specify the labels for the y axis of my chart automatically (based upon a formula), rather than letting Excel set the scale. I believe that to do this I need a macro - but I am new to using these and dont really know where to start. Basically the chart is on a sheet called Graph and the data that I want to use for the minimum and maximums are in cells T4 and T5 on a sheet called tables (although I have also named the cells Min and Max too). Any help would be greatly appreciated. Thanks. Hi, You need to use code. http://pelt...

Pivot Chart
Hello, I am trying to create a stacked area Pivot Chart from a Pivot Table. I have negative values but am having trouble getting them to be displayed properly in the pivot chart. Currently, the negative values are displayed at the top and are "eating into" the positive series. I need the negative values to be displayed below the x-axis. Any help would be greatly appreciated. Aleksandr, This may be silly to ask, but... Did you try and adjust the y-axis scale from 0 to some number to a negative value (greater than the lowest negative value) to some positive value. Ex...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

Display nothing in a cell if value = 0
I have a range of cells formatted to display numbers in currency format. They have a formula applied to them so if the value of the Cell is nothing I get �0.00 showing. I would rather that nothing was shown in these instances. How is this achieved? Thanks Dom Couple of ways, use conditional formatting and display with a white font if the value is zero change the display option to suppress zeroes, Tools>Options>General and uncheck the Zero Values option. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mail...

How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl transformation on it due to what I call bad characters in the data. I have as an example: str = Replace(str, "&#x19;", ".") str = Replace(str, "&#x18;", ".") In there as filters to strip out characters XSL doesn't like and replace it with periods. I am now getting an exception of a hex value of 0x0E however can't figure out what the string replace value should be for this one. From what I can tell, it's some kind of "shift out" character whatever that is. A...

Select case syntax for continuous range values?
I know you can use Select case statements to cover a range of integer values, such as: Case 100 To 500 Case 501 to 650 etc However, I'm not clear on how to work with non-integer values to ensure that every possible value is covered, while ensuring no overlap in categories. With an IF statement I might use: If x >100 and x <=500 elseif x >500 and x <=650 which would properly assign a value of 500.4395 to the second condition Is there a way to do this with select case? Or is my best option to try to use more decimals than the data might need, e.g. C...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

Highlight field when value is changed
I have multiple rows of data, each one starting with a number. If the number in the front of the row changes, I need that whole row to be highlighted. Then I need a check box on the other side of the row so that I can turn off (reset) the highlighting. 1 2 3 4 a # Name S Reset b 21 Jane Hanson F c 22 Mark Johnson M d 23 Fred Lee M I know I can do this with track changes, but we can't use that because of other issues. Can anyone tell me where I can get info on trying to make something like this? I t...

Outlook Client Address Book not renamed
I've renamed the entity 'Account' to 'Company' in CRM 3.0, but the address book in Outlook is still called 'CRM Accounts' - is there anyway of changing this? thanks in advance. The custom entity name will not display automatically in the ABP. Thanks for your suggestion. We will consider correcting this issue in a future service pack or release. An Issue has been entered into our tracking data base. -- Jason -- This posting is provided "AS IS" with no warranties, and confers no rights. "hb" <hb@discussions.microsoft.com> wrote i...

I need to compare to columns and indicate the matches in another
am wanting to compare 2 columns for exact matching. If there are two matching items I want to be able to say "A match" in a chosen cell for all the ones that match. Column C will be retrived in an random order... so how would I write the formula for that????? Example: A B C 45time 11tune A match 11tune 89time 47doog 43jkjkj A match 123ABC 123ABC If possible include how to highlight the ones that make as another option. Thanks Try something like this: For a value list in B1:B5 and a ...

Referencing two (or more) cell values in formula
Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

Select null values in Crystal Report
I want to select those records in a Crystal Report where the country is not filled. I can select those where it is filled, however I get zero record back when I want to select where it is not filled. I use the following selection formula in the record selection part: Length ({account.address1_country}) < 1 Any idea is appreciated. Thanks, Miklos ...

How to convert to absolute value?
Hello! How do I convert numbers to absolute value? Thanks Edit - Copy. Edit - PasteSpecial - Values - OK. Or do you want a code solution ? HTH. Best wishes Harald "Betsy Marlow" <cmarlow22@bellsouth.net> skrev i melding news:5WmHg.12873$j8.11911@bignews7.bellsouth.net... > Hello! > > How do I convert numbers to absolute value? > > Thanks > > Print abs(-12) 12 On Thu, 24 Aug 2006 15:16:31 -0400, "Betsy Marlow" <cmarlow22@bellsouth.net> wrote: >Hello! > >How do I convert numbers to absolute value? > >Thanks >...

Giving Null a value in a VBA stamtement
Hi all, I am writing some VBA code in Access03 in a fields after update event. The field types are decimal (18,1). I am trying to write a SQL statement which looks like this: Docmd.RUNSQL UPDATE tblStorageShip SET tblStorageShip.AmountLeftmg = ([tblStorageShip]![NumberLeftAliquotSize1]*[tblStorageShip]! [SizeUGAliquotSize1])+([tblStorageShip]! [NumberLeftAliquotSize2]*[tblStorageShip]![SizeUGAliquotSize2]); And if one of the fields is a NULL i get a NULL return for that record. Can I somehow set NULL to euqal 0 for this set of code instruction so anytime it wencounters a null while exec...

How to Assign Values from Access field to a MS Word Field?
Hi I need to know how to assign values from Access field to a MS Word field? Thanks ...

Inserting cell value into text....
I am trying to create text in excel where at certain points a cell value needs to be inserted and change as the original cell value changes. How can I do this and maintain the integrity of the text formatting? Thank you so much! Try something like - ="Answer as shown in cell A10 ; "&A10&", or thereabouts." HTH, Andy Support Boris - visit http://www.ncadc.org.uk/letters/newszine36/boris.html ="abcdef and the value is " & TEXT(A1,"$#,##0.00") will allow you to specify the format of the number that is shown in the resulting string. -...

Chart does NOT show data entered for the values represented
I continue to find a problem in excel 2007 that was not present in 2003. I will input 2or more columns and rows of data to create a chart, then create the chart; but IF MORE THAN ONE data path is selected to be charted, the other data paths are charted incorrectly. They do not reflect the values in the cells! Obviously then, my charts are wrong and I can't rely on them for analysis. Please respond if this has happened to you and you know how to correct for this. I am very frustrated. -- Judi Hi Judi, Data Path? what is this, are your charts refering to other workbooks? Why ...