Looping until empty column

Here is my dilemma. I can do VBA in Access, but for some reason I jus
don't get it in excel. What I want to do is go down a column and stor
that info in a variable. I want this to happen untill there is no mor
data. Once I have that data I want to go to another page and store th
data in an empty column. I want the program to auto. find the empt
column. Thank You in advance.
thegu

--
thegu
-----------------------------------------------------------------------
theguz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2491
View this thread: http://www.excelforum.com/showthread.php?threadid=39297

0
8/4/2005 4:18:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
653 Views

Similar Articles

[PageSpeed] 7

Are you going to copy EVERYTHING in the column, i.e., are you starting with 
row 1 and moving down until you find nothing else?

If so, just copy the column

Now, what's the criteria for finding an empty column?  Is it the first 
column you come to, moving left to right, that has nothing in it?  I'll 
assume that's so.

This code copies column A on sheet1 to the first unused column on sheet2

   Dim src As Range
   Dim tgt As Range

   Set src = Worksheets("sheet1").Range("A:A")
   With Worksheets("sheet2")
      Set tgt = .UsedRange.Offset(0, .UsedRange.Columns.Count). _
            Resize(1, 1).EntireColumn
   End With
   src.Copy tgt


"theguz" wrote:

> 
> Here is my dilemma. I can do VBA in Access, but for some reason I just
> don't get it in excel. What I want to do is go down a column and store
> that info in a variable. I want this to happen untill there is no more
> data. Once I have that data I want to go to another page and store the
> data in an empty column. I want the program to auto. find the empty
> column. Thank You in advance.
> theguz
> 
> 
> -- 
> theguz
> ------------------------------------------------------------------------
> theguz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24918
> View this thread: http://www.excelforum.com/showthread.php?threadid=392977
> 
> 
0
DukeCarey (494)
8/4/2005 6:04:03 PM
Reply:

Similar Artilces:

PDF > text > Excel
I appear to have volunteered myself into a nightmare! I've been given a series of PDFs, which are a series of names and addresses of volunteers. Now, the data is in the format of Name Address Number Joined More random stuff Now, of course, if each "block" was in a certain format, given a little while I'm sure I could find or work out some sort of macro thing. But it's not. Sometimes the address has 3, sometimes 4 lines. Sometimes there's a phone number. Sometimes, it's a different way round. Now imagine 500 names and addresses, each of ...

Determining if CString is Empty ?
Hi guys, This has probably been asked a million times but here goes: what is the best (preferred) way to determine if a CString is empty ? And also, what's the best way to make a CString empty ? Here is how I do it now: // like they do in ClassWizard for a Dialog Box CString strEmpty=_T(""); // let's see if it's empty if(strEmpty == _T("")) // we got an empty string else // the string has some text in it Is that the best way ? I was thinking of using myString.Empty() and testing the length with {if myString.GetLength() < 1} but those are function calls ...

Column size keeps changing
Using OL2003 In a custom view I have many columns with carefully chosen sizes for each. This remained unchanged as long as I didn't resize them in OL2002. With the new 2003 version, when I click on Help for instance it opens up a sidebar to the right of the screen with the search fields and results and squeezes the view into the left hand pane.. When I close Help, my custom view is "restored" but the columns are all changed. Help! ...

Sheet presentation: puting column in order
I get an Excel sheet with many columns. I would like to set them quicly in a given order. I have column headers. Today a use cut/paste. But it is long ! Is there any possibility to see the column header and associate those a column number ? --- Message posted from http://www.ExcelForum.com/ Type column numbers you want in an empty row at the top and us Data/sort -- Message posted from http://www.ExcelForum.com Note you need to hit the Options button on the Sort dialog box and choose left to right (And make sure you select ALL the data to be sorted). Be very sure you have got it right b...

Horizontal scroll - listbox
I'm using Access 2000 and 2003. I have a single column listbox where the value extends beyond the width of the listbox. The horizontal scroll bar does not show. I want to confirm that there is no way to make the scroll bar show up in this case. Thanks, Rick Rick, I'm pretty sure that list boxes do not have a horizontal scroll bar. If you want the entire text to show, you will have to make it wide enough to display or you can use a sub form instead of the list box and that will give you the option of a horizontal scroll bar. "rick allison" wrote: > I'm ...

plotting "empty" cells that contain a formula that has returned a blank
Cells that appear empty, but contain a formula that has returned a blank, when plotted on a line chart, plot as ZEROS. This is in spite of setting tools, options, charts to "zero values not plotted". Does anyone know a way round this, please. thanks Bob Farey Instead of return a blank (" " or "") result, return a NA(). If you need a null string ("") for subsequent calculations or for aesthetic purposes, create a 2nd dummy range that has NA() instead of "". Plot the range with the NA()s -- Regards, Tushar Mehta www.tushar-mehta.com ...

Ordering Columns in a Cross Tab Query & Dividing a Value
Hello, First question, is there a way to specify what order the columns appear in a cross tab query? Second, is there a way to distribute a value in one column of a cross tab query across new columns? For example, if I have a total value of a contract, but I needed to recognize revenue equally in each of the three months of a quarter, is there a way to distribute that value across columns in a cross tab? Thanks! >> First question, is there a way to specify what order the columns appear in a cross tab query? In the SQL view edit the PIVOT line to something like this (Make sure...

Thick column lines
Hello I want to create thick column lines but don't know how. Thick lines would enable me to separate the data from one column to next more clearly. Please help. Thank you. Regards hash Format / Cells / Borders / Select thick border and click on diagram to tell Excel where to apply it on the cell(s). -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- There's no 'I' in 'Team' ---------...

Cannot empty "deleted items" folder
Hi All- My wife's machine is locking up when Outlook opens. I'm guessing that the deleted items folder is way too big (almost 8000 messages). Problem is, when I go to empty it (or do anything else), the maching hangs for a very long time and gives me the following error: 1. unable to load personal free/busy data. 2. the file ...Outlook.pst could not be accessed. System: Dell Laptop, Celeron Processor Windows 2000 Outlook 2000 Thanks in advance. Can you open the deleted items folder and select a few messages at a time, then hit delete? How big is the PST file? Dan wrot...

Repost: Loop through files
I posted this earlier, but it never made it to the screen so I'm reposting - hope it doesn't duplicate the earlier one. I need to loop through all currently open workbooks and display a message box that tells me the file name and whether or not the workbook is visible. Your example code is what I need. Thanks in advance for your assistance. Hi, Sub ShowThem() Dim oBook As Workbook For Each oBook In Workbooks MsgBox "Book " & vbNewLine & oBook.FullName _ & vbNewLine & "is " & _ IIf(oBook.Windows(1).Visible...

Holding the view of column one
Good morning, I want to hold the view of column one while working on other columns. What is the best approach to my need? Thank you, Mark Windows>Freeze panes -- Kind Regards, Niek Otten Microsoft MVP - Excel "Mark S." <Mark S.@discussions.microsoft.com> wrote in message news:0DDD2DBD-1BF5-4D11-AA96-86368E7BA8E6@microsoft.com... > Good morning, > > I want to hold the view of column one while working on other columns. What > is the best approach to my need? > > Thank you, Mark ...

Updatig the Next Consecutive Empty Cell in a Range
I am tracking a bank account balance and would like that whenever a new value is entered in cell c3, it updates the next consecutive empty cell in range c5:c200. Please advise how I can write the formula, function or macro. Thanks Sony You need event code to do what you want. Try this... right click the tab at the bottom of the worksheet where you want this functionality, select "View Code" from the popup menu that appears and copy/paste the following into the code window that appears... Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long ...

Column Headers/headings
Hi, Is there any way that you can get the top row to stay visible no matter where you are on the spread sheet - ie: to be able to see what columns you are typing in (eg cost) when further down the spread sheet. Thanks for any help, Matt. Click on A2 Click Window > Freeze Panes -- Rgds Max xl 97 ---------------------------------------------------- Use xdemechanik <at>yahoo<dot>com for email ----------------------------------------------------- "The Stoat" <thefantstoat@lycos.co.uk> wrote in message news:5iSNb.3831$pD3.3270@news-binary.blueyonder.co.uk... &...

My "Do While...Loop" wont loop and delete the spaces between my digits.
I am trying to eliminate the spaces between digits in Australian Business Numbers which have the format "NN NNN NNN NNN" to produce an eleven digit number with no spaces. I have written a function to do this that appears to work. I now want to call this function from a subroutine to change about 3000 numbers. The subroutine is causing me headaches. Here is my space-eliminating function: Function RemoveSpaces(CellContents As String) As String Do While InStr(1, CellContents, " ") > 0 CellContents = Replace(CellContents, " ", "") Loop Re...

Loop?
I really am having a bad week... I have a workbook with two sheets; 'main' & 'not complete'. On the main sheet I have 10 columns of data, row 2 to 20 (More rows can be added so may be more). What I need to do is: for each row, if column I = 1 and J = "" then I need to copy some of the data from that row (column A,B,F & G) to the sheet called 'not complete'. the cell in column J will then be changed to 1. Repeat for each row making sure the sheet called 'not complete' does not have any blank rows. Hope that makes sense a...

Money 2003
What is the column in the register that has the heading of a red exclamation mark? Some of my entries have an image of an envelope and a question mark in that column. What does this image mean? This is Money 2003 Deluxe running under Windows 2K. Thanks in advance. Mark See http://umpmfaq.info/faqdb.php?q=6 and http://umpmfaq.info/faqdb.php?cat=6. From online help: There are two icons that indicate different conditions for downloaded transactions: When you see ? in your account register, no match has been found for this downloaded transaction. It is marked as a possible new transaction. A ...

Unhide a column among columns.
Hi, There are 10 columns which are hidden between two columns(A and X). I want to unhide only 1 or 2 columns without enabling(unhiding) remaing How can i do it. Thanks and regards mowrya.K.B.P. With the help of a Macro. The command look like this: Columns("E:G").EntireColumn.Hidden = False *** Check the VBA Help *** Micky "Mowrya" wrote: > Hi, > There are 10 columns which are hidden between two columns(A and X). I want > to unhide only 1 or 2 columns without enabling(unhiding) remaing How can i do > it. > Thanks and regards >...

Columns and rows are numbered
For some really strange reason lately, when I'm working in Excel both my rows and columns are numbered 1,2,3,4,5....This makes it really difficult to deal with . How do I get excel to stop doing this I want my columns to go back to being lettered A,b,c,d, etc....Anyone else ever have this happen or know how to fix it. It's annoying. Thanks Tools - options - general- R1C1 style- UNTICK. ;) >-----Original Message----- >For some really strange reason lately, when I'm working in Excel both my rows >and columns are numbered 1,2,3,4,5....This makes it really difficu...

Totals column thinks it's 24 hr clock & shows 01h31m instead of 25h31m. Fix?
A spreadsheeI that allows me to enter hours is not adding up correctly in the totals column. It adds up correct up to but not including 24 hours but after that the total deletes that 24 hoursl It seems Excel is mistaking this for the 24 hours clock system and doesn't realize that it must show the total. i.e., after deleting 3 rows above the totals column I get a total of 23h16m but then after the next row, it shows less than 1 hour with the final total of all the rows showing 01h31m. That should actually show 25h31m. Is there a way to get Excel to actually add up the hours instead of ...

flip columns to rows
Is there any way to 'flip' a worksheet with one command, so that columns become rows (or rows become columns)? Have you tried the Transpose function? "captain fantastic" wrote: > Is there any way to 'flip' a worksheet with one command, so that columns become > rows (or rows become columns)? > captain fantastic wrote: >Is there any way to 'flip' a worksheet with one command, so that columns become >rows (or rows become columns)? Not one command, but you could write a macro. Select all Copy Paste Special tick "Transpose" Chris ...

Loop/Repeat Code
A2007 I need to run code while the record is displayed on a form, and must run this for all records. I have on the Click event of a button on the form: DoCmd.GoToRecord , "", acFirst Call MyFunction It is MyFunction I need help with: DoCmd 'Run some code and queries' DoCmd.GoToRecord , "", acNext Need help here to repeat/loop until the last record. Thanks in advance. ...

XML Deserialize of empty value
Hello, I'm encountering a strange issue when deserializing a piece of XML. If I have the following XML and try to deserialize it, everything works: <page> <format>Letter</format> <margin>1</margin> </page> If I have the following XML, everything works: <page> <format></format> <margin>1</margin> </page> BUT if instead I have the following XML, the "margin" element is always 0: <page> <format /> <margin>1</margin> </page> Why...

find first empty cell in range and then copy from another workbook
I am looking for a formula function or a vba code where: - In workbook1 find the first cell that is empty between range A7 - A10, - In workbook2, in Range G10- G13: find the word "Day1". - If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy the particular cell or cells where "Day1" exists to the first found empty cell or cells in range A7-A10 in workbook1. Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means A8 is the first empty cell. And G10,G11,G12, G13 (workbook2) have the word "Day1" Then, Copy cell G10 into ce...

address book empty ?
Has anyone else had the same problem i wonder. i have a good 40 contacts within my contacts folder, but the address book seems to be missing??????? what i mean is, if i want to forward a e-mail and cc it to others, when i go to cc and double click, i have only about 15 contacts. (I HAVE USED PC SYNC FOR NOKIA) if i create a new message and start to type a contacts details that is not in the contact folder, the name will appear. I know the addresses are there but hidden. MTIA CHRIS -- charlieisleofwight Have you confused your nickname list with your contacts? And have you made sure to ch...

recover a folder put in junk folder then emptied junk folder
Learn how to post a question: http://dts-l.org/goodpost.htm See that large white space beneath the subject line? Use it to provide = at least a modicum of information about what you are trying to = accomplish. Otherwise, you are wasting our time and yours when someone = has to come along and try to pry information out of you. --=20 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All = unsolicited mail sent to my personal account will be deleted without = reading. "marvp1" <marvp1@discussions.microsoft.com> wrote Nothing! ...