Selecting a cell according to the cell value

Hello All VB expert

Need help with a Macro.

I would like to be able to select a cell according to a variable in a cell. 
For example: Cell A1 “31/04/2010”. 

Cell A3 = 31/05/2010
Cell A4 = 30/06/2010
Cell A5 = 31/07/2010
Cell A6 = 31/04/2010

In this case when I run the Marco, it will select Cell A6 because it is the 
same value to Cell A1.

And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the 
same value to Cell A1 

If you need more information or my explanation is not clear please let me 
know.

Thank you very much for your help.

Harn
 

0
Utf
4/28/2010 6:00:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
722 Views

Similar Articles

[PageSpeed] 42

See this page
http://www.ozgrid.com/VBA/find-dates.htm

Sub FindDate()
Dim strdate As String

strdate = Format(Range("A1"), "Short Date")

On Error Resume Next
        Set rcell = Range("A2:A10000").Find _
        (What:=CDate(strdate), After:=Range("A2"), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext _
        , MatchCase:=False)
On Error GoTo 0
    If rcell Is Nothing Then
        MsgBox "Date cannot be found"
    Else
       Application.Goto rcell

    End If

End Sub


-- 
Regards
Dave Hawley
www.ozgrid.com
"Harn88" <Harn88@discussions.microsoft.com> wrote in message 
news:70E1689C-6C34-4A6E-A9B1-0B0AED98AF61@microsoft.com...
> Hello All VB expert
>
> Need help with a Macro.
>
> I would like to be able to select a cell according to a variable in a 
> cell.
> For example: Cell A1 “31/04/2010”.
>
> Cell A3 = 31/05/2010
> Cell A4 = 30/06/2010
> Cell A5 = 31/07/2010
> Cell A6 = 31/04/2010
>
> In this case when I run the Marco, it will select Cell A6 because it is 
> the
> same value to Cell A1.
>
> And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the
> same value to Cell A1
>
> If you need more information or my explanation is not clear please let me
> know.
>
> Thank you very much for your help.
>
> Harn
>
> 

0
ozgrid
4/28/2010 6:11:22 AM
Try the below

Cells.Find(What:=Range("A1").Value, After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Activate

PS: Try recording a macro and edit to suit...

-- 
Jacob (MVP - Excel)


"Harn88" wrote:

> Hello All VB expert
> 
> Need help with a Macro.
> 
> I would like to be able to select a cell according to a variable in a cell. 
> For example: Cell A1 “31/04/2010”. 
> 
> Cell A3 = 31/05/2010
> Cell A4 = 30/06/2010
> Cell A5 = 31/07/2010
> Cell A6 = 31/04/2010
> 
> In this case when I run the Marco, it will select Cell A6 because it is the 
> same value to Cell A1.
> 
> And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the 
> same value to Cell A1 
> 
> If you need more information or my explanation is not clear please let me 
> know.
> 
> Thank you very much for your help.
> 
> Harn
>  
> 
0
Utf
4/28/2010 6:12:06 AM
Reply:

Similar Artilces:

Chart from nonadjacent selections
I am trying to create a chart using nonadjacent selections and the chart wizard does not seem to be representing the data in a way I can understand. It has worked fine for two previous charts - producing a column chart showing a comparison between two sets of figures across a number of weeks. This time the chart wizard seems to be spreading the data out using X categories I cannot understand and not showing the data grouped by week for comparison. Any ideas what I can do? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usen...

Selecting outlook account via code
Hi I am using Outlook 2003 with multiple accounts and when sending mail I select the relevant account from the drop down on toolbar. This is slightly tedious and I wonder if I can have two buttons on tool bar and each selects the correct account and signature on its own. In this regard I was wondering if it is possible to this in code (vba); Select the outlook 2003 account via code. I know how to create a mail item in code but not how to set the relevant account? Thanks Regards I believe there is a specific outlook_vba ng "John" <John@nospam.infovis.co.uk> wrote i...

Adding cells from multiple Worksheets
Good day, I am looking for a way to add cells together from multiple worksheets within a single spreadsheet. The catch is: I want to be able to create the formula to allow me to insert additional worksheets and still come up with the correct total. Is there a way to reference a cell in a non-existent worksheet? Or is there another way to accomplish my goal? TIA - Joe Hi Joe Add two dummy sheets(empty sheets)with the name start as the first sheet and one with the name end as the last sheet of your workbook. this are empty sheets!!! All worksheets between these sheets will be Sum ...

Retrieve value from parameter in bound query
I have a report bound to a query that has a parameter filter on a date field. Is there any way to retrieve the value that the user enters into the parameter dialog box? I've tried accessing the Parameter object from the QueryDef, but the Value property is always empty during Report_Open or Report_Activate (the former occurs before the dialog is presented anyway). I also can't just construct the query SQL with VBA, as the query references multiple queries all with the same named parameter (I like how nested parameters don't require multiple entries by the user if they are ...

Select only one top row from each matching child
Hello, We are moving our project to MSSQL 2005, and I was wondering if there is more efficient way to do this: There is a parent table (think customers) and child table (think event log: evtID, evt_cstID, evtTime, evtAllTheRestOfFields). Application needs to retrieve a list of customers with the most recent event. Of course, I'd like to avoid making separate queries (in application code) to retrieve the TOP 1 ORDER BY evtTime DESC for each customer row. Until now we have been doing this: SELECT * FROM (SELECT *, (SELECT TOP 1 evtID FROM Events WHERE evt_cstID=cstID O...

Help to pick constant color to a value in a Pie Chart?
I have a Pie Chart with 2 items only (R or P). R is a dark color and P is a light color. When There isn't any P in my data, the chart picks the light color for R. I would like to always have a dark color for R and a light color or P. I am doing the chart in MS Access 2000-03 and my row source is: SELECT [RP],Sum([Freq]) AS [SumOfFreq] FROM [Rept#704_Joint_RP] GROUP BY [RP]; Can someone please help me. Thanks, Gary I'm afriad this is the MS Excel Chart forum. You might get more help by posting in the Access Reports forum. -- Best Regards, Luke M *Remember to click...

deleting info within a cell
I would like to delete ' and an s in a set of numbers within a cell how do you efficently do this through out a data set. (example 344'6s = 3446 in cell only) -- jkc Find ' and replace with blank, then again with the s jkc wrote: > I would like to delete ' and an s in a set of numbers within a cell how do > you efficently do this through out a data set. (example 344'6s = 3446 in > cell only) Hi, do you always have ' and s in your data if yes highlight the data, press CTRL H find what enter ', then in replace with don't enter...

Text Too Wide for Cell
If my column widths are all set for, say 12.5, why is it then you type, for example, the following into a cell: New York Jets and New York Giants When I view the workbook, some columns with the above is in a cell, truncates the expression, i.e. all you see is: "New York Jets an" And, at the same time, in a different column, the expression is clearly completely visible, overriding the vertical boundary and appearing well into the next column, in some of the cells, but then truncated in the other cells. What "FORMAT??" allows this dilemma to occur? Thanks, FLKulcha...

Selecting matching items in a column
I need to sort matching numbers from a column. For example, 12345 12.00 23456 10.00 34567 8.00 34567 -8.00 45678 6.50 56789 14.50 56789 -8.25 67890 -1.00 67890 -5.00 78901 -2.00 Result 34567 8.00 34567 -8.00 56789 14.50 56789 -8.25 67890 -1.00 67890 -5.00 One way highlight the range. Data>Sort> HTH "Panajohn" wrote: > I need to sort matching numbers from a column. For example, > > 12345 12.00 > 23456 10.00 > 34567 8.00 > 34567 ...

Excluding Error Values
In an Excel chart I have data linked to an MS Access query. The number of rows and columns for the query may vary. To cover this, I have pasted over a larger area of cells that I am likely to receive rows/columns in Access. I now want to plot this data on a chart. When selecting the data source for the graph as the full potential range of cells from the Access query, the graph plots the #N/A values which result from the cell range being large than the Access query. How can I get the chart to ignore the #N/A values like it can be set to ignore zero values? First, the chart cannot be set...

Changing cell size idependent of the other surrounding cells
How do I change a cell size idependent of the other surrounding cells? Sean, You can change a column width, but not just an individual cell. You could merge 2 cells top get a wider one at that point only, but not exactly what you want. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sean" <meistersean@hotmail.com> wrote in message news:019f01c3bcfb$b539c0a0$a101280a@phx.gbl... > How do I change a cell size idependent of the other > surrounding cells? You don't. Width i...

If "Other" Selected
Hi, I'm relatively new to Access 2003 but have been managing quite well until now. I have created a report and a query but need to filter out certain information. My fields are: Referral Made by: (drop down list) Referral Other: (memo box) Basically I want to be able to filter that if Referral is made by "Other" then the "Referral Other" will be entered in its place. Any help with this would be most appreciated. Paula Sounds like you want the contents of a memo field to be enter in a field that uses a drop down list. Is this the correct? -- KARL DEWEY Build...

Macro to copy active cell
I'm trying to write a macro to copy a row of data that has only one data point across a number of columns that has the year. So what I want to be able to do is to copy the data, and somehow reference the year, which is in the first row 1. So it would give me the data and the year of the one and only data point. I was thinking it must involve a For Each Cell statement. Thanks. Sorry if it sounds a bit vague. It's either a macro, or go through 2300 records by hand, and I don't think I want to even think about the second one. What you write is a little confusing. Post...

Using calculation in a cell like a variable
This seems so basic to me, but I want to take the calculation of a formula in a cell, let's say the result of some financial calculation and then later on I have some text explaining what I did. Something like this: The answer to the question is --the value of cell C1-- That way if any information changes in the formula, you don't have to type it into the text. M, If you want the text and the formula result to appear in the cell, try this. Select the cell. Format - Cells - Number tab - Custom. Whatever is currently in the Type box, add this: "The answer to the question ...

I don't want to count hidden cells
Is there a way to count only the visible cells in a given range. I don't know how to use VB so if that is the only way to accomplis this task, could somebody please type up the code so I can use it in m spreadsheet (if this would be too much work I understand) Any help is appreciate -- Message posted from http://www.ExcelForum.com Hi Have a look at the SUBTOTAL function in help (under SUBTOTAL worksheet function). It might do the job for you, although I believe it only works with AutoFiltered hidden rows. -- Andy. "CENorth7272 >" <<CENorth7272.15u6jf@excelfor...

Selecting and moving chart(s) within a worksheet using keyboard ke
Hi all, I have created some charts in a worksheet. I would like to be able to do the following if possible: 1. I have selected a chart (by clicking on the chart and handles appeared around the perimeter of the chart). What I would like to do is to be able to select the chart using keyboard short-cut and to move the chart around the worksheet also using keyboard keys rather than the mouse. At the moment I can only move it (a single chart) using the mouse. However strangely enough, if I select two or more charts (Shift+ Lt.Click) I am able to then use the arrow keys to move the chart. ...

Working with formatted values
I am trying to reference formatted data (eg. 8.6) as opposed to the unformatted data (8.5789....) in a cell in that is in another worksheet which is apart of the same workbook. The problem I am having is my calculation are using the unformatted data which skews my results. Simply put, I just want to "fix" the data that I see in the source worksheet for further calculations later! Please help!!! :) -- A squirrel trying to get a nut!!! Then you must make the value the same as the format. For instance, if 8.6 is in A1, you would have to put: =round(a1,1) (this rounds A1&#...

Create a timestamp when a cell is updated
Hello all, I need help creating a timestamp that only updates when a record in another cell is updated and does not recalculate when you close or open the worksheet or hit F9 etc.. Example: The record in A1 is changed, it's detected and a timestamp is generated in A2 noting when it was changed. This is how I created the timestamp, I created the following Macro called "Timestamp": =now() in cell A2 (I click green X generating a value) I Copy and without moving to a new cell I choose Paste Special. I choose the Values Box under Paste and choose None under Operation The a...

hard coding a cell
Hi all- I'm not sure if I'm using the correct terminology but if you have a cell link that says =A1 and you copy and scroll that down the cells below it will move as well...i.e. =B1 =C1 etc, etc. Now I know that if you put =$A$1 thhe copy will instead put the $A$1 all the way down which is what I want. If I already have a cell with 30 links that don't have the "$" signs and would like to "hard code" all the forumlas with the dollar sign is there a shortcut for that? I seem to remember all you had to do was go to the cell and hit some shortcut keys and ex...

Count if date is before value?
I have a roster of employees and there is a column for the date of thei last annual training. I am trying to have a total on the bottom of th column that counts how many people are delinquent (havent had the clas in over a year) in their training. Looks like this: Name Class Date John Sexual Harassment 4/15/2003 Harry Sexual Harrasment 3/17/2003 Don Sexual Harrasment 4/1/2004 TOTA REQQUIRED 2 2 would be the total if the date was set to 4/15/2004 I have tried COUNTIF(C2:C107">4/15/2004")...

set combobox value
Access 2003 I have a form that is based on an Order table. The records in the table have already been entered. This form is being used to edit the existing records. When the record is initially entered the user can choose a Client from a populated combobox. when the record is saved the client is written to the OrderTable. I now want to edit the Order records but also giving the user the option to choose a different client from the combo box. How can i set the combo box to the value that is already stored in the orders table thus allowing them to change it from a list? Thanks Dear...

how to summerise values from specific columns
Hi, I have tried to find the right formulah, but failed. I have three column Amount A Amount B Amount C 30 10 20 34 and a fourth column where I have to fill in % that applies to all values A-C in the row. 12% 45% How to make a formula in a cell that summerise all amount A's * the respective % For example: 30 * 12% + 10 * 45% = Also for two other cells for B and C Bart Vista/excel 2007 try sumproduct() =SUMPRODUCT(A2:A3*D2:D3) HTH Regards Sebation "AA Arens" <bartvandongen@gmail.com> ??????:1189666230....

Excel 2007 will not refresh cells
I am currently using Excel 2007. I have a spreadsheet that I want to use to do some analysis of various situations. So here is my problem. I have several columns that have totals at the bottom. I have no problem getting the columns to total initially. However, if I plug in another number in the column, the total stays the same and won't refresh or re-total the column. I don't recall that this was a problem when I worked with Excel 2003. Can someone please tell me how to get the totals to update when I enter some new numbers in the column? Thanks in advance for the...

Pasting multiple cells into a single cell
Is there a way to copy data from multiple cells and paste that data into a single cell in Excel 2002? -- Thanks, David Hi David You can do it this way: =A1&B1&C1 change cell range to your need, also if you like space between each entry type it this way =A1&" "&B1&" " etc,etc HTH John "David" <David@discussions.microsoft.com> wrote in message news:97A5C2E0-E314-48F5-97C3-8622FAF836A3@microsoft.com... > Is there a way to copy data from multiple cells and paste that data into a > single cell in Excel 2002? > -- > Thanks, &...

#NAME? in Cells
Hi, We got a accounting spreadsheet via e-mail from a client which contains a lot of formulas. This file can be open by all the computers; however, one two computers, the cells show #NAME? instead of the numbers. All the computers have Office 2003 SP2 (we cannot install SP3 because of some third party software) and one has Office 2007 SP1 and the computer with office 2003 is one of the two computer that cannot see the numbers. It seems like an issue with the computers, not the file. Please help, thanks, Steve Louie On Apr 20, 11:12=A0pm, Steve Louie <SteveLo...@discussions.micro...