What VBA function returns cell 2 from which cell 1 gets its value?
This is a simple and possibly embarrassing question. In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on a different sheet. Cell 1 has a UDF, so it looks like: "=MassageData(SalesSheet!$A$1)" Given that I know cell 1 at runtime, what VBA function returns cell 2? I have tried: Set Cel2 = Cel1.Precedents(1) Set Cel2 = Cel1.Precedents.Cells(1, 1) etc. but that only seems to return Cel1 itself (at least that's what the Debug Window shows). 1) Is my syntax wrong? 2) Is Excel's lack of external dependent/precedent functionality in my old version, the reason? 3) If so, has that un-feature ever been fixed? I guess I could manually remove the UDF and do: Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula)) or maybe this sickness (if I have the syntax right): Set Cel2 = Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula)) But what's the simple way that's staring me in the face? Thanks much. ***
|
0 |
|
Jim
3/24/2010 5:10:08 AM
excel.programming
6508 articles. 
2 followers.
7 Replies
883 Views
On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke <baobob@my-deja.com> wrote: >This is a simple and possibly embarrassing question. > >In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on >a different sheet. > >Cell 1 has a UDF, so it looks like: > >"=MassageData(SalesSheet!$A$1)" > >Given that I know cell 1 at runtime, what VBA function returns cell 2? > >I have tried: > >Set Cel2 = Cel1.Precedents(1) >Set Cel2 = Cel1.Precedents.Cells(1, 1) >etc. > >but that only seems to return Cel1 itself (at least that's what the >Debug Window shows). > >1) Is my syntax wrong? > >2) Is Excel's lack of external dependent/precedent functionality in my >old version, the reason? > >3) If so, has that un-feature ever been fixed? > >I guess I could manually remove the UDF and do: > >Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula)) > >or maybe this sickness (if I have the syntax right): > >Set Cel2 = >Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula)) > >But what's the simple way that's staring me in the face? > >Thanks much. > >*** For the address, perhaps: rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address --ron
|
|
0 |
|
Ron
3/24/2010 12:13:22 PM
On Wed, 24 Mar 2010 08:13:22 -0400, Ron Rosenfeld <ronrosenfeld@nospam.org> wrote: >>*** > >For the address, perhaps: > >rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address > >--ron Never Mind. That doesn't work --ron
|
|
0 |
|
Ron
3/24/2010 1:36:17 PM
If your formula is as simple as you show (only one range reference), then
the answer is probably as simple as this...
.ShowPrecedents
Set Cel2= Cel1.NavigateArrow(False, 1, 1)
.ShowPrecedents True
If you have other range references in your formula, and especially if those
references are for multiple sheets, then the code gets more complicated as
the 2nd and 3rd arguments have to account for them.
--
Rick (MVP - Excel)
"Jim Luedke" <baobob@my-deja.com> wrote in message
news:51588a88-75d9-4a99-ae3f-d2f15188f137@s20g2000prm.googlegroups.com...
> This is a simple and possibly embarrassing question.
>
> In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on
> a different sheet.
>
> Cell 1 has a UDF, so it looks like:
>
> "=MassageData(SalesSheet!$A$1)"
>
> Given that I know cell 1 at runtime, what VBA function returns cell 2?
>
> I have tried:
>
> Set Cel2 = Cel1.Precedents(1)
> Set Cel2 = Cel1.Precedents.Cells(1, 1)
> etc.
>
> but that only seems to return Cel1 itself (at least that's what the
> Debug Window shows).
>
> 1) Is my syntax wrong?
>
> 2) Is Excel's lack of external dependent/precedent functionality in my
> old version, the reason?
>
> 3) If so, has that un-feature ever been fixed?
>
> I guess I could manually remove the UDF and do:
>
> Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula))
>
> or maybe this sickness (if I have the syntax right):
>
> Set Cel2 =
> Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula))
>
> But what's the simple way that's staring me in the face?
>
> Thanks much.
>
> ***
|
|
0 |
|
Rick
3/24/2010 4:56:19 PM
Cel1.Precedents.Select will activate a range which is the union of cells appearing in the formula (Excel 2007). If you have trace arrows showing on your sheet then NavigateArrow will allow you to select a particular one to follow. "Ron Rosenfeld" wrote: > On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke <baobob@my-deja.com> > wrote: > > >This is a simple and possibly embarrassing question. > > > >In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on > >a different sheet. > > > >Cell 1 has a UDF, so it looks like: > > > >"=MassageData(SalesSheet!$A$1)" > > > >Given that I know cell 1 at runtime, what VBA function returns cell 2? > > > >I have tried: > > > >Set Cel2 = Cel1.Precedents(1) > >Set Cel2 = Cel1.Precedents.Cells(1, 1) > >etc. > > > >but that only seems to return Cel1 itself (at least that's what the > >Debug Window shows). > > > >1) Is my syntax wrong? > > > >2) Is Excel's lack of external dependent/precedent functionality in my > >old version, the reason? > > > >3) If so, has that un-feature ever been fixed? > > > >I guess I could manually remove the UDF and do: > > > >Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula)) > > > >or maybe this sickness (if I have the syntax right): > > > >Set Cel2 = > >Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula)) > > > >But what's the simple way that's staring me in the face? > > > >Thanks much. > > > >*** > > For the address, perhaps: > > rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address > > --ron > . >
|
|
0 |
|
Utf
3/24/2010 11:06:01 PM
As far as I know, that will only work for Precedent cells on the same worksheet as the cell (Cel1 in this case)... Precedents will not work on cells referencing other sheets. The only way I know to do that is how I showed in my post... show the precedent arrows, then use NavigateArrow to find the cell on the foreign sheet that you want and the turn the arrows off. The code for this can get complicated if there are several cells on other sheets, especially if there are multiple other sheets involved. -- Rick (MVP - Excel) "pbart" <pbart@discussions.microsoft.com> wrote in message news:585812DF-6C7F-47D1-9343-777F2226D0AE@microsoft.com... > Cel1.Precedents.Select > > will activate a range which is the union of cells appearing in the formula > (Excel 2007). > > If you have trace arrows showing on your sheet then NavigateArrow will > allow > you to select a particular one to follow. > > > > "Ron Rosenfeld" wrote: > >> On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke <baobob@my-deja.com> >> wrote: >> >> >This is a simple and possibly embarrassing question. >> > >> >In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on >> >a different sheet. >> > >> >Cell 1 has a UDF, so it looks like: >> > >> >"=MassageData(SalesSheet!$A$1)" >> > >> >Given that I know cell 1 at runtime, what VBA function returns cell 2? >> > >> >I have tried: >> > >> >Set Cel2 = Cel1.Precedents(1) >> >Set Cel2 = Cel1.Precedents.Cells(1, 1) >> >etc. >> > >> >but that only seems to return Cel1 itself (at least that's what the >> >Debug Window shows). >> > >> >1) Is my syntax wrong? >> > >> >2) Is Excel's lack of external dependent/precedent functionality in my >> >old version, the reason? >> > >> >3) If so, has that un-feature ever been fixed? >> > >> >I guess I could manually remove the UDF and do: >> > >> >Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula)) >> > >> >or maybe this sickness (if I have the syntax right): >> > >> >Set Cel2 = >> >Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula)) >> > >> >But what's the simple way that's staring me in the face? >> > >> >Thanks much. >> > >> >*** >> >> For the address, perhaps: >> >> rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address >> >> --ron >> . >>
|
|
0 |
|
Rick
3/24/2010 11:41:22 PM
On Wed, 24 Mar 2010 12:56:19 -0400, "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote: >If your formula is as simple as you show (only one range reference), then >the answer is probably as simple as this... > > .ShowPrecedents > Set Cel2= Cel1.NavigateArrow(False, 1, 1) > .ShowPrecedents True > >If you have other range references in your formula, and especially if those >references are for multiple sheets, then the code gets more complicated as >the 2nd and 3rd arguments have to account for them. > >-- >Rick (MVP - Excel) Rick, 1. I think the argument in the second line should be True. 2. Will this work in a function? --ron
|
|
0 |
|
Ron
3/25/2010 10:37:33 AM
Rick You are right; my apologies! A different strand of thinking could be to cheat and record the address of predecessor cell (SalesSheet!$A$1) as a string on ProfitSheet at the time the data is set up; the string could be used in the formula "=MassageData(INDIRECT(Cel3)) much as one would a database 'foreign key'. I would only suggest this as a potential solution if it fits well into the spirit of the workbook in question. "Rick Rothstein" wrote: > As far as I know, that will only work for Precedent cells on the same > worksheet as the cell (Cel1 in this case)... Precedents will not work on > cells referencing other sheets. The only way I know to do that is how I > showed in my post... show the precedent arrows, then use NavigateArrow to > find the cell on the foreign sheet that you want and the turn the arrows > off. The code for this can get complicated if there are several cells on > other sheets, especially if there are multiple other sheets involved. > > -- > Rick (MVP - Excel) > > > "pbart" <pbart@discussions.microsoft.com> wrote in message > news:585812DF-6C7F-47D1-9343-777F2226D0AE@microsoft.com... > > Cel1.Precedents.Select > > > > will activate a range which is the union of cells appearing in the formula > > (Excel 2007). > > > > If you have trace arrows showing on your sheet then NavigateArrow will > > allow > > you to select a particular one to follow. > > > > > > > > "Ron Rosenfeld" wrote: > > > >> On Tue, 23 Mar 2010 22:10:08 -0700 (PDT), Jim Luedke <baobob@my-deja.com> > >> wrote: > >> > >> >This is a simple and possibly embarrassing question. > >> > > >> >In Excel 2002 SP3, cell 1 is on ProfitSheet and depends on cell 2, on > >> >a different sheet. > >> > > >> >Cell 1 has a UDF, so it looks like: > >> > > >> >"=MassageData(SalesSheet!$A$1)" > >> > > >> >Given that I know cell 1 at runtime, what VBA function returns cell 2? > >> > > >> >I have tried: > >> > > >> >Set Cel2 = Cel1.Precedents(1) > >> >Set Cel2 = Cel1.Precedents.Cells(1, 1) > >> >etc. > >> > > >> >but that only seems to return Cel1 itself (at least that's what the > >> >Debug Window shows). > >> > > >> >1) Is my syntax wrong? > >> > > >> >2) Is Excel's lack of external dependent/precedent functionality in my > >> >old version, the reason? > >> > > >> >3) If so, has that un-feature ever been fixed? > >> > > >> >I guess I could manually remove the UDF and do: > >> > > >> >Set Cel2 = Range(StripTheEqualSignAndUDFFrom(Cel1.Formula)) > >> > > >> >or maybe this sickness (if I have the syntax right): > >> > > >> >Set Cel2 = > >> >Application.WorksheetFunction.INDIRECT(StripDitto(Cel1.Formula)) > >> > > >> >But what's the simple way that's staring me in the face? > >> > > >> >Thanks much. > >> > > >> >*** > >> > >> For the address, perhaps: > >> > >> rg.Precedents.Worksheet.Name & "!" & rg.Precedents.Address > >> > >> --ron > >> . > >> > . >
|
|
0 |
|
Utf
3/25/2010 11:23:01 AM
Similar Artilces:
Need to set extra hard return
Hi, This must be a very basic thing, but I have been asked to submit a paper with extra hard returns in Word. How do I do this? Can anyone help me out please? Thanks. Viju vijuhegde wrote: > This must be a very basic thing, but I have been asked to submit a > paper with extra hard returns in Word. How do I do this? Can anyone > help me out please? Ask whomever assigned this task to explain this task. Essentially a "hard return" signifies the end of a paragraph. It's simply pressing ENTER. The "soft return" is just a way to start a new line - h...
Sum after Sorting #2
There is an Excel Spreadsheet like the following Project Value Sum Power 120 354 (Power + Computer) Computer 234 AV 123 Quest 200 In other words, the formula for 354 is "=B2+B3" There is a requirement that we have to sort the first column in ascending order but keep the sum still be the value for Power & Computer (as follow) Project Value Sum AV 123 354 Computer 234 Power 120 Quest 200 I have tried a number of ways but still cannot get it fixed. Is there any suggestion ? Hi! Based on your example in the second table it lo...
VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...
Automatically copy cell above
Does anyone know how to automatically copy cell above the current cell? I have a long list of employees with account numbers. However, I want to only include employees with 6-xxxxx account numbers. Having blank cells on some of rows with account numbers, I may pull 6-xxxxx account numbers without names after sorting the cells by account numbers. Here is how my spreadsheet looks like: ABDULLAH,MALIK 6-63027-1200 ABROKWA,JOE 2-11932-1100 2-11932-2000 2-11987-1010 2-11987-2000 ACTON,LATOYA 6-63027-1200 ACTON,LYNETTE 2-24001-1400 ...
SLOW FUNCTIONS
Dear Experts, I have a 'new" excel 2007 & need to run in compatability mode because most of my associates have 2003. Almost all functions - from cut and paste or copy and paste are delayed while the little blue circle thing spins. Usually form 4 to 10 seconds. it really adds up. What can I do to correct ? Thanks -- WDM I am running 2007 on a 5 year old PC and do not experience what you describe. Is your PC really old? Do you have much RAM? (this thing has 1G of RAM, running WinXP) Do you have any add-ins loaded that might not be completely compatible with 2007? &...
Pay commissions to 2 salespersons
Is there a way to pay commissions to two different salespersons for one line item? When I try to enter secont salesperson in "Sales Commissions Entry" window I receive a message "The commission amount may not be greater the sale amount distributed to this salesperson" Commissions in GP are calculated a little differently than what we think about. If I have two salespeople and I want to give each a 5% commission on the sale, then I need to give each of them a 10% commission on 50% of the sale. -- Jim "Pay commissions to 2 salespersons" wrote: > Is th...
Maintain Aspect Ratio of 2D Plot on Different Monitors ?? #2
Hello; There's a good chance that some might have already posted this question (including myself!), but I couldn't find any related threads in this forum directly addressing the problem. Suppose you've a 2D chart displaying the fuselage of an airplane nicely proportional on the Full Screen of your desktop monitor. You copy the w/b to your laptop (with the same XL 2003) and the display is completely out of proportion because of the different aspect ratio of the screen. Q1: Instead of Full Screen display, would a Custom View help in maintaining the aspect ratio of the 2D plo...
Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...
SQL Not Working
What is wrong with this SQL? I am trying to use a Median function and trying to have the median calculate for each JobCode. SELECT tEmployeeMasterCopy.JobCode, (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode FROM tEmployeeMasterCopy GROUP BY tEmployeeMasterCopy.JobCode; Thanks in advance. It seems that (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) ...
Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...
Remap F2 key function to Enter key?
Could someone please tell me how I can set the Enter key to edit the selected cell in Excel 2002? (just like F2 does by default). Much appreciated On Nov 20, 12:08 pm, mark.hand...@gmail.com wrote: > Could someone please tell me how I can set the Enter key to edit the > selected cell in Excel 2002? (just like F2 does by default). > > Much appreciated Please help If your keyboard software allows re-mapping you may be able to do this. I would think that using the ENTER key for cell editing could cause some problems and would not be intuitive. How about just double-click the ce...
How to get font information from clipboard #2
I am writing an image process program. I want to cut or copy some text with font information from other software, such as MS Word, Excel etc . In my program, the text will be draw on screen with same font. So I need to get the font information from clipboard, what should I do? Thanks. ...
VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...
vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...
Drop Down List #2
When you first type the first letter for the email address on the TO: line, in Outlook 2007, you notice that a drop down list shows up with the last email addresses you had used in the past. Is there a way of cleaning out this list? Thanks in advance. -- Moe Jones HVAC Service Technician Energy Equalizers Inc. Houston, Texas www.EnergyEqualizers.com Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your arrow key to migrate to the entry) and then hitting your Delete key before you tab out of the f...
VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...
Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...
get change operation on coCustomLinks cannot find table
Hello, I have this client that has deployed their server in Singapore but has a branch in Thailand. Since Thailand also needs to use Great Plains, they have installed a GP 8.0 application in a workstation in Thailand and connects to the database deployed in Singapore. But when the staff in thailand logs onto her workstation, she gets the error "get/change first operation on coCustomLinks cannot find table" then all suceeding document processing (i.e. GL entries), go haywire - the next journal entry number doesn't generate. They went through the same installation procedures ...
Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...
calling function in another UI thread
I created another UI thread from the main thread using AfXBeginThread. I defined a new function in the new UI thread. Can I directly call this function in the main thread, or do I have to PostThreadMessage? If I can directly call the function, what kind of IPC mechanism is it? I am little confused. Thank you! class CNewUIThread : public CWinThread { ..... void MyNewFunc() { .... } ......... } a thread does not have functions, it is a sequence of instructions, usually including a loop that processes messages or does some other computation until it either finishes its work or is killed by...
my paste special function won't let me copy formulas
I am using Excel 2007 and I can't seem to copy and paste formulas from one workbook to another. Instead I get a dialogue box asking me if I want to use paste or paste link and to chose from a list of formats. Does anyone know if there is something that I can change in Excel to fix this problem? It sounds like you have the workbooks open in separate instances of excel. Close one of the workbooks (and its instance of excel) and then back to the first workbook/excel. Hit ctrl-o and open your second workbook and try the copy|paste. squintana wrote: > > I am using E...
How do I protect specific cells from changes in Excel?
Please can someone help me. How do I protect specific cells from changes in Excel? I have a spreadsheet that I would like users to only be able to change certain cells, the others need to be protected from change Go to Format>Cells. On the Protection tab, uncheck the "Locked" box for any cell you want to be editable. After you protect the document, the user will only be able to edit the cells you unlock. "LarryH" <LarryH@discussions.microsoft.com> wrote in message news:2E8A5FD8-A990-4181-9842-19B007C88F71@microsoft.com... > Please can someone help me. ...
Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...
Getting the result on the basis of date
hi,I have query which is based on a table with employee daily activities.I want to extract the report on the basis of date. I included thefollowing parameter [enter_date] - date/time format and in criteria igave [date]. however when i run the query its giving the entire dateand not the date which i enter in the input box. Can anyone help mehow to solve this problem?Regards,Karthik...
Excel and VBA
I am very interested in learning more about using VBA in office, especially Excel. Can you post some resources (online, books, etc) that can be used to help learn about using Visual Basic in office. Thanks! Here's a good place to start: http://www.contextures.com/xlbooks.html Does that help? *********** Regards, Ron XL2002, WinXP "Bob Sinclair" wrote: > I am very interested in learning more about using VBA in office, especially > Excel. Can you post some resources (online, books, etc) that can be used to > help learn about using Visual Basic in office. >...
Hi, This must be a very basic thing, but I have been asked to submit a paper with extra hard returns in Word. How do I do this? Can anyone help me out please? Thanks. Viju vijuhegde wrote: > This must be a very basic thing, but I have been asked to submit a > paper with extra hard returns in Word. How do I do this? Can anyone > help me out please? Ask whomever assigned this task to explain this task. Essentially a "hard return" signifies the end of a paragraph. It's simply pressing ENTER. The "soft return" is just a way to start a new line - h...
Sum after Sorting #2
There is an Excel Spreadsheet like the following Project Value Sum Power 120 354 (Power + Computer) Computer 234 AV 123 Quest 200 In other words, the formula for 354 is "=B2+B3" There is a requirement that we have to sort the first column in ascending order but keep the sum still be the value for Power & Computer (as follow) Project Value Sum AV 123 354 Computer 234 Power 120 Quest 200 I have tried a number of ways but still cannot get it fixed. Is there any suggestion ? Hi! Based on your example in the second table it lo...
VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...
Automatically copy cell above
Does anyone know how to automatically copy cell above the current cell? I have a long list of employees with account numbers. However, I want to only include employees with 6-xxxxx account numbers. Having blank cells on some of rows with account numbers, I may pull 6-xxxxx account numbers without names after sorting the cells by account numbers. Here is how my spreadsheet looks like: ABDULLAH,MALIK 6-63027-1200 ABROKWA,JOE 2-11932-1100 2-11932-2000 2-11987-1010 2-11987-2000 ACTON,LATOYA 6-63027-1200 ACTON,LYNETTE 2-24001-1400 ...
SLOW FUNCTIONS
Dear Experts, I have a 'new" excel 2007 & need to run in compatability mode because most of my associates have 2003. Almost all functions - from cut and paste or copy and paste are delayed while the little blue circle thing spins. Usually form 4 to 10 seconds. it really adds up. What can I do to correct ? Thanks -- WDM I am running 2007 on a 5 year old PC and do not experience what you describe. Is your PC really old? Do you have much RAM? (this thing has 1G of RAM, running WinXP) Do you have any add-ins loaded that might not be completely compatible with 2007? &...
Pay commissions to 2 salespersons
Is there a way to pay commissions to two different salespersons for one line item? When I try to enter secont salesperson in "Sales Commissions Entry" window I receive a message "The commission amount may not be greater the sale amount distributed to this salesperson" Commissions in GP are calculated a little differently than what we think about. If I have two salespeople and I want to give each a 5% commission on the sale, then I need to give each of them a 10% commission on 50% of the sale. -- Jim "Pay commissions to 2 salespersons" wrote: > Is th...
Maintain Aspect Ratio of 2D Plot on Different Monitors ?? #2
Hello; There's a good chance that some might have already posted this question (including myself!), but I couldn't find any related threads in this forum directly addressing the problem. Suppose you've a 2D chart displaying the fuselage of an airplane nicely proportional on the Full Screen of your desktop monitor. You copy the w/b to your laptop (with the same XL 2003) and the display is completely out of proportion because of the different aspect ratio of the screen. Q1: Instead of Full Screen display, would a Custom View help in maintaining the aspect ratio of the 2D plo...
Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...
SQL Not Working
What is wrong with this SQL? I am trying to use a Median function and trying to have the median calculate for each JobCode. SELECT tEmployeeMasterCopy.JobCode, (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode FROM tEmployeeMasterCopy GROUP BY tEmployeeMasterCopy.JobCode; Thanks in advance. It seems that (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) ...
Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...
Remap F2 key function to Enter key?
Could someone please tell me how I can set the Enter key to edit the selected cell in Excel 2002? (just like F2 does by default). Much appreciated On Nov 20, 12:08 pm, mark.hand...@gmail.com wrote: > Could someone please tell me how I can set the Enter key to edit the > selected cell in Excel 2002? (just like F2 does by default). > > Much appreciated Please help If your keyboard software allows re-mapping you may be able to do this. I would think that using the ENTER key for cell editing could cause some problems and would not be intuitive. How about just double-click the ce...
How to get font information from clipboard #2
I am writing an image process program. I want to cut or copy some text with font information from other software, such as MS Word, Excel etc . In my program, the text will be draw on screen with same font. So I need to get the font information from clipboard, what should I do? Thanks. ...
VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...
vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...
Drop Down List #2
When you first type the first letter for the email address on the TO: line, in Outlook 2007, you notice that a drop down list shows up with the last email addresses you had used in the past. Is there a way of cleaning out this list? Thanks in advance. -- Moe Jones HVAC Service Technician Energy Equalizers Inc. Houston, Texas www.EnergyEqualizers.com Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your arrow key to migrate to the entry) and then hitting your Delete key before you tab out of the f...
VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...
Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...
get change operation on coCustomLinks cannot find table
Hello, I have this client that has deployed their server in Singapore but has a branch in Thailand. Since Thailand also needs to use Great Plains, they have installed a GP 8.0 application in a workstation in Thailand and connects to the database deployed in Singapore. But when the staff in thailand logs onto her workstation, she gets the error "get/change first operation on coCustomLinks cannot find table" then all suceeding document processing (i.e. GL entries), go haywire - the next journal entry number doesn't generate. They went through the same installation procedures ...
Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...
calling function in another UI thread
I created another UI thread from the main thread using AfXBeginThread. I defined a new function in the new UI thread. Can I directly call this function in the main thread, or do I have to PostThreadMessage? If I can directly call the function, what kind of IPC mechanism is it? I am little confused. Thank you! class CNewUIThread : public CWinThread { ..... void MyNewFunc() { .... } ......... } a thread does not have functions, it is a sequence of instructions, usually including a loop that processes messages or does some other computation until it either finishes its work or is killed by...
my paste special function won't let me copy formulas
I am using Excel 2007 and I can't seem to copy and paste formulas from one workbook to another. Instead I get a dialogue box asking me if I want to use paste or paste link and to chose from a list of formats. Does anyone know if there is something that I can change in Excel to fix this problem? It sounds like you have the workbooks open in separate instances of excel. Close one of the workbooks (and its instance of excel) and then back to the first workbook/excel. Hit ctrl-o and open your second workbook and try the copy|paste. squintana wrote: > > I am using E...
How do I protect specific cells from changes in Excel?
Please can someone help me. How do I protect specific cells from changes in Excel? I have a spreadsheet that I would like users to only be able to change certain cells, the others need to be protected from change Go to Format>Cells. On the Protection tab, uncheck the "Locked" box for any cell you want to be editable. After you protect the document, the user will only be able to edit the cells you unlock. "LarryH" <LarryH@discussions.microsoft.com> wrote in message news:2E8A5FD8-A990-4181-9842-19B007C88F71@microsoft.com... > Please can someone help me. ...
Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...
Getting the result on the basis of date
hi,I have query which is based on a table with employee daily activities.I want to extract the report on the basis of date. I included thefollowing parameter [enter_date] - date/time format and in criteria igave [date]. however when i run the query its giving the entire dateand not the date which i enter in the input box. Can anyone help mehow to solve this problem?Regards,Karthik...
Excel and VBA
I am very interested in learning more about using VBA in office, especially Excel. Can you post some resources (online, books, etc) that can be used to help learn about using Visual Basic in office. Thanks! Here's a good place to start: http://www.contextures.com/xlbooks.html Does that help? *********** Regards, Ron XL2002, WinXP "Bob Sinclair" wrote: > I am very interested in learning more about using VBA in office, especially > Excel. Can you post some resources (online, books, etc) that can be used to > help learn about using Visual Basic in office. >...