VBA Command to Look at cell in an If statement

I need to create a macro in VBA that will look at a specific cell (which 
contains the result of a lookup formula) in an If statement.  Basically it 
would look at a certian cell, and If that cell had the word "YES", then it 
would execute a seprate macro.  If not, just continue on it merry way.

I have tried the following and several variations:

Sheets("Print Area").Select
Range("BO8:BO8").Select
If BO8 = YES Then Call PrintBatch

What the program has to do is look at the last entry made in a specific 
column (hence the use of the Lookup formula in the cell) and if that entry 
says "YES", then to execute the sub routine "PrintBatch".  I tried using 
Frank Kabel's article on "Getting the Last Value in Range", but I seem to be 
stuck on actually using the results of the selected cell and comparing it to 
"YES".  Any help would be appreciated.  I have also ordered the 2 reference 
books on VBA programing and commands noted in another thread, but they are 
not due to be delivered until next week.

Thanks again for any help.


0
Wolf (13)
12/27/2004 5:13:57 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
634 Views

Similar Articles

[PageSpeed] 35

try this from anywhere in the workbook. Notice the periods   .    !

with sheets("Print Area")
x=.cells(columns.count,8).end(xltoright).column
  if ucase(.cells("b",x))="YES" then PrintBatch
end with

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Wolf" <Wolf@discussions.microsoft.com> wrote in message
news:2144B0D9-9B4D-4E3B-B053-1FF21953A5AE@microsoft.com...
> I need to create a macro in VBA that will look at a specific cell (which
> contains the result of a lookup formula) in an If statement.  Basically it
> would look at a certian cell, and If that cell had the word "YES", then it
> would execute a seprate macro.  If not, just continue on it merry way.
>
> I have tried the following and several variations:
>
> Sheets("Print Area").Select
> Range("BO8:BO8").Select
> If BO8 = YES Then Call PrintBatch
>
> What the program has to do is look at the last entry made in a specific
> column (hence the use of the Lookup formula in the cell) and if that entry
> says "YES", then to execute the sub routine "PrintBatch".  I tried using
> Frank Kabel's article on "Getting the Last Value in Range", but I seem to
be
> stuck on actually using the results of the selected cell and comparing it
to
> "YES".  Any help would be appreciated.  I have also ordered the 2
reference
> books on VBA programing and commands noted in another thread, but they are
> not due to be delivered until next week.
>
> Thanks again for any help.
>
>


0
Don
12/27/2004 5:57:06 PM
Don,

Thanks for your help.  For some reason when I did a cut and paste, the If 
Ucase line kept coming back with a debug error.  I changed it a bit to:

With Sheets("Print Area")
     If UCase(.Cells(8, 67)) = "YES" Then PrintBatch
End With

So far it appears to do what I need it to do (keeps fingers crossed).  
Thanks again for all your help and have a safe and Happy New Year.
"Don Guillett" wrote:

> try this from anywhere in the workbook. Notice the periods   .    !
> 
> with sheets("Print Area")
> x=.cells(columns.count,8).end(xltoright).column
>   if ucase(.cells("b",x))="YES" then PrintBatch
> end with
> 
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Wolf" <Wolf@discussions.microsoft.com> wrote in message
> news:2144B0D9-9B4D-4E3B-B053-1FF21953A5AE@microsoft.com...
> > I need to create a macro in VBA that will look at a specific cell (which
> > contains the result of a lookup formula) in an If statement.  Basically it
> > would look at a certian cell, and If that cell had the word "YES", then it
> > would execute a seprate macro.  If not, just continue on it merry way.
> >
> > I have tried the following and several variations:
> >
> > Sheets("Print Area").Select
> > Range("BO8:BO8").Select
> > If BO8 = YES Then Call PrintBatch
> >
> > What the program has to do is look at the last entry made in a specific
> > column (hence the use of the Lookup formula in the cell) and if that entry
> > says "YES", then to execute the sub routine "PrintBatch".  I tried using
> > Frank Kabel's article on "Getting the Last Value in Range", but I seem to
> be
> > stuck on actually using the results of the selected cell and comparing it
> to
> > "YES".  Any help would be appreciated.  I have also ordered the 2
> reference
> > books on VBA programing and commands noted in another thread, but they are
> > not due to be delivered until next week.
> >
> > Thanks again for any help.
> >
> >
> 
> 
> 
0
Wolf (13)
12/27/2004 7:39:02 PM
That's cuz I goofed. Try with a period just before columns.count

 x=.cells(.columns.count,8).end(xltoright).column
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Wolf" <Wolf@discussions.microsoft.com> wrote in message
news:437C3DF6-E401-47AE-99EE-3F97A55DE986@microsoft.com...
> Don,
>
> Thanks for your help.  For some reason when I did a cut and paste, the If
> Ucase line kept coming back with a debug error.  I changed it a bit to:
>
> With Sheets("Print Area")
>      If UCase(.Cells(8, 67)) = "YES" Then PrintBatch
> End With
>
> So far it appears to do what I need it to do (keeps fingers crossed).
> Thanks again for all your help and have a safe and Happy New Year.
> "Don Guillett" wrote:
>
> > try this from anywhere in the workbook. Notice the periods   .    !
> >
> > with sheets("Print Area")
> > x=.cells(columns.count,8).end(xltoright).column
> >   if ucase(.cells("b",x))="YES" then PrintBatch
> > end with
> >
> > -- 
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Wolf" <Wolf@discussions.microsoft.com> wrote in message
> > news:2144B0D9-9B4D-4E3B-B053-1FF21953A5AE@microsoft.com...
> > > I need to create a macro in VBA that will look at a specific cell
(which
> > > contains the result of a lookup formula) in an If statement.
Basically it
> > > would look at a certian cell, and If that cell had the word "YES",
then it
> > > would execute a seprate macro.  If not, just continue on it merry way.
> > >
> > > I have tried the following and several variations:
> > >
> > > Sheets("Print Area").Select
> > > Range("BO8:BO8").Select
> > > If BO8 = YES Then Call PrintBatch
> > >
> > > What the program has to do is look at the last entry made in a
specific
> > > column (hence the use of the Lookup formula in the cell) and if that
entry
> > > says "YES", then to execute the sub routine "PrintBatch".  I tried
using
> > > Frank Kabel's article on "Getting the Last Value in Range", but I seem
to
> > be
> > > stuck on actually using the results of the selected cell and comparing
it
> > to
> > > "YES".  Any help would be appreciated.  I have also ordered the 2
> > reference
> > > books on VBA programing and commands noted in another thread, but they
are
> > > not due to be delivered until next week.
> > >
> > > Thanks again for any help.
> > >
> > >
> >
> >
> >


0
Don
12/27/2004 11:27:45 PM
Reply:

Similar Artilces:

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 ...

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...

Looking for MS CRM Partner in Northern CA Bay Area
Hello, We are a small startup company (MCSP) in the N. Calif. Bay area looking for a similar company to partner up with. We are currently 2 employees. I am a Microsoft MCSE with quite a lot of systems experience which includes SQL Server, Exchange and Cisco products. Both myself and my partner are former Microsoft Great Plains Business Systems employees from the Solomon Software division. We are looking to team up with other Microsoft CRM partners and Microsoft Small Business Manager partners for a mutually beneficial relationship. E-mail me directly to contact. Chuck Sellers Fina...

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 ...

FWD: Take a look at these internet update
--mgukdotbv Content-Type: multipart/related; boundary="ltnbnmtomik"; type="multipart/alternative" --ltnbnmtomik Content-Type: multipart/alternative; boundary="yxqdtzodsux" --yxqdtzodsux Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS User this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help protect your computer. This update includes the functionality = of...

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 ...

Why are Outlook commands disabled?
I have just reinstalled MS Office Standard 2007 after a system crash. The new installation doesn't do anything when I attempt to open my old .pst file or when I attempt to set up my e-mail accounts. I make the selection and nothing happens - no error messages or anything... ...

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 &...

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...

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...

Workbook_Close Command
I am looking for a code which would restore the spread sheet to "Clean State" before saving (when it is being saved): Something like the following which would be automatically trigged when sheet is saved: Sub Before_Save () OnSave ActiveSheet.AutoFilterMode = False Range("A3").Select end sub Thanks for your help Jan Maybe you could use the workbook_beforesave event: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With Worksheets("Sheet1") .AutoFilterMode = False Application.Goto ...

Any way to highlight cells that change?
Is there a way to highlight all of the cells in a worksheet that change as a result of some action? I have some worksheets with calculations that depends on other calculations that depend on other calculations and so on. If I make a change to one value or one formula, I may not notice if some cell (possibly off the screen) is changed. I would like some way to make them stand out. A border like the copy border or something like that would be great. -- Running Excel 2000 SP-3 on Windows 2000 Click the cell you have just changed (or are about to change) On the Formula Auditing toolbar, click T...

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...

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, > ...

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. >...

newbie here. How do I rearrange how a report looks?
Hi, I have Access 2007. I have created a report with business name, address, etc... along with a list of different types of employees (administrative, clerical, etc..). I am trying to create a report with the Business name and details all in one block (sort of how it would look like if you were creating mailing labels) and under that I want to create columns title administrative, clerical etc... with the names of employees under each column. Right now, my report is totally in columns, and the list of employees is seperated my commas instead of being under each other - like i...

combining cells in tables in excel
I have created 2 excel tables in 1 word document and I need to add the sum of 1 cell out of each table together. I know that there is a way to reference the cells in other tables but every formula that i try does not work. I have already bookmarked both tables as instructed by Help and yet still nothing. If anyone can help me link these 2 cells together it would be greatly appreciated! Thanks ...

Different beginning cell each time for same macro
I would like my macro to begin in a different cell that I select each time and then have the first step of the macro select the 12 cells immediately to the right of the cell I select each time. Currently my macro runs perfectly except that the macro will only begin in the exact same cell every time the marco is run. This means that the same 13 cells are used by the macro every time. Where as I need the marco to begin in a different cell that I more or less randomly select and then the macro should select the 12 cells immediately to the right of that cell. I know someone out there...

minimum cell value
Hi, I am trying to set up a cell so it has a minimum value. The current formula is: =a1/a2*5 which gives the answer 0 but i need to show a minimum value of 1 is this possible? Hi, Doug, Try: =Max(a1/a2*5,1) --- Regards, Norman "Doug Bell" <Doug Bell@discussions.microsoft.com> wrote in message news:8766BC4D-D3CD-4B4F-AF7B-FD145DE56205@microsoft.com... > Hi, > > I am trying to set up a cell so it has a minimum value. > > The current formula is: =a1/a2*5 which gives the answer 0 but i need to > show > a minimum value of 1 is this possible? > ...

VBA Customization
Is there a way to get the name of the current company through VBA in Great Plains 8.0? Thanks for all your help. open GreatPlains. Add the current window, it will add the Toolbar then add fields choose the company name on the top left after the user id. >-----Original Message----- >Is there a way to get the name of the current company through VBA in Great >Plains 8.0? > >Thanks for all your help. > > > >. > Thanks. I tried, but I couldn't add company name to the field list. It opens up the login screen instead. Any thoughts? Thanks again. "ger...

Run VBA without show
I want excel to run the VBA code without showing me what it is doing. Which code should i use for that? Sub RunWithoutShow() Application.ScreenUpdating = False 'Paste the vba code here 'end of code 'Change screenupdating property to true Application.ScreenUpdating = True End Sub I hope this helps... Selva V Pasupathy For more on Excel, VBA, & other Resources Please visit: http://socko.wordpress.com You may also (inserting into quote from code example bellow from Socko) Use Application.Visible property to hide the application entirely Sub RunWithoutShow() Appli...

Form Controls vs VBA controls
I am new to VBA so just finding my feet, can anyone help me with th differences between an excel control (i.e. one from the form toolbox lets say a checkBox and a vba checkBox? From trying the two controls the I have noticed is that you canno capture the change event of the VBA checkbox in the code module, it ha to be done via the sheet (i.e. checkbox on sheet1, code goes in chang event on sheet1) the control is placed on, is this correct? What woul you do if you have three sheets with the same controls just differen data, do you really have to code the control 3 times? The excel control ho...

Cell Selection issue
Morning all. I'm making a user form, and want to select a cell to place my equation, then with that selection choice, offset upwards, and to the side, to select and merge two sets of two cells, each. I understand with my selection that I'd use the following: activecell.offset(0,1).select to get my first cell selection. But how do I retain that initial selection, and then offset one more up, to then merge those two cells? Thank you. I'm not 100% sure what you are wanting but I think you want to merge the cell to the right of the activecell with the cell one r...

inserting and renaming a sheet vba
Instead of having 2 lines of code, one to insert a new sheet and one t rename it, is there a way to insert a sheet with particular name i one go -- Matt Housto ----------------------------------------------------------------------- Matt Houston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=451 View this thread: http://www.excelforum.com/showthread.php?threadid=26717 ...