Go To First Cell In List That Matches Contents Of Active Cell

Feb 20, 2007

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 9 Replies


ADVERTISEMENT

Cell Return All Matches From A List And Have The List Of Matches Reduce As You Type

Jan 28, 2009

is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?

View 3 Replies View Related

Cell Contents Dependent On Active Cell?

Nov 9, 2012

I have a list of items in cells b2 to z2. I want to display the contents of those cells in b2 dependent on what the active cell is. For example if the active cell is in column b I want a2 to show b2. If the active cell is in column C I want A2 to show C2, etc.

View 3 Replies View Related

Find Text Based On Active Cell Contents

May 11, 2007

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 3 Replies View Related

Macro To Jump To Cell That Matches Text Selected From A Dropdown List?

Mar 24, 2014

I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.

View 4 Replies View Related

How To Get The Contents Of A Drop Down List To A Cell

Jan 7, 2009

I need to get the contents of a drop down list to a certain cell. I have figured out that if i highlite the drop down list and put "=A1" in the formula bar that it kinda works. But instead of putting the words in the cell, it puts how many rows down in the drop down box the item selected is.

View 9 Replies View Related

Excel 2003 :: Highlight Cell On Same Row As Active.cell Whenever Active.cell Changes

Dec 10, 2012

whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.

This action should be restricted to one sheet in the workbook.

It's for Excel 2003.

View 3 Replies View Related

Cell Contents Based On Drop Down List

Jan 6, 2006

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 7 Replies View Related

Remove List Item And Cell Contents, Then Shift Up

Oct 28, 2009

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 9 Replies View Related

Selecting Range From Active Cell To Last Entry In Same Row As Active Cell

Jun 8, 2014

I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.

The code I am using is:

[Code] .....

The code that is not working and bringing up an error is:

[Code] .....

Attached File : Copy of Testexample.xlsm‎

View 2 Replies View Related

Return Matches & Non-Matches From Delimited Cells Against List Range

Jan 29, 2010

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 4 Replies View Related

Cell Formatting - Red Text If Cell Matches Certain Condition Based On Another Cell

Sep 24, 2013

I have one column that contains a monetary amount (column AQ) , and another that contains text reading either "inflows" or "outflows" (column AC)

When AC says "inflows", AQ should be positive, and when AC says "Outflows", AQ should be negative.

I need the text in column AQ (the monetary amount) to become red when the the opposite is true.

i.e. When AQ is negative and AC says "Inflows", AQ should become red. And when AQ is positive and AC says "Outflows", AQ should become red.

View 2 Replies View Related

Return Contents Of 1 Cell Based On Contents Of Another Cell?

Mar 12, 2014

I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.

View 3 Replies View Related

Copy / Insert Row In Sheet3 If Cell Value In Sheet2 Matches Cell Value In Sheet1

Sep 17, 2013

I am working a project where I need to copy/insert a Row of data onto Sheet 3 if values from Sheet2 are located on Sheet1. Here is my code so far:

Option Explicit
Sub move_rows()
Dim ws As Worksheet
Dim ws1 As Worksheet

[Code].....

View 3 Replies View Related

Testing Contents Of 2 Cells - Ignoring Partial Matches

Jul 6, 2012

I have two columns, some of the cells in these columns contain more than one value separated by a space. Example:

Col A Col B
1.99 1.69
39.95 6 119.94 29.99 6 149.94
135 250 135.00 250.00
11.6 11.60

What I am trying to achieve is to test that the values in column A match those in column B and have a TRUE/FALSE response in column C. At present i'm just using =a=b, however I want the formula to give me a TRUE response for the third and fourth lines in my example, as I want it to ignore the zero after the decimal place, at present it gives me FALSE.

So the results I want to see in col C for the above example are:
FALSE
FALSE
TRUE
TRUE

View 4 Replies View Related

Compare Cell Contents With Given Range Of Contents

Dec 30, 2013

I have attached a workbook stating my problem.

file1.xlsx‎

View 10 Replies View Related

How To Return First Non-blank Cell That Matches Another Cell

Jul 24, 2013

I would like to return in column E if the person purchased another package before using all their product up. There are multiple accounts in column A that has a similar set. I need a formula that keeps the used date unique to the account # as well.

Account #
Unique
Purchased
Amount
Used
1561645994
1234

[code]....

View 2 Replies View Related

VBA - Selecting Active Cell And Working Back To Specific Cell Range?

Jul 2, 2014

I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)

Code:
For Each c In Range("Q2:AC2").Cells
If c = period Then
c.Select

[Code]...

The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.

View 5 Replies View Related

Assign Active Cell In Macro With Dynamic Cell Choice

Dec 29, 2009

Before unloading a userform the range to select the active cell is set to

View 3 Replies View Related

Copy Cell Contents Between Tabs Provided Cell References Match (VLOOKUP)

Apr 29, 2014

I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.

For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.

I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.

Screenshots for reference

First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]

View 2 Replies View Related

Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents

Oct 15, 2008

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 5 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Double Lookup To Retrieve The Contents Of One Cell And Put Into The Main Sheets Cell?

Apr 18, 2013

I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.

Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG

View 4 Replies View Related

Excel 2010 :: Cell Summing Through Referencing Neighbor Cell Contents?

Feb 12, 2014

we have a Excel 2010 worksheet containing multiple tables.

Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10

Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17

On a separate worksheet we want to calculate with the first worksheet's values.

For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange

How do we do such a calculation/formula?

View 4 Replies View Related

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

Jun 1, 2014

i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows

View 3 Replies View Related

Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell

Apr 30, 2009

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 5 Replies View Related

Replace Cell Contents Based On Matching Data In Another Cell (On Another Sheet)?

Apr 28, 2014

I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.

I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?

In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.

View 5 Replies View Related

Lock / Unlock Excel Cell Based On Contents Of Another Cell?

Sep 12, 2013

How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?

View 1 Replies View Related

Setup Dependent Cell Based On Contents Of Independent Cell

Oct 20, 2013

I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)

If A is blank, B should be blank

If A has any value other than X, B should display a set string Y

If A contains X, B should contain a drop down list with a range of choices.

I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.

View 2 Replies View Related

Excel 2010 :: Add Comment To Cell Without Changing Cell's Contents?

Mar 13, 2012

How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..

(using excel 2010)

View 2 Replies View Related







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