Take Action Based On String Value In Selected Cells

Mar 22, 2007

I am trying to take some action based on the string values in selected cells. Here is my code to iterate through selected cells and count the occurances of certain string values:

Private Sub TakeAction()
Dim cel As Range
Dim sel As Range
Dim complete As Integer 'a counter for completed
Dim pending As Integer ' a counter for pending
Dim cancelled As Integer ' a counter for cancelled
Set sel = selection
For Each cel In sel
If cel.Text = "Some text" Then
complete = Complete +1
If cel. Text = "Some other text" Then
pending = pending +1
If cel.Test = "Yet some other text"
cancelled = cancelled +1
End If
End If
End If
Next cel

MsgBox complete
MsgBox pending
MsgBox cancelled
End Sub

View 2 Replies


ADVERTISEMENT

Action Based On Individual Values In String

Aug 10, 2006

I need to read a binary value and trigger an action based on whether the each value is 1 or 0.

So for binary 1001

1 switch on
0 switch off
0 switch off
1 switch on

Assuming i know how to switch on and off does anyone know how to make the determination based on individual characters in a binary number string?

View 3 Replies View Related

VBA Change Font Size Of A Character Or Letter Within Any String In Selected Cells

May 15, 2009

Looking for VBA that can change the font size of "•" char(0149) within any string in selected cells.

Also looking to delete the last "•" char(0149) within any string of selected cells.

What would the syntax for these two functions be?

View 9 Replies View Related

Copy Selected Cells Of Rows To Another Worksheet Based On A Condition

Feb 4, 2010

I am self-taught (arguably by a pretty shoddy teacher), and am determined to figure *some* of this stuff out. I need a code to look at the rows on one worksheet and based on the value of column A for each row, copy cells A through E in that row to cells A through E of a blank row on a corresponding worksheet. I found a code from another post that does just what I need it to with one small exception. The code was posted by DaveGuggs and is as follows:

View 2 Replies View Related

Take Action Based On Change In Cell Contents

Dec 18, 2008

I have a range of cells that I want to watch for a change in contents. If any cell in the range has a change in contents I want to launch a MessageBox that asks if they wish the new value to be entered automatically in all of the other cells in the range. Example:
Range = B8:M9

User changes F8 from "empty" to 38. MessageBox should launch asking if they want 38 to be entered into all of the cells in the range. Upon answering yes new contents of F8 should be inserted into the rest of the range.

View 4 Replies View Related

Copy Selected Cells From Sheet1 And Paste Them In Sheet2 Based On A Cell Value?

Dec 27, 2012

have two worksheets, "Entry form" and "Database" in my workbook. I am trying to put together a macro button to find the cell value D5(Entry form) in the column A:A (database), if found, copy selected cells (B5:D5,B7,B9) from entry form and paste in the adjacent cells of the row with the value in the database sheet.

VB:
'Match value D3 and replace data
Dim sht As Worksheet, outsht As Worksheet, r As Long
Dim rfoundCell As Range

[Code].....

View 9 Replies View Related

Perform Action Based On Choice From Validation List

Sep 22, 2009

In all the cells in column F I have a validation list with several predefined choices, but at the bottom of this list I want an "Other..." option, which when chosen, enables the user to enter a new string in that cell if none of the predefined did not match.

View 7 Replies View Related

Take An Action On Only Cells With Content Within A Range

Jul 16, 2009

I am trying to write a macro that goes down column A, and whereever there is cell content, it will copy the row of the table to another spreadsheet.

The problem I am encounting is that there are gaps in the table, for example:

WKJ9900KBKBusiness Banking: Cheque CollectedWBJ90978JKCommercial Banking Corporate Heritage: Cheque CollectedUHN98920HJCorporate: Cheque CollectedUCC1201DBKCredit Cards: Cheque Collected

WHH8989JKBusiness Banking: Cheque Collected Unidentified / ReconciliationWKU8987GHCommercial Banking Corporate Heritage: Cheque Collected Unidentified / Reconciliation

WKJ9900KBKBusiness Banking: Cheque CollectedWBJ90978JKCommercial Banking Corporate Heritage: Cheque CollectedUHN98920HJCorporate: Cheque CollectedUCC1201DBKCredit Cards: Cheque Collected.

The gaps are of varying length. So far I have been playing with the
Range("a1").Select
ActiveCell.Offset(1, 0).Select
Do While Not IsEmpty(ActiveCell)
'do copy action
ActiveCell.Offset(1, 0).Select
Loop
of course, this stops when it reaches a blank cell.

I think the best way to do it is if I set a range, and then get excel to go down the column and copy whereever there is cell content.

View 9 Replies View Related

Select Based On Partial String Then Delete Based On Full String?

Jun 13, 2013

Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.

Here is the format of the report at this point. Names have been changed to protect the innocent.

Agent: 2366 Bacon, Kevin

Date
Start
End

[Code].....

So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.

I know what I want to do...but not shure on the code cause I am not great at VBA.

Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.

View 6 Replies View Related

Excel 2007 :: Lock / Unlock Cells Based On Values Selected In Dropdown List

May 15, 2013

I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.

I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!

View 1 Replies View Related

Skip SpecialCells Action If There Are No Blank Cells

Aug 21, 2008

I am trying to run an action on a series of cells that inserts rows whenever there are blank cells (resulting from the formula) in column.

The problem: When I have a situation where there are not blank cells, the the .SpecialCells action does not work. I have tried the On Error Goto, but I have multiple equations that can have this occur and I have only been able to use that feature once in a macro.

LR = ActiveSheet.Range("B65536").End(xlUp).Row
Set Rng = Range("A2:A" & LR)
With Rng
.FormulaR1C1 = "=IF(OR(AND(RC[16]="""",R[-1]C[16]=1),AND(R[1]C[16]="""",RC[16]=1)),"""",1)"
End With
Set Rng = Range("A:A")
With Rng
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Insert 'stopped here
End With

View 9 Replies View Related

Move Row To Another Sheet Based On Action Words Chosen From Dropdown Menu

Feb 7, 2013

I have a excel file to keep track of sales pipeline. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows once a task is completed and can be moved to another sheet. In Prospect sheet I have a column for " sales stage" and here you have to select from a drop down menu, either "Prospect", "semi-qualifed" "Pre-qualified" "verbal" "closed-won" and Lost. What I would like, is that once you have selected one of the sales stage, the entire row or entry, will be automatically moved to a new sheet according to each stage.

View 9 Replies View Related

Returning String From One Cell Based Upon Multiple Cells

May 22, 2013

I need to return the specific text of a cell based upon the text in multiple cells.

Worksheet as follows:

Asphalt Sub:
BLDG1-Backwash 1
*BLDG1*
*Sub*
BLDG1-Backwash 1 OverEx
*BLDG2*
*Sub*

[Code].....

I have three columns thus far. I need to add another column and search all items in Column A using the criteria in Columns B and C. For instance I need to search for BLDG1-Backwash Sub: and place it into column D using the criteria in Columns B and C.

If possible, I would also like to remove both the words, BLDG1 and Sub from the result so then it only shows "Backwash".

My spreadsheet is much larger than this and I have muliple buildings I am dealing with, sometimes 30-40.

View 4 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)

[Code]....

When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Assign Specific Number To Cells Based On Another Cell Value In String

Feb 27, 2013

So I have this list (I made it a little bit shorter).

So what you see is two different tasks (01 and 02) and three different conditions (A, B and C). In column B you see the result I would like to have. '/Searchtask_01.html' in A1 belongs to conditions A, because it is in session A. However, '/Searchtask_01.html' in A10 belongs to conditions B, because it is in session B.

How to get the results in B with a formula?

View 1 Replies View Related

VBA Macro To Select Cells With Certain Value And Perform Action On Adjacent Cell

Mar 10, 2014

Converting old reports to usable excel format. I am having trouble using VBA to set up a looping macro that would search Column A for cells with the text string: "ACTIVITY TOTAL". If cell has that value I would like to perform text to column action on the adjacent cell (R0C1). The macro recording for the test to column settings i need is :

Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True

I have attached a sample of what I am currently working with. The actual file has roughly 6000 lines of data, so I would like the macro to go through all lines.

Attached File : LoopingVBA.xlsx‎

View 3 Replies View Related

Forming String From Selected Items

Jun 5, 2009

I have a listing of schools, some records selected with a "Y". When all of the desired schools have been selected, I want to run a macro that will scan through those cells and, for each one that is selected, put the school name into a string. I want that resultant string to be written to a cell.

Here's an example of the data:

View 2 Replies View Related

Function: Selected String Contained Certain Phrases

May 13, 2008

I wrote this function to see if a user selected string contained certain phrases. It keeps returning united states so i am guessing its not cycling through the entire list.

Function region2(searchString As String)
Dim result As String
result = "None"
Dim lng() As String
lng = Array("arabic", "belg", "bul", "czech", "dan", "dut", "dutch", "euro", "finnish", "french", "ger", "greek", "greenland", "hebrew", "hung", "iceland", "international", "ital", "nor", "pol", "portu", "russ", "slov", "spanish", "swe", "swi", "turk", "UK", "united kingdom", "states")


Dim Country() As String..........

View 9 Replies View Related

Generating Random Number String Based On Alphanumeric String?

Aug 14, 2014

Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?

Example;

Cell A1 has 123XVF1234
Cell A2 has 321AFW4321

In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value

Example;

If B1 returns 643562 it would need to be able to be converted back to 123XVF1234

View 7 Replies View Related

Excel Text Search Through Different Types Of Text And Action Based On Text

Oct 14, 2008

1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.

e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".

So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.

View 10 Replies View Related

Changing Cell Value Based On Selected Row.

Jun 8, 2009

Is it possible to select a cell on any given row and the value of a cell on that row for example C5 & C6 are shown in cells A1 and B1 respectively? So if the user moved down the sheet selecting different rows the data would change?

View 4 Replies View Related

Macro Based Off Of Selected Cell

Nov 2, 2012

I am trying to create a macro that runs only if the user has selected a cell in column "D". I want it to fill in the background color of the selected cell and then make the value of column "M", row "whatever row the selection is on" = 1.

How to change the background color, but am unsure how to accomplish the other two tasks.

Code:

Sub Macro1()    
With Selection.Interior        
.ThemeColor = xlThemeColorAccent3
End WithEnd Sub

View 2 Replies View Related

SUMIF Function Based On Row Selected

Apr 22, 2006

I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.

background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.

View 5 Replies View Related

Populate List Based On Two Selected Conditions

Apr 6, 2009

I have a worksheet which contains all the details of the medical equipment in the county that i'm responsible for.

This worksheet posesses amongst others; two columns titled location and sublocation.

I need a technique which will allow me to select a location from a dropdown list, select a sublocation again from a list and for these two conditions to then populate a list containing data such as asset code and description of all the items in the location and sublocation (possibly a little like an advanced filter, but only for the selected conditions)

This list will then be used to provide an engineers report on equipment holdings at various locations. I'm open to any ideas fellas.

View 7 Replies View Related

Show Data Based On Selected Parameters?

Feb 11, 2013

How to autopopulate numbers from data sheet based on Selected month.

View 4 Replies View Related

Deleting Selected Data Based On Cell Value?

Oct 23, 2013

I have attached a sample file.

There are two sheets Stock and BrancnID The stock sheet has a very huge date ( approx 30000 ). The Stock Id of all the records are unique. Every day I get the Stock ID numbers only in excel format from different branches. Every day I have to match the ID numbers received and delete its data in the Stock sheet ( to get an idea I have shown this in sheet3 ).

Is there is any code where I copy paste the ID numbers from the varoius excel sheets into my excel file in the BranchID sheet and the data gets deleted. i.e as I paste the data the record gets deleted but not the Stock ID number. Also the next day when I get new ID numbers and I delete the old ID numbers from the BranchID sheet the data should not re-appear in the Stock sheet.

View 4 Replies View Related

Copy Row Based On Dropdown List Selected?

Jun 10, 2014

I am trying to copy/extract row of data from worksheet(Detail)A:W to worksheet(1st Letter) if X=1st Letter on worksheet(Detail). I have never used VBA and i am not sure if this can be done with a formula.

View 2 Replies View Related

Set A Range Based On Area Selected Using Mouse?

Oct 2, 2009

I'm trying to set a variable for a range based on the cells selected. For example, I want variable "myrange" to be set to range("A1:D14") if I click on cell A1 and drag it to D14. So far everything I've tried ends up getting the good old "1004 - Application defined or object defined error".

View 4 Replies View Related

Displaying Data Based On Selected Worksheet

Dec 21, 2006

An integer in stored in Sheet1!A1. I want a formula in Sheet1!A2 to display data of sheetx!A3 where x is the number chosen in Sheet1!A1.

e.g. Sheet1!A1 store 3. Then Sheet1!A2 will display Sheet3!A3.

e.g. Sheet1!A1 store 7. Then Sheet1!A2 will display Sheet7!A3.

View 10 Replies View Related

Call Macro Based On Selected Cell Value?

Dec 27, 2012

code to do the following:

If any cell in range T75:KH1000 is selected whose value is "Exit", call the macro named View6.

(There are about 40 non-contiguous cells in this range whose value is "Exit".)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved