Sum Of A Range Based On Two Variables

Jun 18, 2009

I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates.

Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.

View 2 Replies


ADVERTISEMENT

Autofill Range Using VBA Based On 2 Variables

Jan 14, 2014

I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:

Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page

[Code]....

The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.

View 2 Replies View Related

VBA Select Range To Copy Based On Two Variables

Jan 13, 2014

I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.

For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.

What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between

Code:
Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long

[code]....

View 3 Replies View Related

Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers

Jul 19, 2009

I am trying to select a range based on two variables which store the column numbers. what I have is:

View 4 Replies View Related

Use Variables In A Range?

Aug 23, 2009

I used a macro to get the following code, but would like to do this with VBA code where I use variables and numbers instead of the
macro's ("I568:J568") notation. Thus I would have something like (lRow, 9) : (lRow, 10) or whatever the correct notation is. Basically I'm trying to copy and paste formulas from one row to the next.

View 4 Replies View Related

Using Variables To Set Sum Range

Dec 30, 2013

I am trying to sum the range of column B (Being the found match in column A offset 1) to the relevant column set by the variable intcolumn (integer value of the column matching the criteria (in this case column 25)

Code:
rngMember.Offset(0, 13) = Application.Sum(rngTradeMember.Offset(0, 1) & ":" & rngTradeMember.Offset(0, intColumn))

The sum does not fail but returns an incorrect figure.

What I am hoping to see is a sum of the following

result sheet offset (0,13) = sum of (column B to the column Y)

View 7 Replies View Related

Return Value Based On Two Variables?

Apr 19, 2014

how to return a value based on two variables. I was trying with hlookup and offset but excel kept ignoring my thoughts.

Here is a sample of how the data looks like and what I want to do:

excel.jpg

Depending on the ID which is entered in G2, cell H9 has to get the value from D4.

I know that ID is gonna have to be extracted with find function from column A. So every time an ID is entered (in cell G2) it would need to look for it in column A and then return the value VACLEG from column D.

View 5 Replies View Related

Sum 1 Column, Based On 2 Variables

Dec 9, 2009

A B C D
COL 0
COL 0 1
COL 0
COL 0 1
COL 0
EDM 0

I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.

View 9 Replies View Related

Sum Column Based On Two Variables

Jun 28, 2007

I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.

View 8 Replies View Related

Using Variables In Range References

Jun 5, 2009

I am doing some work in which I need a very flexible tool. I will be doing regression analysis on thousands of funds at a time. I need to define the range for each fund by a count function that counts the number of monthly returns that are posted. The code I have so far is below (I underlined where the references use variables):

View 3 Replies View Related

Range Using Cells Variables

Aug 3, 2012

I'm trying to use a range(Cells1),(Cells2) all working fine, but now I'm trying to use variables for the Row selection. selection.row as one of the variables, but keep on getting a error.

Code:
Sub Clear_Time()
Dim Rng As Range
Dim pointer As Integer
pointer = Worksheets("Summary").Range("Z1").Value
Worksheets("SubPanel").Range((Cells(10, pointer)), (Cells(52, pointer))).Find(What:="").Activate

[code].....

View 3 Replies View Related

Selecting A Range Using Variables

Jun 23, 2008

How would I select a range using variables? For example, say I wanted to say

myRange = Sheets(1).Range("A1:A100"),

but the column was variable. How would I do this if the column number was stored in a variable?

View 9 Replies View Related

Determine If Two Range Variables Are Same

Jun 20, 2007

How to decide the two range object variant represent the same range? Plz check the following code, How to decide Rng4 and Rng5 is or not the same range?

Sub IsTheSameRange()
With ActiveSheet
LastRow = .Cells(65536, "B").End(xlUp).Row
Set Rng1 = .Range(.Cells(3, "K"), .Cells(LastRow, "K"))
Set Rng2 = .Range(.Cells(3, "AE"), .Cells(LastRow, "AE"))
Set Rng3 = .Range(.Cells(3, "BQ"), .Cells(LastRow, "BQ"))
Set Rng = Application.Union(Rng1, Rng2, Rng3)
Set Rng4 = Rng.SpecialCells(xlCellTypeFormulas, 23)
Set WhlRng = .Range("A3:DR" & LastRow)
Set Rng5 = WhlRng.SpecialCells(xlCellTypeFormulas, 23)
End With
End Sub

View 2 Replies View Related

Variables To Make Up Range

Jul 25, 2007

I'm trying to edit the predefined range in my macro that changes formulas to values.
My current macro looks like this:

Dim vCol As Variant
vCol = Application.InputBox("Select Column", Type:=2)
If vCol = False Or vCol = "" Then Exit Sub
Set UserRange = Range(vCol & "9:" & vCol & "35")
UserRange.Value = UserRange.Value
End Sub

I tried extending the range to include 48:53, but it doesn't work. I tried with this: Set UserRange = Range(vCol & "9:" & vCol & "35" & vCol & "48:" & vCol & "53")

View 6 Replies View Related

Use Variables To Delete Range

May 23, 2008

I am trying to select rows using constants I have set-up so that I can then delete those rows. However I don't know how to set-up the row formula to accept the constants. So far I have something like

Dim a As Integer
Dim b As Integer
row(a:b).select
selection.delete

View 3 Replies View Related

Maximum Value Based On Multiple Variables

Jul 28, 2009

Maximum value based on multiple variables. Basically what I have is something like this:

View 3 Replies View Related

Eliminate Duplicates Based On Different Variables?

Nov 28, 2012

I have a list of serial numbers. There are many groups of 8 same serial numbers, where group consists of 2 projects with 4 SN per project (because of 4 different events). I want to create a formula that marks one project with 4 out of 8 SN for deletion based on set of 3 dates assigned to them. In short I need to count 4 rows per project as one unit.

Serial
Project
Event description
Date1
Date2
Date3

Here is a list on what information matters when making decision if to mark project for deletion or not.

1. Project1 has no Dates entered compared to Project2. Mark Pr1

2. Project1 and Project2 has no Dates entered. Mark Pr1 (random, does not matter which should be removed)

3. Project1 has 2009 Dates, Project2 has 2011 Dates. Mark Pr1 because dates are older

4. Project1 has less Date entries filled than Project2 (same year). Mark Pr1 because less Date fields entered.

I can somewhat do it for separate rows, how I can make these rules apply for whole project as one unit related only to one SN at a time. Biggest problem is there is no pattern of dates entered. Sometimes one row can be filled another missing out info and etc.

View 1 Replies View Related

Sumif Based On Multiple Variables

Jun 21, 2006

I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A.

Here is the sheet that the information will be drawn from:

Part#QTYREP
4011A
4023A
4031B
401 1 A

Here is the sheet that the information will be output:

Rep
Part#AB
4012
4023
4031

View 5 Replies View Related

Defining Average Range With Variables?

Apr 5, 2012

It's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.

I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.

Code:
Option Base 1
Dim c(6)
Sub calc_averages()

[Code]....

View 2 Replies View Related

Find First Value Greater Than X In Given Range Using Variables

Mar 3, 2014

My data has a bunch of near 0 figures followed by values I actually need followed by more irrelevant 0's than once again followed by values I need and again irrelevant 0's. Looks like

0.1
0.12
3.2
3.4
0.1
0.3
4.5
4.2
0.3
0.11

but with many more rows and numbers. I'm graphing the start and end values before/after zero's.

The first block of numbers I figured out using

=MATCH(TRUE,INDEX($C8:$C150>1,0),0)+7 I then index from that given row.

The span of relevant values I'm looking for constantly changes, so once my numbers deviate too much I stop, using another index formula.

Now for finding my beginning and end points for the next block of data I've combined using VBA with excel formulas. I can't quite figure this part out. I've created Variables for ranges and then used them to find max and min Values.

Public Sub RunCurrent()
'
' RunCurrent Macro
Dim upEnd As Integer
Dim dnStart As Integer
Dim dnEnd As Integer

[Code] ........

View 2 Replies View Related

Define Range Variables And Use In Formula VBA?

May 12, 2014

I can't quite get this formula to give me the correct value.

imgur: the simple image sharer

I tried defining as a Range and I can't get it to work right either way. When I hover over my r1 and r2 values the range is correct but it does have "$A$227:$A$447" quotes around the range so I think that is messing me up.

View 4 Replies View Related

Range Variables In Customized Function

Aug 30, 2006

I wrote my function as it is shown below:

Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
Next
Else
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
Next
End If
End Function

This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})

However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............

View 3 Replies View Related

Loop Range Using Variables For Rows

Dec 16, 2006

I need to write a FOR... Next loop that will loop through a range of rows, select the same cell range on each row, perform a task, then advance to the next row. I can't seem to figure out how to substitute the loop counter for the row index. For example:

For counter = 1 To 100

Range("B(counter)","F(counter)").Select
ActiveCell.FormulaR1C1 = "1"

Next counter

View 3 Replies View Related

FillDown Range Defined By Variables

Sep 9, 2007

I am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown.
I think my idea is good, but either syntax is off or not going to be allowed to do this.

.Range("F" & lLrwT + 1: "F").FillDown

>>I have posted this on VBAx too.<<

View 9 Replies View Related

Select Range Using Offset With Variables

Apr 23, 2008

I am trying to select a range in VBA, where I know the name of the starting cell, and the size of the range are values returned earlier in the VBA function. I guess the pseudocode I am trying to do is: range(range("StartCell"):range("StartCell").Offset(x,y)).Select. Obviously this code doesn't work, but I don't know the correct way to select this range

View 2 Replies View Related

Copy Range Using Row & Sheet Name Variables

Jun 12, 2008

I'm trying to copy multiple cells on the same row, with the row number to be copied stored in a variable (x). Right now i've been trying to use the Range method, but this copies all of the values between "A" & (x) and "H" & (x). I only want the values of the cells in "A", "F" and "H".

(copy_sht) is the name of the sheet to be copied from
(paste_sht) is the name of the sheet to paste into
(copy_row) is the name of the row to be copied from
(paste_row) is the name of the row to be copied to
(x) and (y) - I'm not sure f these are necessary, I've followed a lot of examples to get here, so i'm a bit confused.

Function copyrow(row As Long, copy_row As Long, paste_sht As String, copy_sht As String) As Boolean
'Is passed the names of the sheets, the rows, and copies the data
Dim x As Long
Dim y As Long
x = copy_row
y = row
Sheets(copy_sht).Select
Sheets(copy_sht).Range("A" & x, "F" & x, "H" & x).Copy
Sheets(paste_sht).Select
Sheets(paste_sht).row(y).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Function

To recap, this code will copy all the cells between A(x) and H(x), but i only want the values of A(x), F(x), and H(x).

View 3 Replies View Related

Calculating Number Based On Multiple Variables?

Jan 24, 2014

What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected

My excel data is set up like this

(A).....................(b)......................(c)..................(d)........................(e).........................(F)
.......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg
1. City
2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50

3. Winnipeg....................................$3.20..................$3.75..................$3.6..................$3.21

4. Saskatoon..................................$5.4..................$5.2..................$4.90....................$4.70

(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)

I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:

..................A............................ B..............................C.
..................Weight..................City..................Charge per 100kg
24...............800..................Toronto..................$5.50

View 5 Replies View Related

Managing Categories Based On 2 Variables And A Chart.

Aug 18, 2009

I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?

View 6 Replies View Related

Asigning Variables Based On Cell Content

Sep 30, 2007

I just ordered vba for dummies, in the meantime I will have to get my info from the experts on this board. I want to use a name I have in a cell in a msgbox routine.

Currently I have

View 10 Replies View Related

Creating Variables Based On Cell Location

Oct 10, 2009

if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!

View 3 Replies View Related







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