Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

evaluate cell values

How do I set up a formula that will write a zero to a range of cells?
I wish to click on a radio button or such to effect this action.


Post your answer or comment

comments powered by Disqus
Hello,
I know that this is going to seem really elementary to someone but I could really use some help evaluating the following code. When it comes to VBA I'm a hack, in other words I keep working at it until something works. That being said I've been working on the following code and I keep getting a mistype error when it starts to loop through the cells. Basically what I want the code to do is evaluate the used range in a column for specific values and if those values show up they are to be erased otherwise I want all the other values to be left alone. The only catch is that if the case "R3" shows up on a particular worksheet it is supposed to calculate the amount of days between the date listed in Column "B" and the current date. If it is over six months or about 175 days it clears the contents of the cell. If it is not over six months it leaves the value and goes on to the next cell. Any help is appreciate, here is my code:

	VB:
	
 ClearClassButton() 
    Select Case ActiveSheet.Name 
    Case "Camption Resident Complaint Log" 
        Columns("M:M").Select 
        Selection.ClearContents 
        Range("M1").Select 
        ActiveCell.Value = "CLASS" 
        Range("M4").Select 
        ActiveCell.Value = "CLASS" 
    Case "Timberlane Securitas Log" 
        Set ClassRange = Columns("K") 
        For Each Cell In ClassRange 
            Select Case Cell.Value 
            Case "R3" 
                Date1 = Sheets("Timberlane Securitas Log").Range("B" & Cell.Row).Value 
                Date2 = Date 
                If DateDiff("d", Date1, Date2) > 175 Then 
                    With Cell 
                        .ClearContents 
                    End With 
                End If 
            Case "R1", "R2", "G1", "G2", "G3" 
                With Cell 
                    .ClearContents 
                End With 
            End Select 
        Next Cell 
    Case "Campton Securitas Log", "San Tropico Securitas Log", "Villarrica Securitas Log" 
        Columns("K").Select 
        Selection.ClearContents 
        Range("K1").Select 
        ActiveCell.Value = "CLASS" 
        Range("K1").Select 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank You

I have a column B1 to B? with one of a possible 5 string
values in each cell. I wish to evaluate the value in the
B-cell and depending on that value is then change the
color of font in the corresponding cell in column C.

Note: B? means Column B is of variable length, could be
30 and next day could be 40.

Thanks in advance

I have a column in excel and I want to evaluate each cell in that column and if the cell.value = a specified value I would like for it to copy and paste it in the cell to the left. Below is the code I am using but it isn't working.... any help is appreciated..

Sub Format_Unbilled_Report()

Dim rngcell As Range
Dim rngRange As Range



Range("A:A").Insert xlShiftToRight

Set rngRange = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))



For Each rngcell In rngRange



If rngcell.Value = "Shoals" Then
rngcell.Copy(ActiveCell.Offset(0, -1)).Paste





End If



Next rngcell



End Sub


In an excel spreadsheet I have live data from an OPC Server at Cells C1 & D1, the following code evaluates the value of D1 (the bag number) and copies the value of C1 (an actual weight value) to a corresponding row/bag number on the sheet. The value in D1 will increment as each bag goes into the system and is weighed. The code below works, in that it copies the values into the correct place, but it doesn't trigger automatically. I've tried the worksheet_change event, but since the value is driven by the OPC server it doesn't seem to trigger the worksheet_change event. Any suggestions?


	VB:
	
 
Dim CellAddress As String 
 
RowNumber = Range("D1").Value 
 
Range("C1").Select 
Selection.Copy 
CellAddress = ("B" + CStr(RowNumber)) 
Range(CellAddress).Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

I am writing some code to extract range values from a sheet based on certain criteria.

Because of the structure of the sheet, I want to check whether a specific cell's value (relative to the main cell being evaluated) is 0 or blank; if so, then this cell's row can be ignored.

I have the following code:


	VB:
	
 chkRng 
    If cell.Offset(0, -3).Value  "" Or cell.Offset(0, -3).Value  0 Then 
        If cell.Value >= 100 Or cell.Offset(0, 1).Value >= 50 Then 
             'Do nothing
        Else 
             '...  copy specific cells
        End If 
    End If 
Next cell 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code correctly ignores blank cells but is copying over cells with a value of 0. The cells in question have a formula which is a direct reference to a cell on a separate sheet (e.g. Sheet2!B100). The referenced cell (ie Sheet2!B100) is blank, and therefore the cell I am evaluating has a 0 value.

Any ideas? I'm sure this is a rookie mistake but any guidance would be appreciated!

Cheers

Chris

I have a template MS Word Document that has approximately 65 embedded Excel worksheets. Each worksheet performs a VLOOKUP to evaluate specific conditions based on a risk and threat matrix. Each worksheet is a unique sheet that is relative only to the proceeding paragraph. The last visible field calculates a composite score based on the average of all VLOOKUP results.

I would like to be able to have a final worksheet at the end of the document that calculates the total number of each grading criteria as a summary. I am having great difficulty in referencing values from one embedded worksheet to another.

Specifically – If an embedded worksheet contains a cell value, how is it possible to reference that cell value in another embedded worksheet in the same document?

I had hoped not to have a Word Document and an Excel document, but simply a Word document. This is because this report will go through multiple “Hands” during its critique and revision. I need the functionality of sending only a single “All inclusive document”

If I have chosen an improper method to accomplish this process, please inform me of that as well.

Many thanks,

Jared

Howdy. I have a worksheet that is filled out 1 row at a time over a period of a year. After a few weeks, the row of data diappears off the bottom of the screen and one must scroll to get to the first available blank cell. Currently I use a custom view to start at the same spot each time the sheet is accessed.

I need some code that will evaluate a cell value (say 40) and do a small scroll by the amount shown in the cell value.

Thanks.

louis

Hi,

I have a number of named ranges that I need to find/replace data in.

For example, in range "PAPKGC" there are three variables: F, P, and X. For "F", the cell needs to contain the word "Flat". "P" is to contain "Package" and "X" is to be blank. Range "PASIDE" also contains 2 variables. I am to concatenate any cells with a value of 2 with "/pk" and any cells with a value of 1 are to be blank.

So my big question is: How can I use the named ranges to find and replace this data using a VBA macro?

This is what I'm trying, but I have no idea what I'm doing.
Sub ReplaceText()
    Dim rngData As Range, rngCell As Range
         
    With ThisWorkbook
        Set rngData = Range("PAPKGC")
    End With
     'define the data range to evaluate
     
    For Each rngCell In rngData
        Select Case rngCell.Value
            Case "F"
                cell.Value = "Flat"
            Case "P"
                cell.Value = "Package"
        End Select
    Next rngCell
End Sub
Any help is appreciated!

-G

Greetings,

I have a particular cell (target) that needs to use one of four formulas depending on the value contained in another cell (radio button linked), the way a SELECT CASE statement would work. Each formula will be simple (something like 3 or 4 cell values multiplied together), but different. I don't want to use 4 embedded IF statements as the the formula in the target cell would get too lengthy.

I was hoping to store the 4 formula as text in a little table, and in the target cell evaluate the one that matches the selection value. I have been fooling around with using INDIRECT but I haven't been able to get it to work as I'd like.

Does anyone have any suggestions?

TIA
WD

Is it possible to build an Excel function completely from cell values in a worksheet?

I have data in three columns. Column one contains a number, column two contains a comparison operator (i.e. <, >, <=, >=, or =) and column three contains another number. I would like to be able to dynamically build a function to combine the data in all three columns and evaluate it to either True or False. I have not been able to get Excel to see column 2 as a comparison operator.

For example:
Column 1 | Column 2 | Column 3
9 >= 2
55 < 33
42 = 15

The first row should evaluate to TRUE, while row two and three evaluate to FALSE.

Thank you for any help you can provide.

I want to come up with an easy to have my Excel file (on open) evaluate the
first row of data and hide the columns where there is none yet (future
months of data will fill in later and then auto unhide).

If possible it would be even better to evaluate the column and make sure Max
of cells is 0 then I know that no cell in the whole column is filled in.
Then if it is 0, hide if not 0 unhide.

Just don't know the syntax.

Tried...

Sub HideAndSeek()
'
' HideAndSeek Macro
'
If Range("H4").Value2 Is Null Then

Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Else
Columns("H:H").Select
Selection.EntireColumn.Hidden = False
End If

End Sub

But get errors on the way I try to evaluate the cell value.

Any help would be greatly appreciated.

c-

I am trying to help a friend create a form for use in employee evaluations, wherein the supervisor can answer a quick question from a drop down list, which will then assign a point value. As an example:

"Question One: How Long has the employee been in their current position?"

The drop down list then gives the options "Less then a year", "One to two year", and so on.

From there I used the following formula to calculate the point value to assign (D6 is the answer field):

=IF(D6="Less then a year",1,IF(D6="One to two years",2,IF(D6="Two to four years",3,IF(D6="Four to six years",4,IF(D6="Greater then six years",5,IF(D6="","0"))))))

The issue is that the need to be able to change the questions and answers based upon the persons job, so I would like to use the cell values from the drop down list to populate the quoted text in the above formula. For example, below is my list for the sample question above:

How long has the applicant been in their current position?
Less then a year
One to two years
Two to four years
Four to six years
Greater then six years

If "Less then a year" is in cell B2, how would I call out the value contained within that specific cell in the above formula? Is there a function I could be using besides IF that would be more effective? My understanding is that the function requires specific quoted text, which is functional for this need but would be very tedious moving forward.

Any help would be apreciated, and if more information is needed please let me know, I have been staring at this for a few hours so I am sure I left something out.

Hi all,

First time poster!! Here is my situation...

I have a workbook with 6 different tabs of data. I need to find out if value in cell D of sheet A matches any cell value in any of the cells of the other tabs. If a match is found, I then need to copy the contents of corresponding cell in column J of Sheet B into column G of Sheet A.

Can anyone help?

Thx..

I am totally new to excel macro

I get code from one of the old forum but really don't know how to make it work in my spreadsheet.
after I cut and paste this in MS visual basic, and try to run it, it keep ask me for the macro name, when I enter test as a name
it will create
Sub test()

End Sub
on top of the function and the function do not work.
what do I miss, or is there any better way to make a sound alert when the cell > certain number
thanks.

here is the code that I get:

Playing a Sound Based on a Cell's Value

Some people like audio feedback. For example, you might want to hear a sound when the value in a particular cell exceeds a certain value. Excel does not support this feature, but it's fairly easy to implement with a custom worksheet function that uses a Windows API function.

For general information about playing a sound file from Excel, click here.
The Alarm function

Copy the code below to a VBA module in your workbook.

'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

NOTE: The Alarm function expects a WAV file (named sound.wav) in the same path as the workbook. You will need to change this statement to match the name (and path) of your actual sound file. If the sound file is not found, the default system sound will be used.
Using the Alarm function in a formula

The Alarm function monitors a cell for a specified condition. If the condition is met, the sound file is played and the function returns TRUE. If the condition is not met, the sound file is not played and the function returns FALSE. The Alarm function takes two arguments:

* Cell: A reference to a single cell (the cell that you are monitoring). Normally, this will be a cell that contains a formula (but that is not required).
* Condition: A text string that describes the condition

Following are examples of formulas that use this function:

=Alarm(A1,">=1000")

The sound will play when the value in cell A1 is greater than or equal to 1,000.

=Alarm(C12,"<0")

The sound will play when the value in cell C12 is negative.
Tips

* The function is evaluated whenever any cell that depends on the reference cell is changed. The sound can get annoying!
* Normally, you will want to use this function in only one cell. If you use it in more than one cell, you will not be able to tell which instance of the function triggered the sound.

Sorry about the title... Hard to describe in a few words.

I'm dragging down a formula (pretty simple operation) but the cell value is going with it.

My formula:
=IF(G15="","",SEARCH(G15,Sheet1!A15))

Quick and easy. Look for x in CELL, return location. In this case, the answer is 3. Perfect.

G16, however, is empty, yet, the answer is still 3. NoGo.

On evaluation:

=IF(G16="","",SEARCH(G16,Sheet1!A16))
=IF(0="","",SEARCH(G16,Sheet1!A16))
=IF(TRUE,"",SEARCH(G16,Sheet1!A16))
=3

What?

Could I get some help troubleshooting? I had it happen on another workbook and I went in to each formula and hit enter and it worked again.

I need to determine a cells value (an ID record item number) within a row being deleted by the user before completion of the 'delete-row' event so that it can use the ID to reference corresponding data in another sheet and delete that also.
Eg. if I have sequencial ID numbering of 1 - 20 in range A1:A20 and then delete row 10 then the ID number of 10 in A10 should be assigned to (IDName) variable.
Ive tried to use the following method but it only returns the resulting target row after the row thats been deleted and returns the incorrect target value
Is there such an event as..
Sub Worksheet_BeforeDeleteRow(ByVal Target As Range)


	VB:
	
 Range) 
    IDName = Cells(Target.Row, 1).Value 
    If Target.Cells.Count = Cells.Columns.Count And IDName  "" Then 
        RemoveOtherRowEntries (IDName) 'passes the ID value to the next routine to delete matching records of the same ID
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


New to VBA:

I am trying to have my code read the a cell value in the active workbook then, based on that cell value, link to a specific page in another workbook.

For example, if the cell value for FrontPanel!E12 = 43532 I'd like to run a macro that will open [Patient ECGs(1.3).xls]43532!A2

Please advise? I'm wicked new to VBA, but the IF function is limited to only 6 levels and I have 6 different values for the cell that need to link to 6 different sheets, so the only way I figure I can make it work is through a VBA macro. Thank you in advance!

Hi

I tried Google but could not find an answer: I want to get a cell value from a sheet which name is in a cell.
wsName returns the correct value of the cell.

But my code returns an error


	VB:
	
Cells(LASTrow, 1).Value = Sheets("Main").Cells(i, 1).Value            Cells(LASTrow, 2).Value = Sheets("Main").Cells(i,
3).Value 
Cells(LASTrow, 3).Value = Sheets("Main").Cells(i, 4).Value 
wsName = Cells(LASTrow, 3).Value 'this is value is correct
Cells(LASTrow, 4).Value = Sheets("Main").Cells(i, 5).Value 
Cells(LASTrow, 5).Value = Sheets("Main").Cells(i, 6).Value 
Cells(LASTrow, 6).Value = Sheets(" & wsName & ").Range("K13").Value 'error is here

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help !

Good day,

Please see below code.

Here's where I need help:

Instead of highlighting that one cell, I'd like the whole row to highlight, but only between columns A and L. I might actually want to specify certain columns.... ex. A, D and F... If that's also possible.

Thank!


	VB:
	
 Range) 
    Set StationColor = Range("I9:I90") 
     
    For Each Cell In StationColor 
         
        If Left(Cell.Value, 1) = "1" Then 
            ActiveSheet.Unprotect ("1234") 
            Cell.Interior.ColorIndex = 35 
            ActiveSheet.Protect ("1234") 
        End If 
         
        If Left(Cell.Value, 1) = "2" Then 
            ActiveSheet.Unprotect ("1234") 
            Cell.Interior.ColorIndex = 40 
            ActiveSheet.Protect ("1234") 
        End If 
         
        If Left(Cell.Value, 1) = "4" Then 
            ActiveSheet.Unprotect ("1234") 
            Cell.Interior.ColorIndex = 36 
            ActiveSheet.Protect ("1234") 
        End If 
         
        If Left(Cell.Value, 1) = "5" Then 
            ActiveSheet.Unprotect ("1234") 
            Cell.Interior.ColorIndex = 34 
            ActiveSheet.Protect ("1234") 
        End If 
         
        If IsEmpty(Cell.Value) Then 
            ActiveSheet.Unprotect ("1234") 
            Cell.Interior.ColorIndex = 2 
            ActiveSheet.Protect ("1234") 
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have the following code to send email through vba :


	VB:
	
 sendmail() 
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim cell As Range 
    Application.ScreenUpdating = False 
    Set OutApp = CreateObject("Outlook.Application") 
    On Error Goto cleanup 
    For Each cell In Columns("d").Cells.SpecialCells(xlCellTypeConstants) 
        If cell.Value Like "?*@?*.?*" And _ 
        LCase(Cells(cell.Row, "e").Value) = "yes" Then 
            Set OutMail = OutApp.CreateItem(0) 
            On Error Resume Next 
            With OutMail 
                .To = cell.Value 
                .Subject = "test" 
                .Body = "This is a test" 
                 'You can add files also like this
                .Attachments.Add (filepathstring) 
                .Send 'Or use Display
            End With 
            On Error Goto 0 
            Set OutMail = Nothing 
        End If 
    Next cell 
cleanup: 
    Set OutApp = Nothing 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The thing is within my list of email, the same email can be there more than once, but I would like to only send one email to that person.

How do you modify the for each loop code so that if the cell.Value in column D was already "analyzed", then skip it.

Thank you for your help.

Hello,

With thanks to Pike, there is a form where a selection of a combobox returns a corresponding cell value in a textbox.

What is the solution to change the value of the corresponding cell into the selected value of a second combobox?

Thanks in advance,

aisietie

I need to hide different rows based on a cell value.

The target cell will always be the same, but there are eight different values in the drop down list in the cell.

I am using Worksheet Change in VBA but it is not working
Note: A user may select a value from the drop down list in the Target Cell, but may then change their mind and select another value.
I need only the rows hidden for the selection chosen in the drop down list.
All other rows should be shown.
Following is a sample of code I am trying to use


	VB:
	
 Range) 
    If Target.Address = "$L$22" Then 
        Rows("26:36").Hidden = (Target.Value = "NEW PHONE & MOBILE NUMBER") 
        Rows("24:32").Hidden = (Target.Value = "DATACARD FOR LAPTOP") 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When selecting NEW PHONE & MOBILE NUMBER, rows 26:32 are hidden as expected, but if I then change the selection in the target cell to DATACARD FOR LAPTOP only rows 24:26 are hidden. Rows 27:32 are not hidden

I have researched this site and own a couple books on VBA coding for Excel, but I still cannot figure out how to pull a cell value into a comment. Here is my latest of multiple attempts with examples of each attempt. The macro completes but none of them pull the values I need.


	VB:
	
Range("D36").Select 
Range("D36").AddComment 
Range("D36").comment.Visible = False 
With Range("D36").comment.Shape.TextFrame.Characters.Font 
    .Name = "Arial" 
    .Size = 10 
    .Bold = False 
End With 
With Range("D36").comment.Shape 
    .Height = 175 
    .Width = 500 
End With 
 
Range("D36").comment.Text Text:= _ 
"1. 13 (10%) - CAT/iCAT: Issues with BUSPROD most likely had an effect on multiple jobs. Latency/Freezing issues were causing
the system to shutdown." _ 
& Chr(10) & "" & Chr(10) & "** no other groupings **" & Chr(10) & "" & Chr(10) & _ 
"- existing tickets at beginning of the week: " & Range("B25").Value & Chr(10) & _ 
"- new tickets received during the week: " & [B26].Value & Chr(10) & _ 
"- tickets resolved : " & Cells(27, 2).Value & Chr(10) & _ 
"- tickets routed to other queues after analysis: " & Worksheets(4).Range("B28").Value & Chr(10) & _ 
"- Resolved vs Routed Ratio: " & Sheets(4).Range("B29").Value & Chr(10) & _ 
"- tickets in WIP as of end of day Sunday: " & Range("B30").Value, Start:=400 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have pulled the data into Sheet3 from Sheet1 using excel formulae.Sheet1 Contains Provision for Names in Range A2:A52 (Provision fior 50 Names). Sheet3 has about 50 pages Formated depending on the data in Sheet1. Every Page in Sheet3 has same format with 29 Rows and 26 Columns. If there are only 30 Names in Sheet1, then the data in Sheet3 for the balance 20 are blank or zero.
Every page in Sheet3 has a link formula to abstract the name from Sheet1.Intersection of Row 3 and Column 16 in Every Pagen in Sheet 3 has the Name abstracted from Sheet1. If Sheet1 has no Name, this Row3 and Column 16 has Zero Value.
My problem is How can I use VBA in Excel 2007 to Print only those Ranges (Row1 to Column16) in Every Page in Sheet3 In Which the Cell Value (Row3,Column16) is Not Zero. How can I use a Command Button in Sheet3 to accomplish this task.
loknath

Hi
I have currently this code running:


	VB:
	
 Range) 
    ActiveSheet.AutoFilterMode = False 
    Range("A7:EY9999").AutoFilter 
    Range("A7:EY7").AutoFilter Field:=Range("F3"), Criteria1:="=*" & Range("E4") & "*" 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What happens is that everytime any cell value in the worksheet changes the above macro runs. is there anyway to get that macro to run, only when the value in E4 changes, and not when any other cell value changes as well?

Thank you
Jay


No luck finding an answer? You could always try Google.