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?
This is a floowup to the issue that was originally posted as "Returning MAX/MIN values from multiple rows in a named range ". I marked that post as solved since I have worked through part of the issue and since have a different one.
View 2 Replies View RelatedI 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 have a spread sheet which is organised in date order column A and produces three signals Buy, Sell, No trade in column S. If i get a signal to buy or sell the price is taken in column T so i might have Buy - 1.9977. I wish to calculate the diff between the value taken to buy in this eg and deduct this value from the opp signal at some point ie a sell signal. Other buy signals and notrade signals should be ignored. The opposite is true for a sell signal
Eg
A,s,t,u
13/4/07, buy,19955,35
12/4/07,no signal
11/4/07,no signal
10/4/07,no signal
9/4/07, no signal
8/4/07, no signal
7/4/07, sell, 19990,13
6/4/07,no signal
5/4/07,no signal
4/4/07,buy,19977
if a buy signal is generated then in this eg 19990-19977 gives the diff if selling then 19990-19955 gives the diff .
I have a file where I have few sheets (link to file below). Sheet zrzut is dropdown from other system - updated daily. Sheet Productivity is filled with data supporting to group data and add conditions (for example column "I" in "zrzut" sheet). Sheet "Vena" has data from "zrzut" based on condition in column "I". Sheet "VENA_PLAN" has the same data as "Vena" but only values - I need only values for other actions. The thing is my current code replaces old data with new, but what I would like to achieve is to have new data stored below old. For now code looks like
Code:
Sub PlanVENA()
Application.ScreenUpdating = False
Worksheets("VENA_PLAN").Range("A3:H300").Value =
[Code]....
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 _...................
Based on filtered data I want to produce a result for the Average trade gain.
Lets say I have 100 rows of data, and based on filtered settings the rows reduce to 5 rows of results.
Of those results I want to find an average of all the positive values (trading profits) and ignore the negative ones.
$2000
$1500
-$700
$4000
-$1000
The answer should be (2000+1500+4000)/3
The need for filtering makes this problematic. I am inclined to use SUBTOTAL as it applies to filters rows only, but I can't see how to use an IF statement within it.
In addition to this I would also love to know how to subtotal all positive values without averaging.
I'm trying to populate the Hourly Rate from 4 different Vendors based on 2 conditions.
What is the AREA and what is the Category.
The Area is a pick list (10 unique values) and the Category is a pick list (50 unique values)
If A2 is picked from AREA and B5 is picked from Category THEN populate the value of C5,D5,E5,F5 into the cells of J2, K2,L2,and M2 ...
I have an excel calculator that I use daily (mostly written in VBA). I change some of the values to get a new result. Is it possible to post the values of the results in a column so that I can see what the old values are? For security reasons I cannot disclose the calculator but I guess I can post an example of how I would like it to work.
View 3 Replies View RelatedI need cell d4 to display the number in column b next to the applicable insurance company when the insurance company is selected from the drop down menu in cell c4. How do I do this?
View 4 Replies View RelatedI have a simple one today but on a time crunch so I don't have time to go through the book.
I am trying to hlookup a list of values from a table and average the results. The values are setup horizontally.
I have the following code that copies only the visible cells in an auto filter...how could I modify this code to paste only the values and not the format?
.Range("a:a").AutoFilter Field:=1, Criteria1:="True"
.Range("b:d,g:r").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Monthly Hours").Range("A1")
Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.
View 3 Replies View RelatedI want to locate the corresponding acct number between worksheets “primary_data” and “qty_movement” and once a match is found (from acct worksheet) then copy over the acct numbers and the share data to the results page. If there is no match I don’t want anything copied to the results page, just ignore that data on either worksheet “primary_data” or “qty_movement”.
•Both ranges on “primary_data” and “qty_movement” worksheet are variable, as accts are left off or kept on depending on the daily activity so the ranges are never set.
•I need to concatenate on “primary_data” worksheet cells A&B&C to get acct number
•concatenate on “qty_movment” worksheet cells B&C (need to keep zeros in front of actual numbers for acct number reasons)
•these accts need to be cross referenced as the accounts on the “acct” worksheet (pre-populated with corresponding acct numbers that never change and will always be on that worksheet)
•If a match is found on the “acct” sheet then I would like it to either replace the acct so they match on both worksheets or just to recognize those accounts correspond with each other and do the below.
•then take the 2 concatenated acct numbers off of “primary_data” and “qty_movement” in the I cells and copy them over to the “results” worksheet as well as the share data from “primary_data (column E)” and “qty_movement(Column D)” and then compare the 2 share amounts on “results(column E)”
i need to elaborate on anything, the attached sample sheet shows what i want to do, but without any formulas or code.
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 am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old
- yellow if the value of a cell on a seperate sheet is 'In progress'
- green if the value of a cell on a seperate sheet (date) is less than 18 months old
I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.
Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.
i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results
LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design owb").Cells(i, 10)
With Sheets("design")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True
The 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
View 4 Replies View RelatedI 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'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
I have two workbooks: master and student. I have one spreadsheet in each workbook. Both workbooks contains data in the same range.
master.xls data = B1:B10 (values are integers)
student.xls data = B1:B10 (values are integers)
I want to know how to update(add) the values in master.xls with the values in a closed workbook(student.xls). Basically if master("B3") = 5 and student("B3") = 1, it will update the value in master("B3") = 6.