I have bunch of data in the first column but there is always an identifier in this data at different location within each cell of the column. I am thinking the best way is to use the Find function. If true, then format the whole row.
For example: starting in row 10, I want the macro to look in A10, find "AFL" and if "AFL" is found in A10, then format row 10 by highlighting it yellow. Otherwise, highlight it red.
I saw there were codes to sum or count cells that have a specified Fill Color
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
However it says the changing of a cells fill color will not cause the Custom Function to recalculate, I really need to recalculate the data if there are any changes in the selected range
I set format condition in range by tool bar(=if formula is yes then display cell.interior.colorindex=3), but I have no idea how to find out (.address) the red colour cells are displayed in range with VBA?
View 5 Replies View RelatedI am still a novice in you standards, but have run into a problem and hoping you all will have some insight for me.
I have a workbook that I am parsing data from the first sheet and putting the data onto the second sheet. Before I place the values in I want to format the cells with boarders, colors, alignment, and in some cases validation (but I can worry about the validation later).
Currently what I am doing is using a 3rd page that has a group of cells that have the formats I want in place before I start. The code will then parse the data and when it finds the data I need go to sheet 3 and grab a range of cells and paste those cells to sheet 2 and then write the data over those cells. This allows me to format the document as I create it.
The problem is that I am going through 25k of lines and on average this function is running 40+ minutes. If I run the same script with out the formatting it only takes at most 2 minutes.
So on to the question.
Is there a way that I can start of my script by grabbing all the format information before I parse the data and save it as a variable? Then instead of doing a copy of the format it would just apply the formatting that is saved already. I would think this would be much faster of a process.
I am making some conditional formats for a document. One that I am trying to achieve is have a group of cells have a background color, but once the data has been added, the color go away.
This is what my conditional format looks like: =ISBLANK($A$1:$H$22) and chose the Green fill background. So, my understanding is, the cells should have a green background if they are blank, but go back to a standard format, once cells have data put in them.
I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, "A")).Select
I have a forecast which is sorted by product code by week vertically. I need to create a lookup to re-format this information into a horizontal format by week. I have started this by transposing the information as my attachment shows but I am hoping there is a lookup formula which will be easier and quicker. I have attached the actual document and the data I want to sort is Sheet 2. I have started in Sheet 1. I want to look up the code in column A, then lookup the week number which would be B2 in Sheet 1 and return the value of that Code in that week from Sheet 1.
View 2 Replies View RelatedI want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.
Is it possible to insert a cell refenece into the FIND function.
So, replace JAN-2013 with Sheet1 cell A1
Selection.Find(What:="JAN-2013", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
what I am trying to do but I'll give it my best shot with examples.
A1 = date
B1 = drivefolder[A1.xls]sheetname'!cell
Function for G6 that will look for data in A6 and put results in F6. I have attached a sample woorkbook so you can better see what i am needing.
View 3 Replies View RelatedI have this issue where I get an #N/A error when I i run a VLookup in cell H7. I have posted the worbook.
I want to find the value in column D for the value you choose in cell G2
Excelforum.xlsx
Any way to use a find and replace function involving a cell reference. I have a spreadsheet with ton of junky text burying information that I want. Here is a simplified example
A B
Book 345353hg dgdgsdgfd fff Book belongs to Jim
Sneaker fdg4 Sneaker is worn by Jan
Gum dfg s d e te4345Gum tastes great
Cake jklsjflsjfjikCake smells delicious
The pattern is that the A1 text appears in the B2 text. What I want is
Find *A1
Replace with blank
Find *A2
Replace with blank
and etc.
So that in the end, I get this:
B2
belongs to Jim
is worn by Jan
tastes great
smells delicious
I've looked at the functions of find, replace, substitute, left, and right and I can't seem to find the right one to do what I need.
I am using a macro to find a value in a cell and if found, replace the value in the cell to the left. Thr macro works fine - unless the value I am searching for does not exist. The answer is ( Ithink) an IF statement, but I am having problems with the errors, when I try the IF.
The macro below blows up at the ActiveCell.Offset(0, -1) = "109073X line with an error "Run-time error "1004" - Application-defines or object-defined error.
I've tried a number of things I've seen from this board but have not found a way past the error.
If the value is found, the macro moves to the else statment and stops at the ActiveCell statement with the above error. If the value is not found, then the macro goto NotFound.
Sub Macro2()
'
'Range("A1").Select 'Start from the home cell
Dim ValueFound As Object
Set ValueFound = Cells.Find(What:="Cirrus Reversals/CREDITS")
If ValueFound Is Nothing ThenGoTo NotFoundElseActiveCell.Offset(0, -1) = "109073X"'ActiveCell.FormulaR1C1 = "109073X"End If
NotFound:
End Sub
I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.
Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).
The relevant section of code attached below:
Dim role_count as range
Dim role as string
If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Range("role_count").Select
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value
I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
I have a range of cells, for this example I will use 2.
Cell E17 = 77/170
Cell E18 = 8/9
Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))
This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"
Now if I use:
=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))
It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.
Using the formula:
=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....
Cell A1: International Studies Academy (P) 9-12
Cell A2: Public
Is there a way that I can get Cell A2 to fill red when cell A2="Public" and A1 contains "(P)"?. I can't seem to get a formula to work yet.
1) My data is $42,397.40 is there a way that I can format for it to read $42.4?
I tried ROUND function and that did not work.=ROUND(A1,-3)
2) Cell A5 is Data:1/14/09 - 1/14/09. Since I only wanted the date, I used this formula =RIGHT(sheet1!A5,LEN(sheet1!A5)-FIND("-",Sheet1!A5)) which gave me 1/14/09. I need to set the format to look like 1/14/2009 but the data function would not recognize..
I need a function who can recognize the format below and return TRUE or FALSE. See example.
View 5 Replies View RelatedI want to apply conditional formatting in a cell based on the contents of another cell. I can do it if I just want it based off 1 condition (=b151="sample") but would like to if it met 1 of a couple possibilities.
For example, cell H151 I would like to fill green if cell B151 contained the text "sample" or "example". B151 has a list box with about 10 possibilities but I only want a few of those to turn H151 green.
I need a formulat that will ensure that a cell has 12 numeric characters,a dash, and then 3 more numeric characters. I thought this was a simple formula but I cannot get anything to work.
############-###
I am trying to format this function to just return the year, but I can't seem to get it to work. It just returns the full date and all I need is the year.
DateAdd("yyyy", -1, Date)
when ltoj is less than zero the following code enters a period in the cell instead of zero.
If ltoj < 0 Then ltoj = 0
Cells(r, 10) = Format(ltoj * 24, "##.##") 'writes data in correct format
I would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies View RelatedI am working on a spreadsheet that deals with times such as a work schedule, how every the schedule cells are in a time format, and my if function will only return "check"(which is false). my code works if it remove the time formatted cells but i want to keep the document in its orginal format.
=IF(Mngmt!F16="PH","",IF(Mngmt!F16="OFF ","",IF(Mngmt!F16="VAC ","",IF(Mngmt!F16="8:00:00 AM", "MIKE", "CHECK"))))
The bold and under line portion is where my problem is at.
Its looking to the mngmt tab to find f16 when it equal 8:00:00 AM (scheduled time in time format) but returns ""check"(false).
Var1 is a number, for example (3.2354896), displayed on the worksheet as 3.2
Var2 is a number, for example (3.958846), displayed on the worksheet as 4.0
Var3 is text, for example (lunch program)
BullPoint = "greater satisfaction with Var3 (Var2 vs. Var1)"
Range ("A1") = Application.Substitute(WorksheetFunction.Substitute(WorksheetFunction.Substitute(BullPoint, "Var1", Var1), "Var2", Var2), "Var3", Var3)
The result of this is now:
greater satisfaction with lunch program (3.958846 vs. 3.2354896)
What we want to see is:
greater satisfaction with lunch program (4.0 vs. 3.2)
without losing the ".0" for the 4.0, which happens if we adjust the original data, using ActiveWorkbook.PrecisionAsDisplayed. Our preference is not to use this command, as it changes source data.
I have two worksheets which talk together and one of them gives a time stamp using some VBA code
=format(Now(),"dd/mm/yyyy AM/PM"
in the other spreadsheet I have the Now() function which has been working fine. However, when another user went into the spreadsheet went in it, my calculations between the two dates now error. I am presuming its because of the now function not having some formatting defined so is there anyway of changing this value? The cells-formatting will not obviously work as excel is getting confused between the two time types even though the format looks fine. To help the calculation that is messing up is
=IF($L7="","N",IF(DATEDIF($L7,$J$1,"d")>10,"N","Y"))
whereby L7 is the imported timestamp and J1 is now(). The calculation is to show which accounts become inactive after 10 days.
After reading up on the internet I know that to get round the problem I have to make sure that everyone is using the same regional settings. But I just have a couple of questions:-
1. I can understand the date calculation not working on the other persons machine, but when I went back to look at it I was getting the same error. Even though the file is shared, how come my settings didnt 'overwrite' and how come i was affected?
2. Is there a way to make the code work regardless of who opens it in whatever region? Im not sure if there is a fundamental problem but I cant rely on everyone having the same settings because I know that this will cause problems in the longterm.
3. Using the formuale Now(), is there no way of forcing that to a uk date format to stop the errors?
I have created an excel addin, the purpose of which is to run a piece of code attached below in code section, which iterates to each cell of excel and remove merged cells. I am getting a very basic error as my addin is not able to recognize the workbook which is opened and it returns Nothing for ActiveWorkbook object....
View 9 Replies View RelatedI need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
