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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Nov 12, 2013
why my undo just won't ever work. Excel 2003.
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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