Macro to find the first cell in a column with different data

Hello,

I really could use some of your expert and excellent help with a problem I 
am having in creating a macro to perform a search in "Column A" to locate a 
change in the data in that column and then move one column to the right and 
one row down and type "1" in that cell.

I really appreciate your help, and Have a Blessed Holiday Season.

0
Alabama (3)
12/14/2005 3:06:07 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
397 Views

Similar Articles

[PageSpeed] 49

Perhaps this might also do it here ..
Assuming data is in A1 down, of the structure as below:

1
1
1
2
2
3
3
etc

Put in B2: =IF(A2<>A1,1,"")
Copy down

Kill the formulas in col B with an in-place:
Copy > Paste special > Check "Values" > OK
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Lost in Alabama" <Lost in Alabama@discussions.microsoft.com> wrote in
message news:371A11D0-21E9-49DF-8B59-440AE45A56A4@microsoft.com...
> Hello,
>
> I really could use some of your expert and excellent help with a problem I
> am having in creating a macro to perform a search in "Column A" to locate
a
> change in the data in that column and then move one column to the right
and
> one row down and type "1" in that cell.
>
> I really appreciate your help, and Have a Blessed Holiday Season.
>


0
demechanik (4694)
12/14/2005 3:18:22 PM
Max has provided a formula.

If you want a macro, try this one.

Sub Addone_At_Change()
Dim i As Long
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
    End With
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Cells(i - 1, 1) <> Cells(i, 1) Then _
                Cells(i, 1).Offset(1, 1).Value = 1
    Next i
    With Application
        .Calculation = xlAutomatic
        .ScreenUpdating = True
    End With
End Sub


Gord Dibben Excel MVP

On Wed, 14 Dec 2005 07:06:07 -0800, Lost in Alabama <Lost in
Alabama@discussions.microsoft.com> wrote:

>Hello,
>
>I really could use some of your expert and excellent help with a problem I 
>am having in creating a macro to perform a search in "Column A" to locate a 
>change in the data in that column and then move one column to the right and 
>one row down and type "1" in that cell.
>
>I really appreciate your help, and Have a Blessed Holiday Season.
0
Gord
12/14/2005 6:39:34 PM
Reply:

Similar Artilces:

Macro question #8
I want a macro to select a row (or cell in a particular row) on a worksheet that corresponds with a value in a cell eg. Cell shows value 5. Macro selects row 5 on another sheet or Cell shows value 5. Macro selects cell at intersection of row 5 and a specific column (say column 2 ?) on another sheet What do I need to include in my macro to do this? Can anyone help ? Regards Ross Bennett One way to select the row: Option Explicit Sub testme() Dim myRng As Range Dim otherWks As Worksheet Set otherWks = Worksheets("sheet2") Set myRng = Nothing On ...

Message box warning of missing data
Hi, When a user inputs data (numbers) into cells in column D, they should include a three digit prefix before the numbers. I would like a message box to pop up if they fail to do this and delete what they have put in the cell thus forcing them to try again. For example 09/123 would be rejected but SUM09/123 would be ok. Thanks in advance. -- Traa Dy Liooar Jock Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and tr...

Active cell
Hello, this is my VBA Macro: I want to select "clastrow" as my active cell. is that possible ? Thanks, Sub SumM() Dim clastrow As Long Dim olast As Range clastrow = Cells(Rows.Count, "I").End(xlUp).Row MsgBox clastrow Set olast = Range("I" & clastrow) isum = Application.WorksheetFunction.Sum(Range("I4:I" & olast)) ActiveCell.Value = isum End Sub Not quite Jeff, You get the last row, cLastRow, but then use that cell rather than the row in the formula. Try Sub SumM() Dim clastrow As Long Dim olast As Range Dim iSum clastrow = Cells(Rows.Co...

How can I update the data listed in the combo box to show the most
I have a Database where tables are linked back to the master data tables. The main entry/edit form has a section (as shown) the new Carer record is entered using a separate form then the detail is called from the main form using a combo box. My problem is that the newly entered Carer data does not appear in the list of carers. F9 does not work, closing and re-opening the main form does not work. When I check the item is listed in the Carer table. My question is how can I update the data listed in the combo box to show the most recent record. I would be really grate...

How do I include cell contents from a form in generic statements/strings within the form?
I have a form that contains NAME in cell A:1, and GENDER in cell B:2. I want to build a library of generic statements along the lines of: NAME tries hard, but if HE/SHE tried harder HIS/HER results would be much better. How can I make these statements get NAME from A:1, and derive HE or SHE and HIS or HER from GENDER in B:2? =A1&" tries hard, but if "&IF(B1="M","he","she")&" tried harder "&IF(B1="M","his","her")&" results would be much better" -- HTH Bob (there's no ...

Displaying the Sigma Symbol as a Column heading
Excel ver 2000 I would like to use the Sigma symbol as a character of a Column heading. I have two questions: Does Excel have the ability to display these symbols as text? What is the process for doing this? I tried looking at the WingDing fonts but could not find the symbol. Where can I obtain a keyboard mapping of the WingDing fonts? Thank you You can see it in Internet Explorer at http://www.mvps.org/dmcritchie/rexx/htm/fonts.htm you can make Firefox show same if you add fonts, and you will have to redo each time a new version of Firefox is installed. http://www.mvps.or...

Sharing user data on home, office and Powerbook computers
I have Office X on several different computers and would like to keep all my user data the same on each of them. Is there an easy way to keep them all in sync? Most important to me is the info in Entourage (addresses, calandar and to do lists) but, it would be great to keep the documents in sync too. If anyone can help, I would surely be thankful. On 9/13/03 10:18 AM, in article d02bc51f.0309130918.3d036d7c@posting.google.com, "Clayton Townsend" <claytontownsend@earthlink.net> wrote: > I have Office X on several different computers and would like to keep > all my user d...

Importing Data From Excel
I have been trying to import transactions I entered into excel, but I have been unsuccessful. Can anyone provide me with some suggestions on how I can import the transactions from excel into Money 05 Small Business See http://umpmfaq.info/faqdb.php?q=8. "Lou" <Lou@discussions.microsoft.com> wrote in message news:B0A1DB45-03AD-462D-8D80-5453DD8F094B@microsoft.com... >I have been trying to import transactions I entered into excel, but I have > been unsuccessful. Can anyone provide me with some suggestions on how I > can > import the transactions from excel int...

chart type affecting data order
When I change the chart type from bar to line (for example), it changes the order that the data is displayed in the data table. How do I keep data in the data table in the same order that I entered it? You would have to use an alternative to a data table. Make a table in the worksheet, where you have much finer control over what appears, where it appears, and how it appears (formats). Then either situate the chart close to this table, or use the camera tool to paste a dynamic picture of the table one or near the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custo...

Chart Data Won't Display
Hello, I've inherited a line chart. I successfully changed the data range last month by dragging the outline box. This month, though, I can drag the outline box, but in the chart, the value won't show up. Does this make sense to anyone? Thanks! ...

I can't find the INVOICE TOOLBAR
I'm using the Invoice template which has an "autonumber" button. It requires me to open the INVOICE TOOLBAR which I can't find. It does not show in the list of toolbars. Hi AhSee i've not used the invoice template provided by MS .. but i have written an alternative system to give people an some ideas if they'ld like to create their own invoice / ordering system ... if you'ld like a copy of it, email me direct julied @ hctsdotnet dotau Cheers JulieD "AhSee" <AhSee@discussions.microsoft.com> wrote in message news:6CBF5420-5EFD-42FD-8A22-B...

lookup multiple values in single cell
Hello there - Im looking for some help with a lookup. I have two spreadsheets. One with a simple list of numeric values: Spreadsheet A 1004 24965 66435 25459 7785 1008 26885 The other has multiple values in a single cell: Spreadsheet B 1004,24965,66435,25459 7785 1008 26885 The delimiter does vary, but it is always a non numeric. Id like to be able to find the row number in spreadsheet B where m value from Spreadsheet A appears. Thanks very much for your time. Andrew -- boneshom --------------------------------------------------...

how to view changes to data by user?
How do I view changes/updates made to a database by various data-entrier by their names and time of the day? Any info is appreciated! Conie code: AddFieldToTable, AddDateUserToTables --- Hi Connie, Add these fields to every table: UserIDc, long integer -- userID who created record UserIDm, long integer -- userID who modified record DateCreated, date/time -- date/time record was created DateModified, date/time -- date/time record was modified DateCreated can have a default valut --> =Now() and it will get filled automatically each time a record is created UserIDc can ber filled o...

Column Size
Hello I've been playing around with placing objects in specified cells. However, I've noticed that the standard column width is shown as 8.43 (64 pixels) - can you tell me what this measurement is? I think the standard row height is in points - why the difference? Thanks Peter Width is dependant on the font. 8.43 denotes that many characters can be displayed in the default Arial font. Try this: With the display set at 100%, note how many columns are visible on your screen. Now, <Tools> <Options> <General> tab, And change the default font to Algerian (right ab...

Convert Data to columns heading
Example: Product | Date | Sales ----------------------- A | Jan94 | 200 A | Feb94 | 300 A | Mar94 | 400 A | Apr94 | 500 A | May94 | 600 A | Jun94 | 700 A | Jul94 | 800 A | Aug94 | 200 A | Sep94 | 300 A | Oct94 | 400 A | Nov94 | 500 A | Dec94 | 600 B | Jan94 | 200 B | Feb94 | 300 B | Mar94 | 400 B | Apr94 | 500 B | May94 | 600 B | Jun94 | 700 B | Jul94 | 800 B | Aug94 | 200 B | Sep94 | 300 B | Oct94 | 400 B | Nov94 | 500 B | Dec94 | 600 How can I convert it to: Product | Jan94 | Feb94 | Mar94 | Apr94 | May94| Jun94 | Jul94...... ----------------------------------------------------------------...

Link Chart with data cell (cl
This was your questio Hi I want to link all the points in my xychart with the rows of the data that are in the chart. i.e. after a mouseclick on the chart data the user will be redirected to the data row The idea is to create a link to a text cell, situated in the same row as the data, which explains the details to each point Thanks in advance for your help Mattia ...

column widths and row heights
based on 100% view, when u set the column width to say 12 units and the row height to, for example, 15... what is the actual dimensions in reality in mm. ie, how can i get the required number to ensure that row and colms are exactly 2 mm in width and height pat Column width and row heights defaults are not in inches or mm. The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. For ...

Importing data from a text file
I have to import raw data from a text file that Excel cannot break into columns, and the import is unsuccessful. would VBA be a better choice, having it specifically search for data? there are 3 specific types of files and all the files (of the same type) are formatted exactly the same. thanks in advance -- jbaranski ------------------------------------------------------------------------ jbaranski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36862 View this thread: http://www.excelforum.com/showthread.php?threadid=566746 Why can't Excel break the ...

How to find duplicates within same column
Hello all, I am trying to find if there are any duplicate values within the same column, without removing them. Example ID no. 1121 2222 3343 5554 6785 4565 6785 1121 3343 In this example 1121, 3343, 6785 are duplicates. I have a ~20000 rows spreadsheet and would like to speed up the process. Thank you all, Frank http://www.cpearson.com/excel/Duplicates.aspx -- Regards, Peo Sjoblom "Frank Beltre" <fbl3@fbl3.com> wrote in message news:uhDpWEL8HHA.3716@TK2MSFTNGP03.phx.gbl... > Hello all, > > I am trying to find if there are any duplicate values withi...

Deleting Blank Characters in a Cell
I have a list of names with email addresses of attendees of a seminar series that I am trying to compile into a list that can be copied and pasted into an email (using BCC as the methodology for emailing). The challenge that I have is that when these files were exported from the database to excel (not sure how), all of the cells contain exactly 100 characters even though the email address may only contain 13-20 characters. Is there a way to "trim" the blank characters after the last character that is used by a letter instead of blank character field? Thanks in advance! PZan --...

Unmask the first digit of credit card number
The first digit of the credit card number needs to be unmasked, so that a person call see what type of card was used. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=dc90...

Comparison of Columns
I have three columns thast represent Set numbers, I want to compare the values in column "B" with those in column "A" find where they match and then calculate how many rows they have moved either up or down and put the answer in column "C" Col A Col B Col C 1127 1129 +1 1129 1130 +1 1130 1132 +3 1102 1102 0 1131 1131 0 1132 1133 +1 1133 1135 +1 1135 1137 +2 1062 1138 +2 1137 1062 -1 1138 1141 You need to provide much more detail. For example, what exactly do you mean by "where they match&quo...

Hyperlink Macro to 2,500 PDF files?
Fellow Forum Members, I have 2,500 PDF ebooks on a variety of subjects all contained in one folder named "Ebooks". I already have all the 2,500 ebook titles inside Excel in Column A, Sheet 1. Now I want to hyperlink each title to the actual PDF file and doing this hyperlink operation 2,500 times is something I want to avoid like the plague. Can anyone out there please help me in developing a macro that would do this hyperlink operation automatically 2,500 times? I'm not a macro expert, but is it not possible to make Excel automically go to the same Ebook folder 2,500 times a...

splitting text from one cell
I need to remove specified data from a single cell and place on the same row in another column. Not all data will be the same eg 100mg, 200mg etc Not too clear; what data? Do you want to extract the number from the text? This will extract 100 from 100mg and 5 from 5mg: =--LEFT(A1,FIND("m",A1)-1) Any help? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rogera" <rogera@discussions.microsoft.com> wrote in message news:98106D4C-04D2-4A2D-B293-9F5BBF0F04EB@microsoft.com... >I need to remove specified data from a single cell and place on th...

Locked hidden cells
Is there any way of stopping someone getting the values of locked hidden cells on a protected worksheet by linking to them on another worksheet? "John Bundy" <jmbundy@gmail.com(remove)> wrote > The right person can get into anything they want regardless of protection, > the more secure you make it the more people you stop but there are always > some. As far as linking them to another sheet, if they will break > protection > on one it is just as easy to do on two so I wouldn't bother Thanks. It seems ridiculous that you can hide data by protecting the...