Excel 2003 :: Do Until IsEmpty Looping

Aug 3, 2012

Excel 2003. I believe I've got everything working in the code up until:

Code:
If rnGx = rnGy Then

It loops once and then "Run-time error '424': Object Required" for the code above.

Code:
Sub test1()
Dim rnGx As Range
Dim rnGy As Range
Dim i As Long
Dim xNum As Integer
Dim yNum As Integer
i = 2
Set rnGx = Cells(i, 2)

[Code] ....

In case I'm way off, I'm trying to get the code to do the following:

Look for identical entries in column bw here duplicates exist, sum the corresponding column d values into one of the existing columns delete the remaining duplicate do this until there are no matches.

Column b is sorted ascending, so any matches should be next to each other. as I'm writing this, though - I realize that if there are triplicates or more in column B, this would need to be run more than once.

View 7 Replies


ADVERTISEMENT

ISEMPTY Across A Range

Jan 13, 2007

I have a range of cells - A1:U200. All I want to do is to check that all cells are populated and if there is an empty cell return the value in column A of the same row. I think I can do it with Loops to test every cell but wonder if there is a simple method.

View 9 Replies View Related

VBA IF Function Or If IsEmpty

Oct 13, 2007

I need a IF funtion to look at a series of cells and if the cells are empty I want it to proceed to userform1. If the cells have data already i want it to go to userform5.

I tried IF IsEmpty(cell list)=True Then UserForm1 and IF IsEmpty(cell list)=False Then UserForm5

But it only bring up userform5 no matter what the cells have in them.

View 9 Replies View Related

IsEmpty Usage

Feb 23, 2009

This seems like it is pretty easy but for some reason it is being goofy for me.

I entered this code to check it.

IF IsEmpty(Range("A1")) Then
msgbox("Yes, It is empty")
Else
msgbox("Not empty")

When it comes up I always get the box with "Not empty" even though I havent typed anything in the cell since I have created it. Even tried Clear Cell. Basically what it is for is to check if cell A1 is empty and if it is, it will create a Data header and if not, it will find the first empty cell and continue inputting data.

View 9 Replies View Related

Getting Loop Until IsEmpty To Stop

Feb 21, 2012

I'm trying to get a looped function to continue down a worksheet until a specific column has no data in it. My code is below, and it works great for doing what I want it to do (copy a row and continue pasting it every 3 rows down until the row where Column E has no data in it) but it doesn't stop at the end of the data:

HTML Code:
Sub Test2()
' Select Row 11, *first line of data*.
Rows("11:11").Select
Application.CutCopyMode = False

[Code] ....

View 6 Replies View Related

Excel Looping With Bloomberg And Pause

Apr 30, 2014

I have an excel workbook right now with a 75x75 correlation matrix. It has 75 stock prices and uses bloomberg to pull in the correlation between each of the holdings. There is a lot of data so it takes roughly 35 seconds for it all to get pulled in from bloomberg. The input value that I can change is the date. The output value is a specific correlation coefficient. Thus, if I input today's date into a cell in the file, it will run the correlation matrix and tell me the correlation between all the holdings using the past year's data. Then it will produce a single output value.

I am now looking to use VBA to make this more robust. What I have been trying to do is have a macro which will -

(1) input the current date into the input cell
(2) have the code wait 35 seconds (and since it is using bloomberg pull in the traditional wait methods do not work because they actually stop the data pull in also)
(3) copy the output value
(4) paste special that value in a different cell
(5) repeat the process but this time use the date from a day before and have the output value copied and pasted to a cell below the previous one.

This loop would continue over 20 times.

Below I have some code that allows me to do this process once but is not looped and I have been stuck trying to make this work with a loop given the bloomberg delay. When I add a loop, it does not wait the 35 seconds I need before doing the whole process again.

[Code] .....

View 2 Replies View Related

Excel 2002 :: VBA Looping Copy / Paste Special For Each Row

Jan 25, 2012

Excel 2002.I have data in columns A and B on sheet 3, starting at row 100. The total number of rows of data is variable (max is 50 rows). I want to take the values in A100:B100 and copy them from Sheet3 and paste values transposed to SheetTL Range C1:C2. I have started to write the following code but as you can see I will have to repeat the code upwards of 50 times. It seems I should be able to do a COUNT function to see how many rows of data exist and then somehow loop the code to copy the data from each subsequent row that many times, but how to put that together.

Sheets("Sheet3").Range("A100:B100").Copy
Sheets("TL").Range("C1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ActiveSheet.PrintOut
If Sheets("Sheet3").Range("A101") = "" Then

[Code] .........

View 5 Replies View Related

Excel 2003 :: Copy Hyperlink From Excel To Outlook?

Apr 19, 2010

I have an Excel 2003 file that contains hyperlinks to OneNote notebooks on a Sharepoint site. An Excel macro looks for these links and determines the full hyperlink address which is then assigned to a variable. An Outlook message is generated which includes the hyperlinks.

The hyperlinks work in the Excel file. I can also copy them from OneNote and manually paste them into an Outlook message and they work. However, when I obtain their full address and transfer that to the email through code, the links do not work. The hyperlink address from OneNote starts with "onenote:http" which is not recognized as a link.

If I can do this manually, there must be a way to do this with vba. Are there characters I need to include in the OneNote hyperlink address to make this work? Is there another way to transfer the working link from Excel to Outlook?

View 3 Replies View Related

Adjust Macro From Excel 2003 To Excel 2010?

Apr 1, 2014

I am trying to adjust the below macro so that it will work in Excel 2010.

Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer

[Code]....

View 3 Replies View Related

Looping Macro Freezes Excel When Encountering Large Numbers Of Rows

Sep 15, 2009

We are attempting to use the macro below.

We believe that the problem is "memory related."

However, we can't find the correct workaround to the issue.

In Excel 2002, the behavior was for the macro to work great on small numbers of rows, but to just stop on large numbers of rows. After it stopped, the user would manually run the macro throughout the spreadsheet, by holding down Ctrl + N.

In Excel 2007, the behavior of the program is different, and Excel actually freezes up.

We've narrowed down the problem to be possibly "memory related."

However, we don't know the correct workaround for this.

For example, in the posting below (the referenced link) they suggest using "variant arrays" to address memory limitations type of issues ... but I'm not sure of how to implement those.

View 6 Replies View Related

Excel 2007 :: Looping Through Text Strings And Copying Based On Criteria

Sep 17, 2012

I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)

File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.

File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work

Code:
Option Explicit
Sub get_data_from_2()

Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range

[Code] .......

the bolded part gives me an error.

View 3 Replies View Related

Excel 2003 :: Alt + F11 Key Not Working?

Feb 21, 2012

when I use the above key stoke nothing happens in excel. It won't get me into vba editor??

using Dell laptopInspiron N4010 and office 2003

View 2 Replies View Related

Excel 2003 :: Hiding 0 Using ABS

Mar 27, 2012

I am using the formula ABS(D6-G6) when these cells are empty I get a 0 result, how can I get rid of this?

Using 2003

View 6 Replies View Related

Excel 2003 :: Group And Outline

May 14, 2013

In Excel 2003 I have Manually Grouped a number of rows. (Data>Group and Outline>Group).

If I use the buttons with the numbers 1, 2, 3 (indicating an outlining level) to collapse or expand the outline I receive the Error Message: "Cannot shift objects off sheet."

If I use the collapse button (-) or expand (+) button the data collapses and expands as I would expect.

I am aware of the normal issues surrounding objects and outlining and have ensured that I have changed the properties of all my of the objects in the sheet to "Move and size with cells". There are no comments in the grouped rows.

View 4 Replies View Related

Excel 2003 :: Formula To Sum Every 6th Row Of E Column?

Feb 4, 2014

The following formula was, several weeks ago, very graciously offered to me from one of Excel Forum's contributors.

=SUMPRODUCT(--(MOD(ROW(E8:E6782),2)=0),E8:E6782)

My request was to find a formula that would add each 6th row starting in row e8 (e8+e14+e20+e26+e32 etc. through e6782) in column "e" when the column was 6782 rows deep from top to bottom. (i am not trying to add every number in column e, just each 6th row, starting at e8 and going through row e6782).

I entered the formula into my spread sheet and, voila, I had a sum that I assumed was accurate for my spread sheet of ticket sales. I began to question the functionality of the formula when I altered the E8:E6782 parameters (which represented the gross ticket sales) to E4:E6778, in an effort to sum up the E4 values e4,e10,e16, e22,e28,etc. . . (which represents the net values after commissions were deducted). The difference in the two sums (e8 values Versus the e4 values) was incorrect and did not represent the appropriate commissions (which should have been 15%).

View 1 Replies View Related

Excel 2003 :: Log Out Duration Per Employee

Feb 27, 2014

I have a Log In/Log Out report that I need pulling data from.

Ultimately what I want to do is get the amount of time my employees were logged out.

I am using Excel 2003. Attached is an example of the data I'm working with.

Sheet 1 contains the data, and Sheet 2 will be the summary.

So, looking at Bob's times: If column A contains Bob (A2 in this case), and the same row in column G (G2) contains 79, subtract the contents of the same row in column F (F2) from the next row down in column D (D3) (which would be D3 - F2). That result should populate on Sheet 2 next to Bob's name in h:mm:ss format.

If there is nothing in the next row down in column D, then do nothing.

I would like to leave the data as is, if possible. There are extension numbers next to the names in the data. If possible to ignore those when rolling up to the summary that would be useful. Also, on the summary sheet, if the employee is not on the data sheet (if they had the day off) they can have 0 or NA or something to indicate there is no data for them.

View 1 Replies View Related

Excel 2003 :: Status Bar Missing?

Apr 15, 2014

In Excel 2003 the Status Bar is missing. I know to go to Tools, Options, View to view and hide the Status Bar however as i cannot see Tools (as Status Bar missing) is there another way to get the Status Bar back?

View 2 Replies View Related

Excel 2003 :: Can't Retrieve XL Documents

Jul 18, 2013

I have used MS Office Excel 2003 for many years without difficulty in XP. Suddenly I find I can't open many of my old documents. I get a message,"Microsoft Office (InfoPath2003) cannot open (the following file, etc.) A second message says, "The form contains XML that is not parsed: An invalid character was found in the content. Line 0, Position 0" Then I get the message "MS Office is not currently set as default." I complete the instructions that follow and it is now corrected but I continue to get the same error messages.

View 2 Replies View Related

Excel 2003 :: Undo Is Not Working

Nov 12, 2013

why my undo just won't ever work. Excel 2003.

View 1 Replies View Related

Excel 2003 :: SaveAs VBA To Always Save In XLS

Mar 11, 2014

I am trying to edit the code below to save in .xls instead of .xlsm. Also, I need to disable the compatibly checker.

[Code]....

View 1 Replies View Related

Excel 2003 :: Two Sumif In One Cell?

Sep 1, 2009

Using Excel 2003, is it possible to use 2 sumif functions in one cell?

i.e., sumif and sumif

View 5 Replies View Related

Excel 2003 :: How To Automate A Macro

Oct 27, 2011

I am running Excel 2003.I have a spreadsheet with two worksheets, each worksheet has a Refresh button that runs a Data Extract Macro.The Data Extract Macro imports Data from another spreadsheet.I want to have the spreadsheet automatically run my Data Extract Macros for both worksheets at a specific time each week. (Scheduled task) Regardless of whether or not the excel file is open.

View 4 Replies View Related

Excel 2003 :: Carriage Return For Row?

Oct 28, 2011

In Excel 2003, I remember being able to tab across a row of cell entering data and when I got to the end, I'd hit enter and it would take me to the beginning of the next row. But in 2007, I can't...is there something I need to do in Excel Options or is this function gone?

View 1 Replies View Related

Excel 2003 :: How To Merge Several Worksheets

Nov 21, 2011

I would like to merge several sheets into one sheet, all from within the same workbook. All sheets have the same data layout. I use xl 2003.

How can I merge them all into a single worksheet?

View 6 Replies View Related

Excel 2003 :: Matching Row Data

Nov 28, 2011

I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.

Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1

View 3 Replies View Related

Excel 2003 :: Cannot Unhide Column

Nov 30, 2011

I have an Excel 2003 application where one of the columns (column D) has been hidden by some user.

When I try to unhide nothing happens. I select column C and E followed by a right-click on column E where I choose Unhide -> column D stays hidden.

View 7 Replies View Related

Excel 2003 :: Return Last Instance?

Jan 3, 2012

I have tried Index/Match but nothing so far. Basically, I have this table:

Sheet2KLMN3Employee1Client Assistance/Request31/3/2012 14:57:344Employee1Client Assistance/Request31/3/2012 14:58:075Employee2Client Assistance/Request11/3/2012 14:59:456Employee3Client Assistance/Request11/3/2012 17:08:297Employee1Client Assistance/Request61/3/2012 17:10:16Excel 2003

So now, I want E19 of the table below to bring back column N of sheet 1, but only the last instance in which those Employee 1 and Client Assistance/Request met. In this example N7 of Sheet 2.

Sheet1BCDE18Client Assistance/RequestDate19Employee 10020Employee200Excel 2003

View 5 Replies View Related

Excel 2003 :: Get Some Information To Stay Still?

Jan 29, 2012

While using Excel 2003, I am aware of "freeze plane" and "split worksheet". The freeze plane comes in handy if I want to the first few rows to remain stationary and displayed while I can scroll down to the rows below. Similarly, the split worksheet comes in useful if I want to have the first few columns remain in place while I can maneuver to the right to see the other columns.

*** THE QUESTION IS: how can I get cells A1 to F20 to remain displayed and unmoved even if I choose to move the active cell around anywhere between G1 to K9999

View 9 Replies View Related

Excel 2003 :: Convert PS File To PDF?

May 24, 2012

Every day I create many Excel reports that I manually save as PDFs for distribution to my stakeholders. I'd like to automate this process using a macro. I've seen the following code online and have attempted to use it, but receive an error in the Dim MyPDF line of code indicating that the user-defined type is not defined.

I'm using Excel 2003 and Acrobat Distiller 8. I have no problem creating PDFs manually

Code:
Sub Create_PDF()
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String

[Code]....

View 2 Replies View Related

Excel 2003 :: Return Last Value In Column

May 25, 2012

Using Excel 2003,

Column A Column B C1 Column D
10062 01-01-2012 3:00 PM 01-02-2012
10062 01-02-2012 6:00 PM 10055
10062 01-02-2012 9:00 PM 10062
10062 01-03-2012 4:30 PM
10055 01-02-2012 1:45 PM
10055 01-02-2012 3:15 PM
10055 01-04-2012 8:30 PM

I am looking for a formula to place in C2 that will macth the number in column D with the number in column A and return the LAST date/time from column B that matches the day from cell C1.

If correct, C2 would return 01-02-2012 3:15 PM (Last date/time for 10055 on 1-2-2012)
C3 would return 01-02-2012 9:00 PM (Last date/time for 10062 on 1-2-2012)

View 9 Replies View Related







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