I need the SUM function. I know how to use SUM and in one of my workbooks it is used quite a few times, but for two columns it will not add the columns up. In the Q column I have this formula throughout the cells: {=IF(AND($H6="DM",$J6=1),"$100.00","")} and the other column is just about the same (just has different H6 value). Then I have a cell with the formula {=SUM(Q6:Q900)} and the end result is $ -. This formula is used for other columns and it works just fine. I have checked the formatting in all the cells and have changed them to General, Number, Currency, and Accounting. None of those working. I have also checked the Calculation Tab in Tools>Options... and it is set to automatic.
I'm dealing with a lot of spreadsheets put together by people who are not that familiar with Excel. Because of this, there are many instances where the formulas created include constants. For example: = A1 * B1 + 200. I want to create a VBA routine that will cycle through the cells I selected and "flag" those cells that have a constant in the formula.
Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?
I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.
If I use functions like FIND, they look at the result of a formula, and not the formula itself.
I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.
I am attempting to write a VBA function that will add textual formatting to each cell that has content. I am just learning VBA; so far, as an example I have
Sub NewSub() i = 1 j = 1 Do Cells(i, j) = "'" & Range(i.j).Value & "'," i = i + 1 Loop Until i = 40 End Sub
but, as I'm sure you'll notice, it does not work. In essence I want to wrap quotes and follow with a comma each cell that contains any content. The actual content test I haven't gotten to yet.
While $ are constants for cells what is the symbol for variables? I guess when I dragged a formula down rows a certain number did not increase so I had to manually up each one as I went down the rows.
In my company we have many spreadsheets with sections that contain formulas, which may be overwritten with "hard coding", manually entered constants, in certain situations.
Is there a way I can detect the differences between the two types of cells? I would like to setup conditional formatting that would highlight constants in a table that would generally only contains formulas.
The range changes each month so I want to use a named cell "LastRow" where I can change the number to replace the 2500. How do I use the range name in my formula without giving me a #value# error?
I am trying to create a set of global variables (or actually constants) that can be used in my program based on two columns found in a spreadsheet. The first column in the spreadsheet contains the name of the variable, and the second column contains its value.
VarName VarValue Var1 abc var2 def var3 ghi
When the program (which is an Add-in) is called by the user, the very first thing that I would like the program to do is to create each variable from the first column and assign the string value from the second column to this variable (or constant).
I have a function in VBA of the type. Function MyFunc(Indx As Integer, k As Long, Rho As Range, A As Range) As Variant .... End Function
which is called as a user-defined function from within the Excel worksheet. When called with the last two arguments being a range (i.e. Result = MyFunc(1,98,A1:A2, B1:B2)) it works fine. However, when I try to directly use an array constant instead of a range (i.e. Result = MyFunc(1,98,{10,11}, {20,30}), it returns a #VALUE error.
I thought I could fix it by redefining the last two arguments as arrays of type double, but this didn't work either (i.e. Function MyFunc(Indx As Integer, k As Long, Rho() As Double, A() As Double) As Variant .... End Function ).
Call LastCell_Example(LastCell) lastrow = Cells(Rows.Count, "B").End(xlUp).row Set Datarange = ActiveSheet.Range("B1:" & LastCell) ' Set to 0 all cells that contain constants, but ONLY those that are numeric Datarange.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
lastcell_example contains
Dim rng As Range
' Use all cells on the sheet Set rng = Sheets("Courtage").Cells
' Find the last cell LastCell = Last(3, rng)
' Select from A1 till the last cell in Rng With rng.Parent .Select .Range("A1", LastCell).Select End With
When I run the code above, I don't understand the fact that the first rows in the sheet AFTER "Courtage" are also being changed to zero.
Obviously, I'm not limiting my datarange correctly, but could someone explain what I need to do to limit the changed data to ONLY the current sheet.
I'd like to add items on a UserForm1 ComboBoxes in the correct order but I can't. That's because with the code below, in ComboBox6P1 the items are added with mixed order:
For Each thing In .Range("AC6", .Range("AC65536").End(xlUp)).SpecialCells(xlCellTypeConstants) If Trim(ComboBox4P1.Text) = thing.Value Then For Each aNew In .Range("AD6", .Range("AD65536").End(xlToRight)).SpecialCells(xlCellTypeConstants) If aNew.Row = thing.Row Then ComboBox6P1.AddItem aNew.Value End If Next Exit For End If Next
I should add items in ComboBox6P1 in order -> AD6, AE6, AF6, AG6, AH6, the rest are empty cells. Instead of it adds AF6, AG6, AH6, AD6, AE6. When I change .SpecialCells(xlCellTypeConstants) to .SpecialCells(xlCellTypeVisible ) it adds it in the correct order but with another 200 emtpy cells in the ComboBox6P1. The format of the cells is General.
I've set up a Named Range that refers to an array of constants, e.g. MyRange ={1,2,3,4,5}. This appears to be fine since I can output the named range to a range of cells in a row (e.g. A1:E1) and see 1,2,3,4,5 (one value in each column).
I'd like to use MyRange as the source for a Data Validation list in a cell.
Normally data validation list sources must be vertical, so I've set MyRange =TRANSPOSE({1,2,3,4,5}). This appears to be fine since I can output the named range to a range of cells in a column (e.g. A1:A5) and see 1,2,3,4,5 (one value in each row).
When I set MyRange as the source for data validation however, it resolves to an error.
why this setup doesn't work?
Workbook detailing the problem attached. Attachment 273788 NamedRangeDataValidationArrayConstansts.xlsx
I am working with a class module and some fields require specific inputs that another coder may not intuitively know. How can I create a list of acceptable conastants that will appear as a drop down when the user is coding with the custom object?
For example: I am creating an Email Engine object that will handle all my emailing needs in code, because coding emails is such a pain. Then I can just create the blasted thing, import the class module where needed and never have to worry about it again. Some coworkers are interested in using the module for their projects when I am finished. I want to put an option for message flags in the object, but only certain colored flags are allowed. How can I get the VBE to show a little drop down with the acceptable flag constants when another user types
[Object Variable].MessageFlagColor = {drop down list of options} I started with
Public Property Let MessageFlagColor(Value As Constants)
I have two sets of data to compare. One is real world data, the other is forecasted data. I have real daily data for every day over about 3 months. The predictions though, there may be 4-6 predictions for ever one real daily value.
What I'd like to do is have a function that looks at Column A's date (real daily value) and Column C's date (predicted data) and if Column C equals Column A, then divide corresponding data in Column B by value in Column D.
In the attached excel file I have two macros that create hyperlinks:
1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).
2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.
In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.
In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?
If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro. Shape color changes as the hyperlinks are selected from sheet1.....
So I have a sheet with 600 or so rows of data on a sheet called "Agent_Summary". On another sheet in the same workbook I have a list 20 peoples names. I am trying to find a way to look at the 600 or so lines on the Agent_Summary in column A and see if the name in that cell matches any of the 20 names from the "Team" Sheet. If the name on Agent_Summary is not found on the "Team" sheet then it clears the contents of that cell and moves on. Then it comes back and Deletes the blank rows. I can make it look for any 1 of the 20 names and the code works fine. How do I turn it into something that will look for any of the 20 names?
Here is my code to look for 1 of the 20 names that works great.
Code: Sub Clean_up_Agent_Summary() Dim Cell As Range Dim R As Long Dim RE As Object Dim Rng As Range Set Rng = Worksheets("Agent_Summary").Range("A5:A600") Set RE = CreateObject("VBScript.RegExp")
[Code]...
"Worksheets("Team").Range("A5")" is where my 1 name is and the rest are just below that. I am thinking some kind of an Array but I am stuck on getting that set up and working right.
i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. I’ve Highlighted in ROW 947 one that should Say NO yet Says YES.
I have two cells which contain text, and I'd like to use IF function to compare between them. Something like: =(IF(A1=B1),"TRUE",FALSE"). But it seems like the function cannot do it with text..
I would like to compare 2 cells, Sheet1!B2 and Sheet1!B3 ( If sheet1!b2 is between the values of "D101" AND "D105", OR EQUAL TO "D201", OR EQUAL TO "D203", OR EQUAL TO "E110", OR EQUAL TO "E111", OR between the values of "H107" AND "H110" OR between the values of "H107" AND "H110" OR between the values of "H203" AND H205 ) AND ( if sheet!b3 is EQUAL TO "E" )
then output a 1, if not, output a zero
The formula, which I deleted, excel reports back as being too long
I want to compare the value of D8 to the value of the last cell in Column D with data. I have the following code but am stuck! (This is code that I was writing from scratch and may not actually work)
Code: Dim myRng As Range Dim C As Range Dim lr As Long
On Error Resume Next
[Code] .........
I am stuck on the "Else" statement...If they don't match then change the color of cell D8 if they do match I want to do nothing.
I want to do is create a function that will allow me to compare various cells. For instance, we have people scoring applications and I want to see if raters' ratings are within 10 points of each other. If raters differ by 10 points I want to print conf call on my spreadsheet (so I can schedule a conference call for raters to discuss the 10 point difference). If raters are within 10 points then I want to print "compute mean" (so I will generate a mean for each applicant).
I can get Excell to compare one rater against another (=IF(C3-C4>10,"conf call","calculate mean"), but I do not know how to make it compare all raters. For example, I have four raters and I want to be able to compare rater 1 to rater 2 and rater 1 to rater 3 and rater 1 to rater 4 and then rater 2 to rater 3 and rater 2 to rater 4; and lastly rater 3 to rater 4. Then if any rater dyad is different by 10 points I want to print our "conf call", but if there are no differences (by 10 points) then I want to print our "compute mean".
I converted a very large PDF to Excel. I want to run a logical formula that will compare a mailing address and property address. My formula goes like this: =if(a1=b1,"yes","no"). However, when I run my formula I am getting False for every line even though I can plainly see the answer should be yes. My guess is that it has something to do with the formatting of the text inside the cells that I'm comparing but they seem to be formatted the same. The strange thing is that if I manually type the information and run the formula, it comes out fine. I have more than 37,000 lines of text so I cannot manually retype the whole thing.
