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.
View 3 RepliesIs 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.
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.
I have the following formula.
=SUMIFS('Register Sales'!$E$51:$C$2500,'Register Sales'!$C$51:,A2,'Register Sales'!$L$51:$L$2500,D2)
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 have an Excel spreadsheet with 3 columns (A, B, and C) and 600 rows. Each cell in A and B contains a 6-diget number. Column C is empty.
I want to determine whether the number in A1, A2, ….A600 appears anywhere in column B. If A(n) does appear in column B then I want to enter a symbol (say a Y) in C(n). If A(n) does not appear in column B then I want to enter a different symbol (say N) in C(n).
What is the formula I enter (and where) that will do that?
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.
View 4 Replies View RelatedI am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies View RelatedI have a list of customer names. These include one-off customers and companies. Unfortunately, they are not always keyed correctly and in the same format. What I need to be able to do is flag any companies. These will be those that contain key words, such as Ltd, Ltd., Limited, PLC, & Co, & Sons and so on.
My customer list may look like this:
1. Bill Jones & Son
2. Mr B Smith
3. Posh Homes Ltd
4. Posh Homes Limited
5. Mr A Singh
6. Bill Jones & Sons
I then need to flag no.'s 1, 3, 4 and 6 as Companies while 2 and 5 are flagged as non-company.
I'm glad founding this great forum. I have learned a lot in the last few months.
Now I need to do a task and can't find online a tutorial for this. Here is what I need:
File A contains 23 columns with info for 300 products (rows). File B contains same columns for 280 out of the 300 products from file A, plus 40 new products.
I need to flag in file A the 20 products that are not in the file B, plus flag in file B the 40 products that are not in the file A.
The structure, columns, etc are the same for both files.
Ideally I'm looking for a way to do this with formula's but for the life of me I just can't think of one. Attached is a sample that explains what I'm trying to achieve.
View 7 Replies View RelatedI am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedCan you declare a const as an array in VBA? For example, let's say I wanted to define an array of ORDINALS:
Const ORDINALS(0 to 9) = ("0th","1st","2nd","3rd","4th","5th","6th","7th","8th","9th")
Can a macro be provided for selecting cells (including hidden) in the active sheet which are :
1. Totally consants ie not used in formula either in same sheet or other sheets
2. Constants ie figures used in formula in the same sheet and/or other sheets
I declared a constant like so:
Public Const iFirstRow As Integer = 6
I figured that any time I used this, at least in the same module, its value would be 6, but it was 0.
I changed it to ModuleName.iFirstRow, which seemed to set it straight.
Is this just how it is, or am I doing something wrong that is preventing me from using it without the module name?
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.
View 4 Replies View RelatedI have a Excel budget spreadsheet. I need a running formula I can copy and paste to other month cells.
SUM=Bal+interest+interest1-pmt
=SUM(B11+B4+C11-D11) Interest(B4), interest1(C11) and pmt(D11) are constants. The formula I am using works but it won't copy and paste.
how do you assign a value to the variable to make it available to the module?
I have tried the following without success
Option Explicit
Dim batch As String
Dim folder As String
Dim title As String
batch = Sheets("Day 1"). Range("D1")
folder = "M:CR dim checksRetention"
title = folder & batch
file = title & ".xls"
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'm wondering if it is possible to declare a constant in an add in, and then reference that constant from workbooks that use that add in?
Basically I want to set a version number in the add in, and check it from the workbook.
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 ).
I have the following code
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 try to understand what the syntax is to create and use an array named constant which would be composed of other constant.
Example :
Constant called AvgZone1 which is =AVERAGE(zone1)
Constant called AvgZone2 which is =AVERAGE(zone2)
and then create a named array constant based on the names : =AvgZone1,AvgZone2
is not working. You cannot for example do a SUM().
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)
End Property
But that hasn't gotten me anywhere.
Macro to clear cells with numbers but no cells with formulas with in this macro:
Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub
The range is where the cells with numbers need to be cleared but not the ones with formulas.
I need to search through a worksheet for a value located in the first A column eg. and then put down a flag ( or bettar the searched text ) to che previous row in B column
Es:
I search the text 'xxx' in A column
if I found the searched text 'xxx' in A5 i need to have a flag or better 'xxx' in B4
Sorry for posting a related question earlier, where I forgot to specify something. I need to find the max in a column of data without doing any filtering or rearrangment of the column -- can this be done, either indicating the max across many columns by highlighting the max box with color or by writing its address in a new box in the bottom?
View 2 Replies View RelatedI am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?
View 7 Replies View Related