Excel 2010 :: Loop Through Row Of Data Testing Values

Dec 25, 2011

I am running excel 2010. I have set up a variable " testvalue" type variant

I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".

testcol = 1
testrow = myTextRows - 1 'penultimate row
Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0
testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value
Select Case testvalue

[Code] ........

View 3 Replies


Excel 2010 :: Testing Userform - Divide By Zero Error

Mar 26, 2013

I'm the final stages of testing a userform that, in response to a button click, copies certain cells from a big messy worksheet and pastes the relevant ones (based on user input) in a clean sheet. Suddenly, I started getting a 'divide by zero' error for the following line:

VB : UpCount = PickNum - 6 + ((PickNum / 12))

UpCount and PickNum are both declared as Double, though this shouldn't matter. UpCount is being assigned a value here for the first time, and PickNum varies from 1 to about 250 depending on input.

Obviously I'm only dividing by a constant here, which is VISIBLY not zero. This error only occurs for certain ranges of PickNum...something like 50-70. Interestingly, in trying to debug it, I added:


...since PickNum was 54 as I was getting this error. Just dividing 54 by 12 ALSO got a div by zero error.

Perhaps I should mention I'm using VBA in Excel 2010 for Mac.

View 1 Replies View Related

Excel 2010 :: Hyperlink - URL Has Two Values In Which To Input Data

Feb 24, 2012

I wish to use a formula to grab data out of cells A1 & B1 and use that data to complete a hyperlink URL in cell A3. The base URL never changes but the last two variables do. For instance:

URL Format: http://www.test.com/XXXX&page=XXX

Example data:
Cell A1 = 1234 (always 4 digits)
Cell A2 = 567 (always 3 digits)

Desired Final Result, hyperlink: [URL] .........

I cannot seem to figure it out.... my formula i have gives errors:

=HYPERLINK("http://www.test.com/"(A1)"&page="(A2), "Linked")

This is Excel 2010

View 2 Replies View Related

Excel 2010 :: Pulling Cell Values Into URL When Using Get External Data From Web?

Feb 20, 2014

I am using Excel 2010. I am trying to make a spreadsheet where I can type in a date (02-19-2014), and pull in box scores from a basketball website. I have everything set up so that the date is parsed into (3) cells (02 19 and 2014) so that it can easily fit into the URL:


As you can see, the month value in the URL is "02" the day value is "19" and the year value is "2014". What I want to be able to do is type in any date I want and return the box scores from that day in a new tab. I have everything done so that the new tab is automatically created and named, so my only issue is that I can't figure out how to input the day, month and year values automatically into the code for the Get External Data pull:

'Import from www.basketball-reference.com
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.basketball-reference.com/friv/dailyleaders.cgi?month=02&day=19&year=2014" _
, Destination:=Range("$A$1"))
.Name = "dailyleaders.cgi?month=02&day=19&year=2014"

View 2 Replies View Related

Excel 2010 :: Data Validation Won't Accept Start And End Date From Cell Values?

Mar 16, 2014

Data validation in an Excel 2010 workbook.

I want the date input to be restricted a start date and an End date specified in two cells on the sheet. However, when I set up the Data Validation, ANY date will be accepted.

As an example cell Z1 contains the date 1/3/2014 and Cell Z2 contains the date 31/3/2014, so in the Data Validation box, the Start Date is referenced to Z1 and the End Date is referenced to cell Z2.

But I can enter 1/9/2020 and the Data Validation happily accepts that date.

View 12 Replies View Related

Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432

View 3 Replies View Related

Excel 2010 :: Conditional Formatting - Highlight Values Based On 2 Columns Of Numerical Data

Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:

Column L:

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies View Related

Excel 2010 :: For Loop And VLookup Don't Work

Aug 21, 2014

The for loop don't work in my excel 2010 macro. Probably the problem might be the Range("Fi") construct with the variable "i"?

Dim i As Integer

For i = 4 To 10004
Cells(i, 63).Select
Cells(i, 63) = Application.VLookup(Sheets(3).Range("Fi"), Sheets(4).Range("F:AY"), 45, False)
Next i

View 3 Replies View Related

Excel 2010 :: Search Gets Into Infinite Loop?

Feb 5, 2013

I have various spreadsheets that get into infinite loops sometimes when I search for things. Hitting break breaks it, and the search window (find all) shows the same exact entry hundreds of times.

This is Excel 2010.

View 2 Replies View Related

Excel 2010 :: Loop Through Every Line On Sheet?

Sep 30, 2013

How can I get this code to loop through every line on sheet?


Cells.Find(What:="Part Number", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

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

View 2 Replies View Related

Excel 2010 :: Selecting Or Activating Cell Within Loop?

Apr 28, 2014

After adding worksheets, data to ranges, etc. I want to step through each worksheet and "Select" or "Activate" a specific cell (Mainly to clear all the range selections).

My code does the job except the second to last part of cell selection/activation.

Win7, Office 2010

View 3 Replies View Related

Excel 2010 :: Loop Through To Check For All Filenames In Folder And If Then?

Dec 22, 2013

getting a function working within Excel 2010. What I'm trying to do is to first look in a specific directory and loop through each foldername then check to see if the foldername exists in column B of my worksheet. If the foldername exists then check Column E of the same row for a specific value ("assigned") and then make sure column F of the same row has no picture inside the cell. If those three things exist (column B foldername and column E "assigned" and no picture in column F), then add a picture to column F of the same row as the foldername with a hyperlink to a filename of "notes.one" in that specific folder. Then just loop through each foldername in the specific directory until all foldernames have been checked.

View 1 Replies View Related

Testing For Values That Vary Within Specific Tolerance?

Apr 22, 2014

I'm trying to identify where a set of values 'agree' within a specified tolerance.

I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.

To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.

I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.

Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.

View 2 Replies View Related

Excel 2010 :: Add Single Record To Multiple Column Listbox In A Loop From Array

Mar 19, 2014


I have an array PeopleList(6,320) that contains

PersonID, FirstName, LastName, Email, Phone, Notes

What I'm trying to do. I have two separate requirements:

(1) To add the whole array to a listbox on form initialization - see Sub UserForm_Initialize()
(2) To clear the listbox and re-add only certain items based on what's typed in a textbox - see Sub txtSearchTerm_Change()

I have two errors:

Error 1 in UserForm_Initialize()

The listbox contents need transposing! It is displaying as

1 2 3 4 5
Tom Ben Heidi Julie Mark
Smith Jones Evans Simpson Petersen
x@yo.com a@bo.com c@do.com e@fo.com g@ho.com
02071001022 02071001026 02071001027 02071001028 02071001029

When it should be displaying as
1 Tom Smith x@yo.com 02071001022 Friend
2 Ben Jones a@bo.com 02071001026

Is there a way to transpose the array?

Error 2 in txtSearchTerm_Change()

I cannot find anywhere - even on MSDN - all the information I need how to correctly add a single record to a multiple-column listbox! What I'm trying is:

For i = 0 To UBound(SearchList) If InStr(1, SearchList(i), SearchTerm) 0 Then
With lstPeople
For j = 0 To UBound(PeopleList, 1)
.List(c, j).Value = PeopleList(j, i)

[Code] ......

How do I add a record to the listbox????

Full code for reference:

Option Explicit

Private PeopleList As Variant
Private SearchList As Variant
Private Sub UserForm_Initialize()

[Code] .......

View 5 Replies View Related

Statistical Significance Testing With Excel?

Jul 12, 2012

I have simple data points based on a question that how many people liked product A between 2011 and 2012.

It looks like below

2011 2012
# of Respondents 319 329
% of respondents liking the product 78% 84.5%

The question is: is the increase from 78% to 84.5% statistically significant at either 95% or 99% confidence level. Is it possible to do this basic analysis using Excel?

View 1 Replies View Related

Excel 2010 :: Produce Unique List Of Key Values With Second Values Concatenated Together

Sep 15, 2013

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b

My system is Windows 8 Excel 2010.

View 7 Replies View Related

Hypothesis Testing On Data

Nov 4, 2006

Performing hypothesis testing on data comprising of hours devoted to patient care per week for practitioners in conventional and alternative medical practices. I have used data analysis -> z-test-two sample for means to perform my calculations.
I would like to know if this is the correct way to perform the hypothesis calculation. See attachment for more details.

View 2 Replies View Related

Excel 2010 :: VBA Loop Code To Automatically Email Range Of Cells And Email Addresses

Sep 16, 2013

I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.

I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.

A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."

The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.


Sub Email()
Dim rng As Range
Dim OutApp As Object


View 5 Replies View Related

Excel 2007 :: Loop Expression Won't Recognize Values Are Equal?

Apr 20, 2012

(Excel 2007) The issue I'm having is that a loop statement of mine is failing to recognize that a couple values are equal. It works for the first 3 iterations of the loop, then stops recognizing. I tried copying another set of numbers in the failing one's place, and that actually worked. I can't attach anything, so I'm including a link to a detailed screen shot with some notes on it and code that I'll host on my site. Not sure how else to explain the issue, but if you take a look at the screen shot you can see the excel sheet being worked on and the vba code running. I will also include a text file of the code

Here are the links to the screen shot and text file of my code
Text file
Screen Shot

View 3 Replies View Related

Excel 2010 :: Highlight Same Values In Different Worksheets

Jul 26, 2013

I have "Worksheet1" with Columns A and B for IP Addresses, then Column C is for a device name.

I have "Worksheet2" with a range of IP Address from F5:I260

When I enter a new device and assign it the IP addresses on "Worksheet1", I would like the IP Address to highlight in "Worksheet2"

This way I can keep track of which IP address I have used. Excel 2010

View 7 Replies View Related

Excel 2010 :: Sum Values Between Two Dates In Tables

Jan 27, 2014

Jan-14 Feb-14 Mar-14
Salary 10,000 20,000 30,000

Salary 30,000

I have a table above (in red), as an example, my actual table goes out 60 months.

Where the Green Value is, I need a formula that will reference the two months above it (They could be any of the 60 months), that will then sum the salary from the table between the selected two months (including these months)

I have tried,

tblCluster[[#Headers],[Jan-14]:[Dec-18]],">="&'Cluster Analysis'!$I$51,

View 2 Replies View Related

Excel 2010 :: Replace Values In A Cell

Apr 24, 2014

In Excel 2010 I am attempting to replace values in a cell, the right two values with "00", but am having difficulties with the correct '=replace' function.

View 2 Replies View Related

Excel 2010 :: Looking Up One Value And Need To Return Multiple Values Horizontally

Mar 28, 2014

I have in column A duplicate values and in column B different responses (Sheet: Lookup). I need to look up the value in column A (Sheet: Results) and bring back all the responses in column B (horizontally).

Nittie Query.xlsx

View 5 Replies View Related

Excel 2010 :: Countif And VLookup Of Multiple Values

Jan 15, 2014

I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.

I have attached a sample workbook as a reference : Sheet1.xlsx‎

View 8 Replies View Related

Excel 2010 :: Plotting Values V1 / V2 / V3 And V4 According To Given Date And Condition

Mar 25, 2014

Excel Version : MS Excel 2010
Attachment filename : <Forum to plot the values.xls>

My requirement is i want to plot the values V1, V2, V3 and V4 from the given condition (A1 / A2 / A3 / A4) and the given date.

If the given date is not available then the formula should take the older date than it not next date. For example if the given date is 10-Mar-14 and the date is not available for the given condition then it should take the older / earlier dates like 09-Mar-14 or 08-Mar-14 like and it should not take 11-Mar-14, 12-Mar-14.

The values should be plotted in the yellow cells.

View 1 Replies View Related

Excel 2010 :: Showing Multiple Values Per Cell?

Jul 31, 2014

When I update a cell (change A1 from 2 to 3), any cell that references that cell correctly changes its value (B1 = 2*A1). However, the screen will show the new value in B1 (6) over the previous value (4). At first I thought it almost looked like a strike-through, but then I realized the old value and new value were simply stacked in B1.

If I scroll the screen away that cell and go back to it, the correct value will now show without the stacked values. I'm not having this issue in any other program (Open Office), and I don't seem to be having any kind of stacking issue in any other Microsoft program.

View 5 Replies View Related

Excel 2010 :: Pasting Space Separated Values?

May 9, 2013

I have some text out of note pad in the following format

"cat" "dog" "bird" "turtle"
"cat" "dog" "bird" "turtle"

There is several lines like this. I need to copy it out of notepad and paste it into excel where every word in quotes is in its own cell. Right now if I paste it everything goes into cell A1.

I am using excel 2010..

View 4 Replies View Related

Excel 2010 :: Crashing When Pasting Formulas As Values

Nov 8, 2013

I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.

The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.

I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.

I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.

There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.

I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.


=IF(AND(N3>1,ROWS($1:1)<>MATCH(M3,$M$3:$M$7979,0)),"Exact Duplicate","")

is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.

View 1 Replies View Related

Excel 2010 :: Looking Up Values Across Sheets And Return Not Only Value But Also Sheet Name

Jan 14, 2014

I am looking up the largest value across various sheets (1 to 99) with the following formula:


That correctly returns the largest value in range B1:B50 across sheets 1 to 99.

However now I want to know the sheet name of the value above in a seperate cell, let say in: A3.
I'm using excel 2010.

View 3 Replies View Related

Excel 2010 :: Selecting Corresponding Values From Multiple Columns

Feb 18, 2014

I have a spreadsheet with two date/time columns 'Date1' & 'Date2'. Each date/time column has its own column with corresponding values ('Var1' & 'Var2'). These dates cover the same time period, however values for 'Var2' were collected less frequently than 'Var1'. I want ONLY the values in 'Var1' that correspond to the dates in 'Date2'

I am trying to select values from one column 'Var1' which have correlating date/time in column 'Date1' that match the dates specified in 'Date2'. Basically I need the values from 'Var1' that match the same date/time as 'Var2'

See the attached image to make it clearer..



View 3 Replies View Related

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