The code down below selects the 4 lowest values in Range("A1:Z1") and gives them a red color.
The problem; if there are only 1,2 or 3 values in Range("A1:Z1") instead of 4 then it errors.
My query; how should the code be modified so it also works when there are less then 4 values to process?
I have the following table:
Month / Year / Value
JAN20060,73
FEB20060,76
MAR20060,76
APR20060,76
MAY20060,73
JUN20060,69
JUL20060,69
AUG20060,73
SEP20060,76
OCT20060,75
NOV20060,74
DEC20060,72
JAN20070,67
FEB20070,68
MAR20070,71
APR20070,75
MAY20070,75
JUN20070,74
JUL20070,71
AUG20070,68
SEP20070,67
OCT20070,7
NOV20070,73
DEC20070,75
I want excel to multiple the values in a specific range.
For example:
I will give 2 dates: MAR 2006 and MAR 2007. Excel should multiple the value 0,76 and 0,71. It's simples because is 2 months. But I have a lot of months (2006 until 2009).
So I would give MAR 2006 and MAR 2009 and excel should multiple the values from MAR 2006, 2007, 2008 and 2009. All the values in the range, but only for March.
The month will never change between the first and the last date.
I think on using VLOOKUP. Then I have the first value and the last one. But how can I tell excel to multiple the range?
The ideal will be a VLOOKUP that returns the ADDRESS of the cell, not the value. But I didn't see this possibility.
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
I have .csv file from which the data is importing to master schedule. i have a column in .csv file which is spitted into multiple columns. and my need is i have to compare this each individual cell value with the range of header values in master schedule and if match found i have to place that cell in the row.
I am adding 2 attachments one is .csv and other is master schedule.
I couldn't able to find where i have to attach my files.
I want to build a database where the user can select a specific time period. I have a static file with the time periods and the related values. For instance I have in column A1:A15 the time periods from January 2011 to Dezember 2011, but I have some dates multiple times as there are more values attached to it. For instance I have in the first 4 rows January 2011...what I want is when someone puts in the value January 2011 in cell B1 and Dezember 2011 in cell B2 that the whole static file gets copied to another location (including the multiple dates) displaying the chosen time period. Similarly if someone puts in the value March 2011 to November 2011, I want only those values to be copied.
View 4 Replies View RelatedIs there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.
=AVERAGEIF($E$4:$E$34,">0")
=AVERAGEIF(E4:E34,">0")
I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?
/code
Dim cell As Range
For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp))
If cell.Value = "xx" Then _
cell.Value = "01"
Next cell
/code
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
I have is looking up the values between 2 numbers in a row, and match it with range of values between the numbers in a column:
Example: ColA ColB
Row1 3 7
Row2 5 10
and so on...
Column C
R1 3
R2 5
R3 7
R4 9
R5 11
R6 13
and so on...up to 20 rows
What I want is match the range of values between the 2 numbers in ColA & ColB with the range of values between the numbers in R1 to R20 in ColC,
Then in ColD I want it to display "Y" beside the numbers if the range of values of Row1(3 to 7) matches with this range of values in ColC
Then in ColE I want it to display "Y" beside the numbers if the range of values of Row2(5 to 10) matches with this range of values in ColC
So what I like to see is...
in ColD,
"Y" beside 3,5 and 7
in ColE,
"Y" beside 5,7 and 9
and so on...
i want to put a subject for a negative values, how should i do it?
For example:
lookup_value: -1000
table_array:
<0 - Poor
0-10000 - Good
> 10000 - excellent
is it possible to do this? ALternatively see the (new) attachment for clearer question.
i have a very large spreadsheet filled with telephone numberS and some other codes that go with them...i need to match the codes with the phone numbers.
Sub TRCO()
Dim TNs As Long
Dim i As Long
Dim TempArray() As String
Dim TRCO As String
Dim CD03 As String
Dim ASOC As Range
TRCO = "TRCO"
CD03 = "CD034DF1"
'Filter TN's
Columns("B:B").EntireColumn.Insert
Set tempRange = Range("A6", Range("A65000").End(xlUp))
With tempRange
. AdvancedFilter _...................
I have two named ranges 'wrkNRP' and 'wrkQTY'.
Instead of totalling each range seperately, I need a way (within VBA) to go through every value in both ranges and mutiply them together, then record the total- e.g.
wrkNRP has the values
10
20
30
40
wrkQTY has the values
10
20
30
40
Then I need a way to do (10*10)+(20*20)+(30*30)+(40*40)
Is this possible WITHOUT adding an additional column?
I have a simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.
View 3 Replies View RelatedI 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'm trying to do is search a range of cells for a range of values AND then see if it matches one other value in another range of cells. In this case,
=SUMPRODUCT((T3:T49="P6")+(T3:T49="P5")+(T3:T49="P4")+(T3:T49="P3"))*(U3:U49="w")
T3:T49 can equal P6 or P5 or P4 or P3 but the cells can only be counted if U3:U49 is 'w' as well
1. I have two workbooks (eg. workbook1 and workbook2)
2. I compare the cell values in workbook1.sheet1.cell range (d6:d20) and workbook1.sheet2.cell range (d6:d20).
3. If the values in the range of cells are same, I want to take the value in workbook1.sheet2.cell range (d6:d20) and copy to workbook2.sheet1.cell range (d6:d20).
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.
View 3 Replies View RelatedI have the following formula: =IF((COUNTIFS(data_sheet!U:U,Monthly_Report!H10))>166,0,COUNTIFS(data_sheet!U:U,Monthly_Report!H10,data_sheet!BN:BN,"Premier"))
This formula counts values on my data sheet based on the specified criteria. I have this broken ito 3 section, each section that needs to be summed.
Section 1 can not be greater than 165, if it is all the values in the range needs to be reset to 0
Section 2 can not be greater than 190, but at the same time can not be less than 166, else is need to be reset to 0. How would I adjust the above mentioned formula to check these criteria?
1) I would like to sum (or average) the top X numbers from a range.
2) I realize that this can be done with the Large function and an array list, and I have done that, however, I would like more flexibility than that.
3) Ideally, I would like to input a value for x in a cell and then the formula would refer to the value and select that many from the range.
i.e. I have rows 5-20, each with 10 values. I would like to average the top 5 values of each row. I can enter 5 in cell C1 and it will return the average of the top 5 numbers for each row (think grade book with 10 assignments). So each student has an average grade based upon their best performance. Then, without changing any of the formulas, I just enter the number 7 in cell C1 and the averages now use the top 7 values instead of top 5 and I do not have to make any changes to the averaging formula at the end of each row.
I've got a problem where I need to assign values to ranges between 0 and 3.
Eg.
RANGE VALUE
2-3 2.2
1-3 1.6
0-3 0.8
0-2 0.5
0-2 0.5
0-1 0.1
0-1 0.1
0-1 0.1
0-1 0.1
The values above are simply approximates done manually but I need it done automatically using a formula. The resulting values should be proportioned similarly to the ones above but they need to all sum up to 6 and be no more than 3 each.
I have a range of times, and with the whole range I cant have any duplicate values, if they are duplicates I would like to add 1 second until all the values are individual.
View 3 Replies View RelatedI would like to restore the currency format including the $ and three decimal point commas.
=MIN('2Data'!E2:E500)&(" - ")&MAX('2Data'!E2:E500)
Starting in cell J2 and down up to J9 - I need to preset the following values:
3
3
7
7
11
11
15
15
Tried a few times but no success. What am I missing ?
I'm having a problem with a formula I'm using to sum the values within a certain date range. They have to meet certain criteria also, but I have that part of the formula worked out already. Here is the formula I am using, with the part I believe is the problem in red: ......
I searched around and tried replacing the ",--" with asterisks, I tried holding CTRL+SHIFT and pressing ENTER to activate it, and I just can't figure it out. Anybody understand what might be going wrong?
It gets ranges from different worksheets and specific cells and brings them back to one master worksheet. I was wondering how to modify the code so that the ranges come back to the master sheet as paste special as values.
Here is the code so far
Sub PullData()
Dim ws As Worksheet: Set ws = Sheets("Master")
Dim wksht As Worksheet
Dim lMasterLR As Long, lwkshtLR1 As Long, lwkshtLR2 As Long
Dim strAccount As String
On Error GoTo ErrHandler
Application.ScreenUpdating = False
[Code] ......
ErrHandler:
Debug.Print "There has been a critical error."
Application.ScreenUpdating = True
End Sub
I have a value X in cell B5 and a Value Y in cell B6, in multiple worksheets
In my userform, i have 2 comboboxes
one holding value ranges for the x coordinaate (eg 0-10, 11-20,21-30)
the other holding values for the y coordinate (eg 0-10, 11-20,21-30)
i want the user to be able to select a range from the x coordinate and the y coordinate...then i want to search thru all the B5 n B6 cels across the worksheets and return those worksheets that match the users input...
is it possible for this to be done?
Using VBA, I want to import data from a text file into a worksheet, but only import a row of text if the three-letter code matches one I have in a named range. The data looks similar to this:
ALF,D,06/01/2009,0.96,0.97,0.96,0.97,1007,0
ALK,D,06/01/2009,0.34,0.36,0.34,0.345,4375,0
ALL,D,06/01/2009,3.7,3.8,3.68,3.75,12381,0
ALR,D,06/01/2009,1.155,1.18,1.155,1.17,388,0
ALS,D,06/01/2009,3.39,3.39,3.23,3.31,2642,0
ALT,D,06/01/2009,0.031,0.031,0.031,0.031,3460,0
ALY,D,06/01/2009,0.1,0.105,0.1,0.105,460,0
etc...
and the named range similar to this:
AAC
AAF
AAM
AAR
AAX
ABB
ABC
ABP
ABQ
etc....
I know how to open and import the data, it's just making the comparisons that I'm having trouble with at the moment .
I'm trying to build a results sheet for some work that we do.
1 column has the month in which the work was done. ie.
01/01/04
01/01/04
01/02/04
01/02/04
01/04/04
01/09/04
01/09/04
01/09/04
etc... In the example above, as you can see, some months have multiple jobs
done, others only 1. Also there are some months when no work was done at all.
The dates are always the 1st of the month, so no problems in looking at
specific dates, just the month values.
So if I wished to show the last 4 month's when work was done, what function
would I need to use to get Excel to produce a list like this...
01/01/04
01/02/04
01/04/04
01/09/04
In the following range:
Code:
.Range("C13", .Range("C13").End(xlDown))
For column G, I want to put "N/A" into blank cells.
Like this:
Column CColumn GFIC0021FIC0012FIC0063FIC003N/AFIC0341