Go To First Cell In List That Matches Contents Of Active Cell
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution...
I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust).
I sort the list so that the all names that are the same (ie John Doe) are together.
A short example list looks like this:
Jeremy Apple
John Doe
John Doe
John Doe
Jimmy Kravitz
Jimmy Kravitz
In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it).
Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on.
Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Find Text Based On Active Cell Contents
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View Replies!
View Related
Cell Contents Based On Drop Down List
Can the information in say cell b:1 be based on the information in A:1? Example: If A:1 has the name Fred Smith then I want cell B:1 to say Nurse. Column A will have a drop down list of names and column B will have a list of Nurse, EMT or Paramedic. When someone pick a name from the list the information in column B will automatically be filled in based on the information in column A.
View Replies!
View Related
Remove List Item And Cell Contents, Then Shift Up
how to code the following? I need to remove an item from a list, find its match in column "A", remove the matching cell and then shift the remainder of the column up to remove the empty cell. If this helps, there are 5 different sheet names, and I'm working with range A4:Range("A" & Rows.Count).End(xlUp).Row for the items in the list. To make it as simple as I can: COLUMN A one two three four user clicked on two in the list, so column A now looks like, with no gap between one and three: COLUMN A one three four One sheet actually needs it to do the same removal to identical rows in column B as well (i.e. remove A10 and B10, or A45 and B45, etc.)
View Replies!
View Related
Return Matches & Non-Matches From Delimited Cells Against List Range
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working": 1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List 2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List 3.) Report In Column D: Identify and return all unique values in "Column C" as a List. Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
View Replies!
View Related
Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells. For example: Cell 'A1' starts out blank. Cell 'B1' has a drop down to select specific items. Cell 'C1' is the date of the order and is a locked cell. Cell 'D1' is the date of completion and is a locked cell. The worksheet is protected to prevent direct changes to cells C1 and D1. If a user enters any information in A1, then C1 displays the current date. If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View Replies!
View Related
Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug. Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View Replies!
View Related
Access A Cell's Contents Via A Variable Containing Cell Address??
How can you access a cell's value via a vb variable that contains the cell's address. I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this? e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress??? Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach.
View Replies!
View Related
Clear Contents Of Cell Based On Comparsion Of Cell Values
I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col if eg h60 = h59 then I60 clearContents, Select h59 Else select H59 Do until H3 is selected Psuedocode Select table (A2:I Xldown) Sort Table by CAcode (H Col) Set Range as H3:Xldown Select Last cell with CAcode (Xldown) in H col For every cell in Range (H3:Xldown) Use If/thenIf Last cell = 2nd Last cell (H Col) then Clear contents of I col (last row) select 2nd Last cell (H Col) Else 2nd Last cell (H Col)Next Cell I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work. Sub sort_And_delete_Sumif_amounts() Dim r As Range 'Select range to sort Range("A2:I98").Select 'Sort CAcode in H col ascendingly Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'select range for comparisomn of CAcode Set r = Range("H3", Selection.End(xlDown))................
View Replies!
View Related
Identify Active Cell And Use The Column To Add Formula To Another Cell
I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted. I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank. What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added. In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
Display Contents(text) Of A Cell Referenced By A Given Cell
I want to display the contents of cell B5 in A7 by using cell C7 to show the location of the cell to be displayed. Cell C7 has a formula that shows the cell location of information (text) that needs to be displayed. How can I show in A7 the contents of cell reference shown in C7? In figure attached I show an example to better understand. Want in A7 to show the contents of B5 by reading what is in C7. So A7 cell should show word Road.
View Replies!
View Related
Identify Rows Where A Given Cell Is Equal To The Contents Of The Cell Below
I need to identify rows where the contents of a given cell are equivalent to the cell below. I have managed to get this working based on iterating through the cells and selecting them both for cutting. However it doesn't work effectively: It seems to be leaving blank lines in the original sheet and further fails after the first execution. I would be grateful for any suggestions on a good method to perform the following operations: (1)to compare the cell with the cell below I have used If cell = cell.Offset(1, 0) Then (2)to act on the rows for the two connected rows Sub Test4() ' Loop through rows Dim TheExtract As String Dim cell As Range 'Application. ScreenUpdating = False For Each cell In Worksheets("Sheet 1"). _ Range("A1", Worksheets("Sheet 1").Range("A65536").End(xlUp)) TheExtract = cell If cell = cell.Offset(1, 0) Then cell.Rows("1:2").EntireRow.Select Selection.Cut Sheets("Test").Select ActiveCell.Offset(3, 0).Rows("1:1").EntireRow.Select Selection.Insert Shift:=xlDown End If Next cell 'Application.ScreenUpdating = True End Sub
View Replies!
View Related
Add Active Cell Data To Cell By Clicking
I want to automatically put information from several cells into one cell, when these cells are clicked. Let's say that A1:J8 is the range were a cell can be clicked and if a cell is clicked in that range the value of these cells should be put in cell A9. If possible I want to avoid private subs in a worksheet. I preffer to have a macro running, so I can decide when to stop this routine.
View Replies!
View Related
Cell Contents To Cell Comments With Lookups
I'm working on an attendance spreadsheet and would like to set up an onchange() macro that will do the following: Take the contents of cell in column E, paste them as a comment in a cell found in sheet from F, row from C and column from today's date. Confused yet? this should help, my sheet has the following columns: A Date B Time C Employee Name D Callback number E Reason for absence F Manager Spreadsheet has pages for each manager and I use the formula:
View Replies!
View Related
Showing The Contents Of A Cell Based On Another Cell
one cell gives me the MAX value of a range of cells I want to then use that value to refer back to the range of cells and return the value one column to the left This formula returns "A4" which is the cell number for this result I want to show but it just enters A4 not the contents of cell A4 ="A"&(MATCH(B10,B2:B5,1)+1)
View Replies!
View Related
Determine Cell Content By Other Cell Contents
I am trying to write an "IF" scenerio. I want to be able to control what is put in a cell based what is placed in another cell. For example. I want to be able to only enter a credit/debit in B1 based by what I type in A1. If any cell in column A is any of these numbers (41,47,48,49,50,51,52,60,61,65,68,63,67,69) then any number entered in a cell in column B can only be a positive number. If any cell in column A is any of these numbers (40,42,43,44,45,46,52,53,55,56,57,64,62,66) then any number entered in a cell in column B must be a negative number. So if A1 is 41 then B1 must be positive (debit). If A4 is 42 then B4 must be negative (credit).
View Replies!
View Related
Finding Sheet That Matches A Cell Value
I have worked on the code – with as limited knowledge as I possess – to perhaps communicate my intention. I know for a fact that in the code below there are 2 problematic lines – and herein lies the root of my problem. The first line is: “Dim Day As String” In Debugger speak, I am told that this cannot be done along with “Set sh = Worksheets(Day.Value)”. However, in normal speak, I am trying to copy data from one sheet to another. When pasting the data on the second sheet, I am trying to make the code find the appropriate sheet that matches a Day value in Cell I5. Getting the code to execute this one action is proving to be particularly difficult. I am thankful for any suggestion on how to get the code to work correctly.
View Replies!
View Related
Automatically Tick Cell If Entered Value Matches
I have attached a spreadsheet which, when you enter a matching value in the vehicle column, the cell that matches the vehicle name in both instances (column and row) is ticked. Think I am wanting to use a worksheet_calculate function but cannot figure out how to write the appropriate lookup in VBA. I do not want a formula in the cell.
View Replies!
View Related
Populating Cell Only If Multiple Criteria Matches ...
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments. The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments. I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if: (1) The software title in worksheet two matches the software title in worksheet one (2) The comment cell in worksheet one contains a comment (or text) I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
View Replies!
View Related
Add/Insert Row Below Cell If Value Matches
I have a column O with the heading "Return Visit Required". Each cell, starting in row 5, has data regarding a job. So each row represents a different job. If the cell in a certain row in column O has a value="Yes" then I need 1 row added below that cell. If the value="No" or "" then do nothing. I need the value "Yes" to be non- case sensitive.
View Replies!
View Related
Name 1 Cell Based On Contents Of Another Cell
Step 1 - Find the column named "Date Closed" (it will always be in row 1) Step 2 - Find the column named "Status" (it will always be in row 1) Step 3 - If the "Date Closed" column has a date in it, add text to the cell in the "status" column in the same row that says "Closed". Step 4 - If the "Date Closed" column does NOT have a date in it and is blank, add text to the cell in the "status" column in the same row says "Pending". NOTE: Cell format in the "Date Closed" column is set this way... Number tab; Category is "Date", Type is *6/25/2009 Example 1 below, shows how data currently is...
View Replies!
View Related
Move Contents From Cell D1 To Cell A2.
I have over 6500 rows of data. In Columns A through C for each line I have data for boys name, and then similar data for the girls' name on the same line in columns D through F. I have inserted a blank line after each of the more than 6500 lines because I need to move the data in columns D through F down to the next line and over to columns A through C.
View Replies!
View Related
Use The Contents Of A Cell In A Formula In A Different Cell?
I've got a master workbook which reads from a number of other workbooks. The names and file address of the workbooks it reads from are listed in column A - e.g. A1 contains "C:DocsInfoFile1[sheet1.xls]". I've got a forumla that reads the required information from the workbooks and it looks like this: =('C:DocsInfoFile1[sheet1.xls]Summary'!$B$3)+('C:DocsInfoFile1[sheet2.xls]Summary'!$B$3) What I need to know is how I can replace the address in this formula with the value in column A. There will be many different sheets to read from and they'll change occasionally so I thought it would be easier to have one cell containing the name of the latest relievent sheets the formulas will link to rather than having to update each formula every time.
View Replies!
View Related
Compare Columns Across 2 Workheets Copy Cell Next To Matches
I have this code that compares two columns on two different sheets when it finds a match it then puts the match on that sheet your running it from in my case sheet 2. However i want to change this slightly and im having a hell of a time. I want to match but when tha match happens i want it to copy the cell next to the match. Sub Find_Matches() ...
View Replies!
View Related
Delete Row If Cell Text Matches Known String
I have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried: For i = 1 To 200 range("B" & i).activate If range("B" & i).value = " Total:" Then rows(activecell.row).select selection.delete shift:=xlUp End If Next i When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"
View Replies!
View Related
Delete Rows If Opposite Column Cell Matches
I have a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch! Sub DeleteRepeatedRows() ' goes through coord. sheet and identifies duplicated lines, copies the date from the old row ' copies it into the new line, then deletes the old line Dim rCell As Range For Each rCell In Selection If rCell.Value = rCell.Offset(1, 0).Value Then rCell.EntireRow.Delete End If If rCell.Value = rCell.Offset(2, 0).Value Then rCell.EntireRow.Delete End If.........................................
View Replies!
View Related
Name A Active Cell Which Ever Cell Is Activecell
I need to name a cell that is the active cell. The active cell can be any cell that is selected at the time I need to name it. I can get it to name the cell using the cell address, but I don't know how to change it to the active cell. Below is the code that I tried, but the syntax that I tried to say what cell is to be named simply isn't correct.
View Replies!
View Related
Get Value Of Cell Adjacent To Active Cell
I'm trying to compare two lists of foods, one input by a user (together with number of servings consumed), the other a list of foods with their calorie content. Then I'm trying to get the number of servings associated with the user's input (in a cell adjacent to the one I'm in for the comparison/search), and get the calorie content of that food from the list (also in an adjacent cell), so I can multiply the two to get total calories consumed. However, I'm getting, variously, "Wrong number of args," "Type mismatch," "Application/Object-defined error," and so on. The code below shows what I've tried, and I think I'm doing something braindead basic wrong, but I can't find what my error is. Everything works in the snippet (that is, I've correctly Dimmed the variables, I get my food match, and the row indexes keep up correctly), except the attempt to write, for instance, the number of servings to the variable 'o'. That cell ref fails. m = 7 'initializes the row index of Daily Data block of the Inputs sheet for the food items n = 2 'initializes the row index of the Data sheet for the food items r = 0 'initializes value total carorie content of food item across number of times eaten in a day For Each Food In FoodName 'the User-input food For Each datum In DataList 'table containing food's calorie content If Food.Value = datum.Value Then
View Replies!
View Related
Search List For Matches From Another List
I have a chart of accounts that is probably 30,000 accounts long. I have another list with say 500 accounts. I need to check to see if the 500 accounts exist anywhere in the 30,000 accounts. The next step is, the 30,000 accounts is separated into different categories, so say it's 30 blocks of 1000 accounts. I need to check the 500 list in each of the blocks. I'm just trying to see which accounts are missing from the 30000. So if they exists in each block then all is good, if they don't, we have problems.
View Replies!
View Related
Copy & Paste Cell Value To Date Matches Across Sheets
I need macro to copy values from a Master worksheet on a daily basis, and paste them to multiple individual worksheets in cells adjacent to today's date (already crafted). For instance, a value of 8 is entered into a particular cell in the Master, it is copied and pasted into column E of another worksheet, on the row with today's date already written in column B. I need to do this repeatedly for over 50 worksheets.
View Replies!
View Related
Clear Cell If Number Matches Comma Seperated Numbers
I found code on this site after searching a long time that has worked well in a larger macro I'm writing for use in Excel 2003 usage. However, I'm stumped on how to achieve the following on the attached sample: find and remove the number in A2 from the list in E2 then count the individual numbers seperated by commas in E2 and post the results in G2 Repeat the same sequence for C2, D2 and F2
View Replies!
View Related
Color Table Rows Where Specific Cell In Row Matches Condition
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C. Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row. Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""x""" Selection.FormatConditions(1) . Interior.ColorIndex = 16
View Replies!
View Related
Search A String For A List Of Matches
I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.
View Replies!
View Related
Return A List Of Unique Matches
I need to return all the unique matches from a set of data. For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones. Example of data set I need to lookup in: prod 1 venue1 prod 2 venue 2 prod 1 venue 3 prod 1 venue 10 prod 2 venue 6 I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10. It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....
View Replies!
View Related
See All Cell Contents
Contents of Column C may well spread to over 18 characters, but column is formatted to be this wide - therefore if character length exceeds this, the data spills over into the next column. I don't want to use "Wrap Text" as I want to keep all rows the same height. I have toyed with this macro (which I'm sure came off this forum originally) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("C5:C2200")) Is Nothing Then ActiveWindow.Zoom = 90 Else ActiveWindow.Zoom = 110 End If End Sub I quite like this as I can format Column C to "Shrink To Fit" and then read the potentially tiny font by clicking on the cell What I'd really like though would be to keep the font the standard size for the sheet, but with a click be able to expand the cell to show the contents. I know that a simple cell click will show the contents in the formula bar, but it would be nice to actually work on the cell On a related topic, again in the interests of neatness, is there any way to stop the "spilling over" of text into (in my case) Column D? This column is empty, and I can achieve my aim by putting a space into the topmost cell and then dragging down, but this seems a little messy!
View Replies!
View Related
|
TRACKER
