Applying A Mid Function To Many Rows

Jun 14, 2007

I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.

View 4 Replies


Use Sumifs Function Applying 2 Criteria?

Aug 21, 2013


Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is


However it is giving a value of zero only. I have tried other combinations as well.

View 4 Replies View Related

Applying Function To Certain Cells Only Based On Another Column

Aug 17, 2014

Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A

It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.

View 2 Replies View Related

Applying Function Arguments As A Control/class Name

Mar 16, 2007

I'm not sure what verbage to use in order to describe what I'm trying to accomplish.

Let me give you a piece of code and the question will be clear.

Function addValuesToComboBox(arg1)
End Function

I would like to use this function later in the code in the form of:


VBA will error out with "Run-time error '424': Object required". I know I need to declare the variable arg1 as a type in order to use it in this form, but I don't know the correct data type.

I also ran across a similar issue like this where the advice was to use the Set function in the form of:

Function addValuesToComboBox(arg1)
Set arg1 = arg1.AddItem("one")
Set arg1 = arg1.AddItem("two")
Set arg1 = arg1.AddItem("three")
End Function

View 9 Replies View Related

Extracting Consecutive Letters From Cell And Applying IF Function

Mar 8, 2013

I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.

If A1 contains the letters "isd" consecutively, then B1=TRUE

If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true

View 4 Replies View Related

How To Use COUNTIFS Function Only For Visible Data After Applying Filter

May 27, 2014

How to use COUNTIFS Function only for the visible Data after applying filter.

Ex:=COUNTIFS(A:A,"Ret",P:P,"M")-COUNTIFS(A:A,"Ret",P:P,"M",B:B,"") it gives d result including hidden data, but i want it only for visible data after applying filter.

View 1 Replies View Related

Applying Formula In A Column For New Rows Inserted?

Jul 23, 2014

I've applied a basic multiplication formula =D12*E12 in a table Column F, also I've filled it throughout but now if new rows are inserted in between or at the end of the table, new cells in this columns doesn't include this formula.

View 1 Replies View Related

Applying Formula Across Columns For Multiple Rows?

Mar 3, 2014

I have a formula that I wrote in B2 and then applied vertically to a large list of rows (100+). I applied it vertically by simply double-clicking the cell with the formula, and it cleanly applied the formula to every row in Column B.

This formula now needs to be applied horizonally across columns, for each respective row. I need a way to do this in one swoop, as it would be very time consuming to manually drag each cell in column B.

I've attached a sample spreadsheet with a simple formula just for the sake of an example.

View 3 Replies View Related

Selecting Visible Rows Post Applying Filters?

Apr 7, 2014

I have an excel spreadsheet(Sheet1) with information in 35,000 odd trade lines, which is eventually compared against another set of records from another tab (Sheet2) in the same spreadsheet.

Have recorded a macro which does the lookup using the common parameters and returns the unique identifer in Sheet1 from Sheet2. Post updating the UID's in Sheet1 - range O:O, I need to select all the rows which are not matched and move it to another tab.

I apply filter on column O1 and select #N/A and select the complete range. Via VB I use the below code to move between to the visible row from filtered row:

ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select

If the visible row is within the first 1,000 rows, it doesn't take much time. However if the same is around 25,000th row, it takes more than 5-6 mins in order to move to the visible row.

View 1 Replies View Related

Applying Formula To Dynamic Number Of Rows For Different Files?

Feb 20, 2014

I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.

I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.

For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.

View 8 Replies View Related

Applying Percentages

Jun 17, 2008

I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:

Mike Dan Bill Carl
400 500 600 700
800 900 1000 800
700 600 500 400

In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.

I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.

So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.

Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.

View 9 Replies View Related

Applying IF To Multiple Cells

Jan 9, 2014

I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.

= 10% is AWARDED
>10% <90% is IN PROGRESS


View 8 Replies View Related

Applying Date Range

May 18, 2009

To explain I have two sets of merged data. On one fixed column I have two different sets of peoples age

1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983

I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34

I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order

Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have.

View 6 Replies View Related

Auto Filter Not Applying?

Feb 25, 2013

Using VBA in a code. I was using this same code for another workbook before and it worked fine.

[Code] ...

The filter works, but it is not applied. after the macro is run, I need to click "OK" on the filter for it to apply.

View 7 Replies View Related

Applying Same Formula To Entire Row

Feb 20, 2012

I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:

Code: [URL] ........

A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.

View 3 Replies View Related

For Each Command Not Applying To Other Worksheets

Jul 8, 2013

My issue is my For Each command is not grabbing the next worksheet and applying my code. Here is my code:

Sub Test()

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.LeftFooter = "&D"
.CenterFooter = "Test"
.RightFooter = "&P"
End With

End Sub

View 5 Replies View Related

Applying One Formula To 2 Columns

Feb 8, 2014

So right now I have a formula that I'm using in column "C":

=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))

So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.

So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.

View 3 Replies View Related

VBA: Applying The If Statement To All Cells

Mar 3, 2008

I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?

Sub MACRO1()

If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If

If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If

End Sub

View 9 Replies View Related

How To Specify The Sheet Name For Applying A VB Code

Aug 22, 2009

have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
'Allow printing
End If
End With
End Sub

View 9 Replies View Related

Applying Colorname From Another Cell

May 3, 2006

I have cell A7 Merged and need info in that cell. Example of what I want: Say I click on cell A1 and it is the color Pink, but has the letters blue in it. I would like it to display the word "Pink" in cell A7 not the number. Well I would like when you click on any cell with colors, that it will display the color in Cell A7. When I try different ways all it gives me is the number in the last cell of the square not the color and when I click on other cells it will not give me that cells info.

Private Sub CommandButton1_Click()

Dim x, rng As Range, r As Range
Dim myList
Dim row1 As Integer

x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng. CurrentRegion.Clear

For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)

Range("A7").Value = r.Interior.ColorIndex

End With


With rng
.ColumnWidth = 10
.RowHeight = 50

With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With

.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With

End Sub

View 9 Replies View Related

Applying Formula To Drop Down List?

Feb 14, 2013

Cell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1

View 2 Replies View Related

Applying Formula To Many Cells That Already Contain Values

Jun 8, 2014

I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.

My column contains the values
in separate cells

I want to divide each value by 1.2 - without having to change every individual cell - can it be done?

View 2 Replies View Related

Applying Macro To Selected Range

Apr 3, 2014

I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.

View 3 Replies View Related

Applying Macro To Entire Spreadsheet At Once

Feb 6, 2009

For simplicity, let's assume I have three columns, "Company," "Policy," and "Balance."

Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2

According to my post preview, the columns are running together, but I do have it arranged in three distinct columns. What I want to do is program a macro to do the following:

Every time the name of the company changes, I want Excel to insert two rows after the last of the group, and have a summation of the "Balance" column in the first inserted row.

This is what it should look like:

Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
$10 <- (this should be directly under the $4, but I'm not good with forum languages)

Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
$23 <- (same with this - should be directly under the $4)

View 3 Replies View Related

Applying A Percentage To A Long Column

Jan 9, 2009

I have a long column of numbers that I want to apply a single percentage to all the numbers in each column. How do I do that?

View 3 Replies View Related

Applying Multiple Formatting To A Cell?

Dec 2, 2011

In Excel we can apply different formatting styles to the "TEXT" contents of a cell while we are in edit mode. For example we can apply "BOLD" to Brown Fox and "UNDERLINE" to lazy in a string "A quick brown fox jump over a lazy dog.

But when the above string is concatenated from the contents of various cells, excel is unable to apply different formatting (as exampled above) in the concatenated cell. Although we can apply bold, underline etc by selecting the concatenated cell but it is applied to whole string and not to the selected parts of string.

Sheet1 *A1Brown2Fox3Lazy4*5A quick Brown Fox jump over a Lazy DogSpreadsheet FormulasCellFormulaA5="A quick "&A1&" "&A2&" jump over a "&A3&" Dog"

Moreover, MS Word's "mail merge" is capable to apply different formatting to different "Fields" in a single paragraph.

Is there any way so we can apply multiple formatting to the contents of a single cell which is not "Text" but result of "concatenate" in Excel, like MS Word's mail merge or as it is applied in custom format within straight brackets like [Red] etc.

View 3 Replies View Related

Excel - Applying Color To Cells?

Sep 19, 2013

I am using two separate workbooks. I am tranferring dates into a new spreadsheet to track projects dates for milestones.

The formula I am using is this: =IF('[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$CQ$4="", VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 94, FALSE), VLOOKUP(W54, '[BBBBB Dates as of 9-9-13.xlsx]Sheet1'!$1:$1048576, 95, FALSE)).

I would like the cell to turn green if the last part of the formula is true and stay clear if the first part of the formula is true.

I also need to add color beyond just that. I was attempting to apply conditional formatting but am a bit stumped. I want the green to be maintained regardless of other formatting. I would also like to apply to cells that are not color coded green:

Red - if the date is overdue
Yellow - if the date is within 7 days
Otherwise, leave the cell color as clear

Is this possible with conditional formatting? I am not at all proficient in VBA...

View 9 Replies View Related

Applying IF Condition In Excel In A Cell?

Sep 21, 2013

I want to have a condition which will check if the value in cell is present or not and based on that i have to display result in a cell.

Eg: In Sheet1, in column D i have 4 values (Simple, Complex, Medium Complex, Very Complex)

In Sheet 4, we have numeric values to Simple, complex...Like below

Complexity Estimation
Simple 2hrs
Complex 4hrs
Medium Complex 2.5hrs
Very Complex 6hrs

In Sheet5, we have values defined in % like below:

Requirement Analysis



System Testing


In column G of Sheet1, we have to write a function or formula which we do below calculation.

IF (Sheet1!D3="Simple") then Sheet1!G3=Sheet5!B2(ie.15%) of Sheet4

Similarly i will have Sheet1!D3 values as Complex,Medium Complex,Very complex

View 2 Replies View Related

Applying Same Formatting Across Multiple Sheets

Aug 20, 2007

There is currently data on each sheet in my workbook. I am trying to get a macro to Copy the formatting of sheet "Statement" and paste the formatting on all other sheets in the workbook, except Sheet.actual

There is also a picture @ the top of sheet statement, that i would like copied and pasted to each sheet except sheet.actual.

Also, on all sheets except for STATEMENT & ACUTAL, i need the formula in cell C11 =vlookup(F14,cardnum,2,false) and in cell A11 = Name :

View 9 Replies View Related

Applying Filter To 'Coloured' Cells

Dec 28, 2007

Is it possible to apply Filter utility on Column Cells that are 'coloured'?

View 9 Replies View Related

Copyrights 2005-15, All rights reserved