I have the following data
Country
Aug-13
Sep-13
Oct-13
Nov-13
UK
3
5
3
2
USA
10
14
16
13
France
1
5
7
9
What I would like to do is sum a specific range of this data based on three criteria. Each criteria is in its own cell.
- Start Date
- End Date
- Country
So if I wanted to sum from 'Sep-13' to 'Nov-13' for 'USA', the formula would give me the answer 43.
I will have a list of unique values. For each unique value, there is an indicator that tells me how many lines of information I need. Based on that criteria, I need a macro to insert that many rows and populate it with text. This will ultimately become a look up table that I will just use index/match function to populate.
I've attached a sample sheet. I'm not sure if I need a macro to insert rows or a macro to paste a copied range. Or maybe I need a mixture of both with a formula to calculate the number of cells I need?
I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.
Sub MoveRangeOfCellsBasedOnCellCriteria()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp))
For Each cell In myrange
If IsNumeric(Left(cell.Value, 1)) _
Or Left(cell.Value, 5) = "UNIT " _
Or Left(cell.Value, 4) = "THE " _
Or Left(cell.Value, 5) = "FLAT " Then
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
cell.Offset(1, 1).Range("A1").Select
End If
Next cell
End Sub
Code is trying to
1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.
Recieve run time error 1004: Method 'RAnge of object_GLobal failed
===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3
[code].....
Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet).
Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.
This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.
I need to sum values from the same column, in different rows, but is there a way to sum each value based on the name in the first cell of the row. Since I realize I probably did not word that well, I will give an example. The data would be as follows
Bellcrank 3
Bellcrank 5
Gearbox 2
Bellcrank 6
Is there a way I could just sum the Bellcrank values? I understand that i could just use the sum function in this case, but I want the sum to automatically update if another bellcrank row is added.
I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.
I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.
I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.
Trying to sum a range based on the criteria of the 1st position within each cell in the range. Cells have data like "A-8", "A-4", for a total of 12 for "A". This is the formula I have that is not working.
=SUMIF(left(C4:P4,1),"A",RIGHT(C4:P4,1))
I would like Excel to return me a value based on many criterias in the same worksheet.
E.g
A
B
C
[Code].....
QN: For any of the value from Col B to Col N, i want the result of Column A appearing in my cell.
Need to select a range from colum A7 to E20.
Condition.
1) if the last colum (E20) should be selected if in the colum A equal to "Thank You".
I am copying from cell A2 to C2, then A8 to C8, etc. if the cell in column "A" contains the text "Item" until the end of data in column A. Here is what I am attempting:
Sub MoveItem()
sText = "Item"
sText = LCase(sText)
iMax = Cells.SpecialCells(xlCellTypeLastCell).Row
For i1 = iMax To 1 Step -1
If InStr(1, LCase(Cells(i1, 1)), sText) <> 0 Then
Cells(i1, 1).Copy Destination:=Cells(i1, 3)
End If
Next i1
End Sub
The problem is the destination location. If I define it as a Range eg: Cells(i1, 1).Copy Destination:=Range("C2") The macro works, but does not iterate the destination cell.
I am trying to copy a range of cells, based on a set of criteria that changes constantly.
In Column A, I have numerical values that are present every 10th cell. In columns B through D, I have various text data that corresponds to the number in Column A. So for example, in A50, I have the number 46975. In the range B50-D59, I have text data that goes with the number in A50.
Each day I run a few calculations and based on the number I get, I want to find the exact match in column A, then copy all the data in columns B through D that are related. So if my calculations result in 46975, I want to automatically copy over to another worksheet the range B50-D59.
I have a table of data with which I need a macro to clear a range of data based on certain criteria. I have attached a file as an example. In this example the current period is 6. I therefore need to clear columns which have a period of 7 or higher.
Essentially, if the current period is x, then clear the range in the period columns if the period is greater than x.
which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5)
But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere?
Private Sub CommandButton1_Click()
Dim rngFind As Range
Dim strFirstAddress As String
With Sheet4.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Do
rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If ..................
I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.
Row A Row B
25 John Smith
50 Mike Jones
25 Chris Carter
50 Mike Jones
I want to be able to count one number in "A" but only if the name is not duplicated in "B".
My result for #25 would be 2.
My result for #50 would be 1, because Mike Jones is duplicated.
The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.
this is what I have come up with....
sumif(N10:N49, "Business", AW10:AY49)
but it returns incorrect values and not the whole range????
Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...
H2 and H3 are my attempted formulas
I would like to ask how can I get to work the index match function (if there's any formula other than this current function its fine) if i have a start date and end date as range date then another criteria for name to get my desired result. im having a hard time to explain so i attached a workbook so you can around it.
I found a formula and tweaked it but still cant get to work.
I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this.
For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
rng = Range("F" & Target.Row & ":M" & Target.Row)
If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value = "Yes" Then
Application.EnableEvents = False
rng.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
I have 2 ranges: 1."Task info" (4x4 range)
2." Dates" (24x2 range)
I have one variable cell ref: "Date1" (a date)
Based on "Date1" I want to copy the value in the "task info" and Paste special value that is next to the corresponding date in "dates".
I'm trying to do a sumif based a range of dates but keep getting a "-" for the result.
In the past when using dates I've had to value the dates in order for it to work. I can't seem to figure out the formula this time around - any thoughts? See attached for sample - my 'broken' formula is in cell C29.
Here's what we need to do:
We are compiling one master spreedsheet from many smaller ones and I need to figure out how to copy them quickly and efficiently. For example:
Small Spreadsheet:
Column B is marked as P, M, C or S. This represents the type of the account.
Column C is the name of the account.
Master Spreadsheet:
We have a column for each account type (P,M,C,S)
Can we use a formula/macro to automatically copy columns B and C from the small sheet and organize them accordingly in the Master Spreadsheet?
For example:
Small spreadsheet:
P Account Name1
M Account Name 2
M Account Name3
C Account Name4
M Account Name5
would appear in the Master Spreadsheet as:
P M C
Account Name1 Account Name2
Account Name1 Account Name3 Account Name4
Account Name1 Account Name5
I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).
View 6 Replies View RelatedI have data like in excel, which I uploaded and I need "simple" thing, If in column G is text Australia then copy from D column each cell which has "AUS1*" + copy the same number cell from C column and copy it into cell A and B. I hope I wrote it understandable
View 2 Replies View RelatedI'm not sure why this doesn't work on the dummy data?
Problems:It doesn't recognize the September 2011 datesIt is supposed to pull dates between July and August (i.e. 2 dates) and pull everything except those dates.
The date strings have to be variable based since it can't be hard coded.
Code:
Sub copyrow()
Dim rc As Integer, row As Integer, i As Integer
Dim mm As String, fdt As String, pdt As String, mo As String, yr As String
Dim Date1 As String, Date2 As String
mm = Month(Date) - 1
mo = Format(Now(), "mm") 'ex. "08"
yr = Format(Now(), "yyyy")
Date1 = mm & "/01/" & yr
Date2 = mo & "/01/" & yr
[code].....
I am really struggling to summarise weekly budget data into period budget data by subcategory.
I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.
I have a query in relation to locking cells to disallow editing based on a dynamic range. The VBA code I have thus far is this :
Sub PasteForecastWO()
rowarr = Array(10, 26, 28, 69, 72, 79, 81, 87, 89, 104, 106, 114, 116, 122)
For Each ce In Range("S8:AD8")
If ce = "ACTUAL" Then
For i = LBound(rowarr) To UBound(rowarr) Step 2
Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value = Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value
Next i
End If
Next ce
End Sub
What I would like to do is lock the range that applies as the variable "ACTUAL" moves or changes along the range S8:AD8. I have attached the relevant sheet so that you may get a clearer picture.
I need a macro that will check that the cells B1,C1,D1,E1 and one of the cells F1,G1,H1,I1 are filed out upon entry in A1.
If Not IsEmpty(.Range("A1")) Then
If WorksheetFunction. CountA(.Range("B1,C1,D1,E1")) <> 4 Then
MsgBox "All cells in row 1 are not filled out"
Application.Goto .Range("A1:I1")
Exit Sub
But how do I get it to also check that either F1 or G1 or H1 or I1 is also filled out. If possible I also would like it to beimpossible to add text in more then one of the cells F1:I1. Summary, upon entry in A1 I want cells B1:E1 plus one of the cells in F1:I1 to be mandatory, so in total there will be 5 mandaory cells.
The begining portion of this works fine, I have it moveing data from one column to another, but I then need it to combine two rows (BW and P) and then place that into column (O)...
View 3 Replies View Related