Do While Loop Statement Not Performing

Aug 5, 2008

I have the following code which previously was showing an error on the Do While line. It was suggested that I change the line too Range("B2").Text. However, the loop is not deleting the rows based on "#N/A". Can anyone offer any suggestions?

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess .....

View 9 Replies


ADVERTISEMENT

Use IF Statement In DO WHILE Loop

May 18, 2009

I have the following loop that should look through my rows of data for the Value "Y" in column T, for those rows that contain "Y" it should update column K with the variable xdate. For some reason it doesn't seem to work, it changes the first entry it finds and then seems to crash, I have to use CTRL+BREAK to stop it.

View 3 Replies View Related

Using MID On Range In For Each Loop With IF Statement?

Aug 8, 2013

I am trying to loop through a range, checking for a cell to contain one of two characters and then put a value in a cell to the side (well 3 away) of it. Im getting an error for Type mismatch on line:

VB:
If CellContent = "h" Or "v" Then

the data in the cell is an along the lines of '12.34.56.43 som-thi-vh-ng1'

VB:
Dim MyRange, MyCell As Range
Dim CellContent As String
Sheets("Sheet1").Activate

[Code]....

View 1 Replies View Related

How Can I Use An IF Statement To Loop LINEST

Sep 2, 2008

how to get my LINEST function to work properly. Now, what I'd like to do is have it reduce or increase the order of the polynomial until R^2 equals 1.0, or the polynomial reaches 6th order, whichever comes first.

Can I do this with a bunch of nested IF ELSEIF statements, or is there a cleaner way to do this?

View 12 Replies View Related

How To Input If Statement In For Loop

Jul 31, 2013

I have a code like this below

Sub Copydata()
Dim emre As Integer
For emre = 100 To 121
Sheets("CP TB").Select
ActiveSheet.Range("$B$8:$G$10000").AutoFilter Field:=3, Criteria1:=emre
Range("E8:F8").Select

[Code] .....

I want to skip values when the selection is a value I choose. I tried to put a "If" statement but I couldn't make it work.

The IF statement will check the selection after red colored code. if cell value is "a" then it will go for Next emre.

View 6 Replies View Related

Getting Error For For Each Loop With If Statement

Nov 9, 2013

I am getting the Run-time error '380'. Could not set the value propery. Invalid property value.

I have a user form for data entry purpose which is working fine. Now, I am making a code for data editing purpose which pulls out data from the worksheet into the user form.

I am able to do this for text boxes and combo boxes that are not linked with each other. However I am getting a problem in the following case; I have a combo box and a text box that are linked to each other by combo box _change code which is below which may be the reason for the error I am getting for the next code...

Code:
Private Sub cmbColor_Change()
If cmbColor.Value = "Other" Then
lblColor.Visible = True

[Code]....

View 6 Replies View Related

A Write Statement With A For Loop

Jun 15, 2007

So here is my problem:

how do you repeat a write statement four time

form 1234 to 1234123412341234

View 3 Replies View Related

Call Statement In Loop Only Once

Oct 16, 2007

If ComboBox1.Text = " Market 1" Then
StoreNumList = Array ("66", "67", "107", "222") 'obviously array string is much longer than this
For x = LBound(StoreNumList) To UBound (StoreNumList)
StoreNum = StoreNumList(x)
ComboBox1.value = StoreNum
Call btnOK_Click_Process
Next x

Program runs fine first step through. It calls the btnOK_Click_Process sub routine just fine. However on second and subsequent For/Next cycles it does not call btnOK_Click_Process.

View 9 Replies View Related

Excel 2007 :: Do Loop With IF Statement

Nov 12, 2013

I am having an issue getting an IF statement to work inside a DO loop. I have Microsoft Excel 2007 and Windows 7 Entreprise.

In my Excel spreadsheet, I have in column B a list of keywords and in column C the category to which each keyword belongs. For instance, the keyword "boot" goes withe the category "Shoes", "scarf" goes in "Accessories", etc.

In another column, I have a list of product descriptions (like "Black Boots" or "Silk Scarf") and I would like to assign the relevant category to each product. I thought to do this by creating a new function in VBA. This function would go through the whole list of keywords in column B, checking if the keyword is included in the product description. If it is, the function's result would be the relevant category, and if it is not, the function would search the next line in column B, until a relevant keyword is found, or until the end of the list.

Here is the code I have written so far:

Code:
Function SegmentSearch(Item)
Dim i As Integer
i = 1
Do
i = i + 1

If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE" Then SegmentSearch = Cells(i, 3)
Loop Until Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE"
End Function

Without the loop, the function works fine (if "Item" happens to include the first keyword in the list) but as soon as I try putting the loop I get a #VALUE! error.

View 7 Replies View Related

If Statement Causing Loop To Fail

Dec 28, 2006

My nested loop works as intended without If statements, ie it counts non blank cells in a series of 17 ranges (this loop is called the CountRangesOnEachRow loop) AND then drops down a row and repeats (this loop is called the RowNo loop). The problem is that once my first If statement occurs the RowNo loop fails and it stops at the first row.

The first If statement is designed to do the following:

1. Tests to see if A4 has data. If yes, copy cell contents (pupil's name) to Sheet(2).Range("StudentName")
2. If empty skip the countranges loop and move down a row to A5.
3. Cells A4:A35 need to be tested for data in this manner.

I've included a sample workbook & code with the IF statement comment blocked so you can see what it should do AFTER it tests the A column for data. The macro should end after row 6 as A6:A35 are all blank.

View 9 Replies View Related

Setting Value In For Each Statement Stars Infinite Loop

Sep 17, 2009

Setting value in For Each statement stars infinite loop. I am trying to get the following script to work

View 2 Replies View Related

Nested Next Loop: Evaluate The If Statement Below Over A Range Of Cells

Aug 22, 2008

I think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells

Sub Analysis()

Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer

TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row

For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................

View 9 Replies View Related

Looking Up 2 Conditions Before Performing Another Task?

Jul 12, 2013

I'm a fairly new user to using VBA and I'm currently running into a little snag. I'm trying tu create userform which will let me know if a person already exists in my sheet by looking up the last name in "A", then looking up his first name in "B", if such entry exists then display a message saying " Yes". If entry user has not been entered in the sheet then use a combox, to select the reason , then choose a date ( i have a working date picker ). Then entry should be entered in sheet (Name, Surname, Reason, date).

Whats working, I can use all of the above to enter all the info into the sheet, what I need to do is verify if user is alread in there, if not, then add it. The so on and so forth

View 3 Replies View Related

Performing Calculations On My Comments!

Feb 26, 2008

i've managed to get a set of cells to show (with comments) what the last weeks data looked like. (so cell value = this week, comment contains last week)

What i now want to do is to use conditional formatting to highlight any major differences between the figures... so:

My comment i'm sure is stored as text in the format:

Previous value was
xxxxx

So the first thing i'd need to do is to to strip out the text at the start, and then perform the calculation.

View 9 Replies View Related

Data, Performing Functions, And Percentages?

Mar 13, 2009

I have a very large grid of data, about 400Rx200C, which contains a lot of blank fields. All of this data is currently in values from 0-100 representing percentages, but not formatted as such. I need to make all of these values into Excel Percentages so naturally used Paste Special and Divide from a field with the value 100 in it and then simply format the cells as %.

However, this fills in all of the original empty cells with a 0.000% which I do not want, I need them to stay blank. And some of the cells that do have values also have 0.000% so I cannot just delete all of these values with a VBA script. I had been hoping the Skip Blanks option would do this, but it does not unfortunately.

View 3 Replies View Related

Opening Files While Performing Vlookup

Sep 22, 2009

I am trying to write a Macro that will do the following.

1. Using a new workbook
2. Open a file in a in a directory. Then
3. Run a vlookup using the opened file as the filename in the vlookup
4. Paste the results of the vlookup into the original file next to the file name.
5. Close the file and open the next one in the folder.

The folder contains about 2k files that it needs to loop through. I found a macro on this site created by Ravishankar and attempted to add to it. Not going so well.

View 2 Replies View Related

Subroutine Stops Before Performing All Commands?

Jun 17, 2013

I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:

Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures

[Code]....

View 4 Replies View Related

Setting Range Of Numbers And Performing Subtraction?

Mar 28, 2014

I am creating a range of numbers from 1 to 2000 with increments of 0.5. Then I have to subtract certain range, for example, 100 to 350. So, the result should display, 1 to 95.5 and 350.5 to 2000.

View 4 Replies View Related

Assigning Value By Performing Search Relative To Position

Jan 19, 2012

The data is a copy of CSV file in to a spreadsheet. Below is what the paste looks like:

100100 Books
1234.ab
1345.bf
1456
1567
100100 Books
100200 Magazines
100300 Videos
1234.ab
1345.bf
1456
1567
100300 Videos

All of the numbers between the 100100 Books, 100300 Videos belongs to that category. This repeats hundreds of rows down. I need the column to the left of this range to display which division it under. I parse this in excel so Books, Videos, Magazines will be in their own column. I am not exporting the CSV so have to manipulate it in excel. I need to have the column to the left combine the whole identifier so I can perform a VLOOKUP on it. It should look as follows:

100100.1234.ab
100100.1345.bf
etc.

My approach was to get the column to the left of the range to display the correct division, then have another column combine by using (cell)&"."&(cell).

View 1 Replies View Related

Breaking Time Into Intervals And Performing A Count

Nov 20, 2006

Office 2003 with Windows XP

This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want

I have an Excel sheet with data

A1=date
b1=source
C1-h1 all contain data (name, location, etc, etc) but I am not interested in those.

A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM

[Sample]

a2 = 8/1/2006 12:20:23 AM b2 = phone
a3 = 8/1/2006 12:21:13 AM b3 = email
a4 = 8/1/2006 12:33:03 AM b4 = phone

This is what I am trying to accomplish:
I want to break each hour, whether it contains data or not and get a count of the different types of source

my desired output would be:

time Phone email
12:00AM
to 2 3
12:30AM

My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time.

I was going to perform the following:
=text(a2, "h" + if("mm:ss">30, 30,00)

Basically, pull the hour from a2 and evaluate the mins/secs.

If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval.

Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.

View 9 Replies View Related

Formula Stops Working After Performing Certain Tasks

Nov 21, 2006

I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:

=If(ISNUMBER(MATCH(MIN(If( Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(If(Date=D2,If(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),If(Date=D2,ABS(Time-E2)),0)),"")
Everything works fine, until I attempt to…
- cut & paste a block of cells, or
- do a SaveAs, or
- change a worksheet name

The formula will no longer function after performing any of these tasks (the cells go blank), and I have to close the workbook without saving to preserve the original workbook functionality. All other tasks, including even importing of different files into the workbook have NO negative effect.

The formula uses an “INDIRECT” named range. Sheet name changes are reflected in the named ranges, but any change of names somehow causes a malfunction (no sheet names are referenced in the VBA).

I thought that the large lookup workbook (40,000 rows) may be a problem, but even after deleting most of the data and retaining only a few rows, the problem still remained.

After the formula cuts out (after performing the above tasks), if I open up the lookup file, then the lookup will work again, but if I close the lookup file, it will stop working.

I doubt the problem is in the VBA code since IT WORKS, and besides, I have other simple VLookups formulas that also uses the same process (and VBA code) and they continue to work just fine when that one formula gives out. The only thing I can think of is that the complexity of that one formula may be an issue.

Anyway, I the original thread is long, so I’m starting a new one here. The original thread is here (Domenic’s formula, pg 7; FFF’s code, pg 8):
Dedicated Cell To Choose Lookup Table

View 9 Replies View Related

Performing Worksheet Functions On One Column Of A Two Dimensional Array?

Nov 19, 2009

I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?

View 4 Replies View Related

Removing Duplicate Information In Same Cell After Performing VLookup?

Apr 8, 2014

I have used a vlookup code from a past post (Vlookup multiple values in one cell) in VBA and successfully retrieved my information to one cell.

However, I would now like to remove any duplicates in this cell; preferably while running the same code.

--vlookup code
Public Function mVLOOKUP(mlookup_value As Range, mtable_array As Range, mcol_index_num As Long)
Dim r As Range

[Code].....

View 2 Replies View Related

Locating Next Similar Condition In A Range And Performing Calculations

May 11, 2007

I have a set of data with names in col A, Currency in col B, Amount in Col C. I want to convert all the amounts in NON-USD based on exchange rate I input daily into column D.

How do i search down the row for the next name that is denominated in GBP / YEN etc and keep converting until I dont find any more other currencies in it. I tried to improvise on the finding nth but couldnt get anything out of it. Have attached a spreadsheet.

View 9 Replies View Related

Performing A Specific Function Repeatedly But With Variable Values Each Time?

May 13, 2014

The spreadsheet has over 200K rows and two dozen columns. My job is to:

1. There are two columns, one is called OCR code where we have repeating ID's and other column is called Minutes Count which is basically a number. I have to filter the OCR column for similar OCR codes(values) and then add their time(Minute Count) so that I get total minutes for a specific OCR code. After adding the min I will copy them into a new sheet with the code in one column, total minutes in another and number of repeatitions for that specific OCR code or count number in a separate column. Example:

OCR_Code; Minutes
54xg; 456
45fk; 65
23IS; 18
54xg; 971
45fk; 265
.
.
.

Now I've to filter 54xg so that I can only see it and its corresponding minutes. Then I will add up all min for 54xg. Then I will count the number of instances a 54xg has been repeated in spreadsheet(here its 2 but it can be in hundreds in my case). Last thing is to transfer all this data to a new sheet. And automatically repeat the process for other OCR codes like 45fk, 23IS etc...

View 3 Replies View Related

Need First Statement To Loop Until "False"

Jul 27, 2009

I have a worksheet built that obtains info from Sheet1(ACV) and enters it into Sheet2(Payment1), however when I run the process, only Line 10 fills and then it stops. I need to loop the first statement of the code, so that it fills all of column 1 until there is no info to fill then I assume the rest of the code will fill in the worksheet accordingly.

View 3 Replies View Related

Performing IF Cell Contains Number THEN Copy That Number ELSE Give 0

Jun 14, 2013

Looking for a way to search an active cell (that has been found using macro) for numerical data and if there is some present paste this in a different location

If there is no numerical data in this this cell then paste a 0 in a different location

An example of the string contained in the cell

Product 7.5% wheat <- i would want 7.5 (or 7.5%) copied to a different cell
or
Product X <- i would want a 0 copied to the cell the 7.5 would be in from the above

I can find the cell to search in, i just do not know the correct way to extract the information I need.

View 9 Replies View Related

Utilize A Select Case Statement In Target Intersect Statement

Jul 28, 2009

I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.

My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.

I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?

View 5 Replies View Related

Loop Within A Loop (repeat The Loop Values)

Mar 31, 2008

With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!

View 9 Replies View Related

Loop Through Multiple Files And Call Macros (but Unable To Loop)

May 14, 2014

Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.

Why it stops after one file when using "Application.Run..." to call the macros?

NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.

Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long

[Code]....

View 4 Replies View Related







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