if statements in excel worksheets Results


Page 1 of 3.
Results 1...20 of 42

Sponsored Links:



- IF is an Excel worksheet function, you can't use it in Access. Instead, you can use the VBA function IIf (Immediate If).
- You don't need to check the condition twice, once is enough.
- You must use parentheses ( ) around the arguments of the function.

=IIf([TotalActualWeight]>[TotalVolumeWeight],[TotalActualWeight],[TotalVolumeWeight])




I want to change an Excel worksheet's tab colour if a cell within the sheet changes to a non-zero value.

I know how to change the tab colour using VBA but am struggling to trap the event which triggers this.

I was trying with this subroutine:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.Sheets("Checks").Tab.ColorIndex = 6
End Sub

(I am going to put the IF statement in later - this is a work-in-progress)

But that subroutine is only triggered when the worksheet in question is active/open and I change any cell by typing into it. I want to trigger the subroutine when a linked cell in the sheet is changed by a change in some other sheet. I hope this makes sense - its an error-trapping process in a financial spreadsheet and the worksheet "Checks" is a summary of all possible errors anywhere in the Workbook.

Although possible, it seems too clumsy to have a subroutine running every few seconds to make the change if required - but I do want to trap these events in real time because that's the best opportunity to correct a mistake.

Grateful for any ideas.

Martin




I'm looking for a "what if" statement that will cover two conditions on two ranges in two worksheets in the same file. Let's say I have one sheet called sheet 1 and another named sheet 2. I would usually first select the sheet with a "Sheets("sheet1").Select" then I would say If Range("a1").Value And Range ("b1').Value= 2 Then" whatever my action is. This is easy on one sheet because VBA knows the ranges I'm talking about are on the sheet I selected.

My problem is how can I identify two ranges on two different sheets in my "What If" statement? Naming the ranges is not an option.

Thanks.




NOTE: This is a different issue than the one I raised in the thread "A problem using class instead of regular modules", even tho it refers to the same code.
----------------------------------------
I have a workbook that uses the ThisWorkbook object to instantiate a class
module within the workbook. There are also two regular modules. This code
does work in Excel 97, 2000 and 2002.

In preparation for moving the class module and the two regular modules to a
VB 6 compiled ActiveX DLL, I converted the two regular modules to class
modules within the workbook.

I decided to keep each class module separate to make code changes easier.

I then added a new regular module to provide Public constants that had been
in the original regular modules.
I also added wrapper functions in the new regular module that could be used
as worksheet functions using functions in the new class modules.

I added Public variables to the new module for instantiating the classes.

All 3 classes are instantiated in the Workbook Open event.

I now find that in Excel 97, the Workbook_Open event is not firing, i.e.,
the Msgbox statement is not executed and the worksheet controls do not
function. Works correctly in Excel 2000 and 2002.
Here's the code in ThisWorkbook.

clsROExcelInstance and clsRONotExcelInstance are Public variables in a
regular module.
------------
Option Explicit

Private clsROWorkbookInstance As clsROWorkbook

Private Sub Workbook_Open()
MsgBox "Workbook_Open" ' This statement does not get executed in Excel
97.
Set clsROWorkbookInstance = New clsROWorkbook
Set clsROWorkbookInstance.ExcelWbk = ThisWorkbook

Set clsROExcelInstance = New clsROExcel
Set clsRONotExcelInstance = New clsRONotExcel
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
With clsROWorkbookInstance
Set .ExcelWbk = ThisWorkbook
If Err.Number = 0 Then
.SetRanges Sh
End If
End With
On Error GoTo 0
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
MsgBox "ClearRanges"
clsROWorkbookInstance.ClearRanges
End Sub




The following Excel code works OK in my workbook, but takes over half a minute to run on each worksheet. There's nearly 80 worksheets so its pretty tedious waiting for the code to finish before I switch to the next sheet to start again.

The code works on a pre-selected range in one column and looks for zeroes and then hides that row before moving to the next row. The range (300+ rows in col A) I have pre-selected contains a formula, an IF statement, and represent zero activity on that row. But the macro only works on one sheet at a time.

Is there a way this code can be modified to run on several pre-selected sheets?
Or can you suggest an alternative procedure?

Sub HideZeroAccounts()
'

Application.ScreenUpdating = False

Selection.EntireRow.Hidden = False
Dim rng As Range
Dim r As Long
Set rng = Selection.Columns(1)
For r = rng.Rows.Count To 1 Step -1
If Not rng.Cells(r) = "" And rng.Cells(r) = 0 Then
rng.Cells(r).EntireRow.Hidden = True
End If
Next r
Application.ScreenUpdating = True

End Sub




Im having issues with figuring another formula to use other than the IF statement. Excel 2007 will not allow me to use more than 7. My spreadsheet has a column that references several other tabs in the same worksheet. Maybe this will help:

=IF($E$9="Bidder #1",G107,IF($E$9="Bidder #2",I107,IF($E$9="Bidder #3",K107,IF($E$9="Bidder #4",M107,IF($E$9="Bidder #5",O107,IF($E$9="Bidder #6",Q107,IF($E$9="Bidder #7",S107,IF($E$9="Bidder #8",U107,IF($E$9="Bidder #9",W107,IF($E$9="Bidder #10",Y107,IF($E$9="CRI BID DAY",D107," ")))))))))))

Where Bidder#1-10 are in different columns.

BIDDER #1 BIDDER #2 BIDDER #3 etc....

There is a dial that I can push to choose which bidder is lowest and if they have any alternates It will show up on the tab with the formula above. Unfortunately excel wont allow me to use this formula. Any help out there???




I have a simple worksheet that seems to be calculating something incorrectly by a very small amount. I simply have a formula to check if the sum of the totals in columns G and H equal the sum of column F. I tried two formulas that that should theoretically return the same result but they don't.
Forumla 1: =IF((E30-F30-G30)=0,"okay","not okay")
Formula 2: =IF(E30=F30+G30,"okay","not okay")

Forumla 1 returns "not okay". When I evaluate the formula, the result of the first part of the formula is -7.23 E-12. When I remove it from the if statement, it returns zero. Any thoughts on why this happens? The file is attached.

Thanks,
Terry




When I open Excel, it does not have focus. I have to click on any part of the window to get the focus. It appears that somehow the focus is on VBA. If I open Excel and just start typing, the characters I type are inserted in front of Option Explict in VBA (even tho I have not make the VBA visible (Alt F11). I am looking at the worksheet and nothing is being entered in any cell, but when I open VBA, I can see the characters I have typed and they are in front of the Option Explicit statement.
I do have a personal.xls and have run compile but nothing was found. I moved the Personal.xls file so it is not opened when Excel opens and then the characters are entered in front of the Option Explicit statement in the Blank Workbook.
Does anyone have any ideas?




I am having troubles with a formula in a table which looks at a group of dates from march 1, 05 to Feb 1, 05

on one page Dates and Work, there is column A which is the date and in B and c is the date broken up in
month col b,
and day col. C,
in col d is the data i want to move into a table on the wks sort by date if the columns that are split up match with the month and day listed in table in worksheet sort by date.

I have a if statement which I thought would work, bascially if value is true then pull from this cell but, it seem that no match can be made because of how i broke my dates up I think, Maybe someone has an idea which can help me.

Thanks




Hi-

I'm trying to track my expenses in Excel, using it as a check register. When the first of the month rolls around, I would like Excel to refer to the system's clock and copy a particular worksheet, renaming the worksheet with the new month. I know that I need an if/then statement. I just can't figure out the code to make it refer to the clock.

Any help would be appreciated as I've been pondering this problem for awhile.




I am trying to simplify the medical supply ordering for our small fire department. I have listed the most commonly ordered supplies from two suppliers. I want to enter a quantity in one column and let Excel figure out which is cheaper and place the quantity, description, and item number on a separate worksheet for the appropriate company, which will then be printed and faxed.

I have been trying to use IF statements to do this, but have not figured out a good logical way to do it. I would appreciate any ideas.




I've built a worksheet that includes a significant amount of Conditional Formatting to turn cells in Col A red if certain Conditions in other columns are met. I regularly copy updated data from an external source into this worksheet.

I'm looking for a simple way to then filter the worksheet to show only the rows that meet the Conditions (rows where Col A is Red).

My thought is to add another column with an IF statement that matches the Conditions (IF Condition is true, THEN "Y", ELSE " ") and then Filter on that Column.

Is there a simpler way?

thanks

Don Brownlee




Hi!

I am going to be receiving a workbook with a number of worksheet tabs on a weekly basis from a new client. The tab I am interested in will be titled "ADD TO JNL DDMMYY" (Where DDMMYY represent a weekly date). If the tab is arranged at the front of the workbook then the "DoCmd.TransferSpreadsheet " function will import the tab without having to add a range at the end. I have no control as to the order of how the worksheets will be arranged and as such need to be able to select the Tab in question using VB. The command I am using is:-

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ADD TO JNL 070606", "J:HAssocBroomleighDanny Hall" & strFileName, True, "'ADD TO JNL 070606'!A1:R1000"

If I omit the single quotes around the range then I get a run time error '3129' with an Invalid SQL statement etc message, but with the quotes I get a run time error '3011' with the message "The Microsoft Jet Database Engine could not find the object "ADD TO JNL 070606'$A1:R1000'. Make sure the object exists and that you spelt its name and the path correctly." I don't have a problem with tabs which don't have any spaces between the words.

Can anyone help here, please?

Cheers,

Niven




Hi,
I've got the following macro that isn't quite working. I didn't want to have to actually name every single worksheet that had the word "budget" in the worksheet name, so I used the If, Then statement. The error I keep getting is that there is no "For" to go with the "Next". Any help would be awesome.
Thanks!
Lana

Sub testpt()

For Each Worksheet In Worksheets

If Worksheet.Name = "*Budget" Then

ActiveSheet.PivotTables("PivotTable5").PivotFields ("Period"). _
CurrentPage = Sheet6.Range("AK8").Value

Else: Range("A1").Select

Next Worksheet

End Sub




I'm a dud at anything but the most trivial formulas so need some expert help here.

I have a spreadsheet which I have been asked to support dynamically switching the data that a set of formulas refers to between two different worksheets (these contain the source data). This switching would occur when the user selects one of two options from a pair of radio buttons which are on the worksheet itself (not a form). If choice 1 is selected, the formulas should point to source sheet 1 else source sheet 2. I'm sure this can be done but don't know the formulas well enough to do it (I can do it in VBA but I'm sure that's overkill and more complex than needed).

I have the 'cell link' set to D17 for the radio button and it returns 1 or 2 based on which button is pushed so I guess I need an IF statement around each formula. There are lots of formulas I need to add this IF statement to was hoping I didn't have to edit each one. Something like:

IF (D17=1,"use price sheet 1", "use price sheet 2")

Both of these source sheets represent pricing data so by the user selection choice 1 or choice 2 they are selecting a different look up table for the prices. Obviously the above formula isn't going to work but the idea is to dynamically switch the source data sheet so the 'real' formulas can know which sheet to use. Is this what the INDIRECT formula is good for?

Is there a slick way to implement this given I already have these formulas set to look at a single priceing sheet?

Thnx, Deb




(Edited by HansV - inserted [tab] tags to preserve indentation - see Help 19)

When a change is made anywhere in a range ("d3:u31") on the current worksheet
I want to change the value of cell A1 from "static" to "changed"
I want to use the A1 value in a macro later on
After the first change I don't need to know there are more changes, hence the first if statement below.

This is what I'm trying at the moment. But with no success.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveSheet.Range("A1") = "static" Then
If Intersect(Target, ActiveSheet.Range("d3:u31")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Range("A1") = "change"
Application.EnableEvents = True
End If
End If
End Sub

Regards




I'd like to create a template/form in Excel that would read an Excel database. We have about 75 people who make quarterly contributions to a stock plan. I'd like the person's name and address to show up at the top of the form and also to have their information since they started participating in the plan show up on the form. I didn't want to have to create 75 different worksheets in one file and enter each individual's data each quarter, and copy and paste formula's etc. If I could just enter the data into a database in Excel and then the form would read the database it would make it much easier and faster to distribute the statements. Any examples to get me started would be greatly appreciated. I've attached an example of how I'd like the form to look, however I'm not sure how I need the actual database to be designed either. Help please!!!!!
Thanks!!!
LJM




Hi again!

I've used the following statement in a number of macros with good success. The idea is that if the state "CT" shows up in the address line then I get flagged with a message box reminding me to check to see if CT sales tax applies.

I've done the same thing with other worksheets for the word "layer" when we have multiple layer products and only want an identification label for the entire moulding to print instead of one for each layer. Normally it would print an ID label for each layer, but this statement below prevents duplicate labels from being printed.

In this case though it is only working if CT is the ONLY thing entered in a cell. In my invoice the town, state and zip all appear in the same cell.

So, if E11 contains "CT" the message box appears. But if E11 contains " Thompson, CT" it does not appear. In my "layer" example the statement works if the corresponding cell says "Layer", "LayerA", "layerB" etc.

I can't figure out why this isn't working just the same for "ct".

If InStr(LCase(Range("E11")), "ct") = 1 Then
MsgBox ("This is a CT customer and may be taxable.")
End If

Thanks,
BH




I am trying to get a macro that will run on blank cells. I note the ISBLANK() function for worksheets but can not seem to get it to work in a Macro. How can I use an if Statement to work on only blank cells.




I'm definitely not a VBA expert, but I'm trying to automate a workbook with some code. I have two comboboxes placed on an Excel worksheet. The contents of the second combobox depend on the choice in the first. I know how to write the statement that populates the second box, but I don't know where to place it so that it happens as soon as the user makes a choice in the first box.

The first box is linked to a cell on the sheet, so it could happen as soon as that cell changes, if that's any help!

Thanks!


Page 1 of 3.
Results 1...20 of 42