VBA Max Statement For A Range Where "from" Is A Variable

Jan 25, 2010

I need to calculate the max over a range of figures in a macro.

The "from" is a variable, call it X
The "to" is my counter in a loop so (Cells(-(Counter - 1), 5)

What I'm trying to do is to tell Excel that I want the max from X to the current counter cell.

e.g. Application.Max(Application.Index(X, 1):Cells(-(Counter - 1), 5))

but this give me an error.

View 9 Replies


ADVERTISEMENT

Declaring Variable In Sub Statement?

Nov 23, 2011

I am trying to make sense of quite an elaborate macro built by someone else. This has the following line of code

Code:

Public Sub readOperation(rowNumber As Integer)
Dim rowOff As Integer
rowOff = rowNumber - 1
'other arguments and conditions
End Sub

What I am buggered about is that I cannot see the the variable rowNumber being declared anywhere but it seems to have a value of 1. I should mention that Operation is a class and I am very new to classes.

View 8 Replies View Related

How To Use Variable In R1C1 Statement

May 29, 2013

I need to know how to use a variable in an R1C1 statement. The statement is as follows:

Set rng = Sheet1.Range("A2:QFinalRowII").CurrentRegion

Ideally the statement would read something like,

Set rng = Sheet1.Range("A2:Q10").CurrentRegion

How would I use a variable name like "FinalRowII" instead of a number?

View 8 Replies View Related

Object Name As A Variable In A With Statement

Jan 16, 2007

Does the object in a 'With' have to be an object or can it be a variable containing the name of an object.

I want to carry out the same series of settings on a number of objects
and propose to define a Sub and pass the name of the objects as a parameter.
I then want to use a 'With' to set that object.
i.e. as shown below - though I cant see how it would work.

' combobox objects called Staff, Maker, etc

Call Setuplist("Staff")
Call Setuplist("Maker")
...

Private Sub Setuplist(whatlist As String)
'some complex filtering and sorting goes here
...
Set rnData = ActiveSheet.Range(whatlist+"List")
vaData = rnData.Value
With Me+whatlist ' this is the bit I cant fathom
.
.
End With
End Sub

View 9 Replies View Related

Evaluate A Count Statement With A Variable?

Jul 14, 2013

I am trying to perform a count statement in VBA as below, with the 1st Evaluate I get the correct response, however with the second option it shows a 0. The variable is obtaining the correct values as per the example of 6017Complete, this is proven with the MsgBox

VB:
Dim TillNo As String
TillNo = Target.Offset(, -1) & Target.Offset(, 0) & "Complete"
MsgBox "Till Number is" & TillNo
Target.Offset(, 10) = Evaluate("COUNTIF(C3:C123,""6017Complete"")")
Target.Offset(, 11) = Evaluate("COUNTIF(C3:C123,TillNo)")

View 6 Replies View Related

Insert Variable From Sheet Cell Into SQL Statement In VBA Code

Oct 29, 2013

I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.

Here is my code

cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear

Set rs = New ADODB.Recordset

[Code] ........

This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.

View 1 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View 9 Replies View Related

Set Range Variable Using Variable Row Number

Mar 11, 2008

I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.

Dim Totals As Range
Set Totals = [U37: AE37]

Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.

View 3 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related

Select Range In Worksheet Where Last Cell In Range Is Variable?

Jan 27, 2012

I am trying to write code to select a range in a worksheet where the last cell in the range is variable.

Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range

[Code].....

View 8 Replies View Related

IF Statement Between A Value Range

Jan 13, 2007

This is hard for me to articulate, so hopefully someone will understand what I'm trying to do.

I'm attempting to construct a formula in Excel, that basically says: IF the value is between these two numbers, multiple them by this percentage, or if the value is between these numbers, multiple it by this percentage.

So, let's say cell A3 equals 10,000,000, A2 equals 5,000,000 and A1 equals 1,000,000... Also cell B3 equals 10% and B2 equals 5%. We'll say that number X is in cell C1. If the number X is between 10,000,000 & 5,000,000 it should by multiplied by 10%. If number X is between 5,000,000 and 1,000,000 it should be multiplied by 5%.

I've tried a formula similar to: IF(C1 (Rng(A3:A2), C1*B3) ---- Meaning that if the number is between 10,000,000 and 5,000,000, multiple the number by 10%.

View 12 Replies View Related

Assign Named Range To A Vba Range Variable?

Jan 25, 2013

I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")

I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.

the first two lines of code work fine, the msgbox shows "E100:E105", happy days!

However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.

VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup

MsgBox Range(thisString).Address

Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)

View 1 Replies View Related

Copy A Range And Paste It Over A Variable Range

Jul 16, 2009

I am using the code below to copy a range and paste it over a variable range.

View 4 Replies View Related

Set Range Variable To Growing Dynamic Range

Mar 6, 2008

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

View 3 Replies View Related

Using MID On Range In For Each Loop With IF Statement?

Aug 8, 2013

I am trying to loop through a range, checking for a cell to contain one of two characters and then put a value in a cell to the side (well 3 away) of it. Im getting an error for Type mismatch on line:

VB:
If CellContent = "h" Or "v" Then

the data in the cell is an along the lines of '12.34.56.43 som-thi-vh-ng1'

VB:
Dim MyRange, MyCell As Range
Dim CellContent As String
Sheets("Sheet1").Activate

[Code]....

View 1 Replies View Related

Using A Cell Range In An If Statement

Jul 23, 2009

I am trying to do a calculation based on the conditions of two cells but one cell I would need the range of the report. Either way, here is my current statement.

=IF(P2:P15 = "Green Building 15",SUM(COUNTIF(C2:C15,"Over AC")+COUNTIF(C2:C15,"Top Lab AC")),0)

I get a Value# error (though it systematicaly works if you check in the funtion area), and its because of the range I am using, is there anyway to bypass thiss issue or can someone give a better calculation.

View 4 Replies View Related

If Statement For A Range Of Numbers

Aug 28, 2009

I have tried over and over to get a point value from the percentages posted below but my formula will not return consistent results. Am I doing something wrong.

94% - below 1 Point
95%-96% 2 Points
97% 3 points
98%-99% 4 Points
0% or 100% 5 Points

Reference Cell is AG2 and is .99 but for some reason I return a 5
=IF(AG2=0,5,IF(AND(AG2>0,AG2<=0.94),1,IF(AND(AG2>=0.95,AG2<=0.96),2,IF(AG2=0.97,3,IF(AND(AG2>=0.98,A G2<=0.99),4,5)))))

View 6 Replies View Related

Nested If Statement Within Range

Apr 7, 2009

I dont know how to nest and if (if that is the right way to go about it). this is what I have ....

View 10 Replies View Related

If Statement With Range Of Numbers

May 2, 2012

Need all numbers in between the ranges set. So if I input for example 8765 then the cell still comes up with 18.

IF Cell E25 is 0-7200, then 12, If cell E25 is 7201-14400,
then 18, if cell E25 is 14401 - 21600, then 22,
if cell E25 is 21601-28800, then 28, if cell E25 is 28801 - 36000, then 34.

View 4 Replies View Related

If Statement Continuing Through Range?

Jun 21, 2012

so on sheet 1 i have a list in column b (as below) and in column G I want to put a note "not included" if the cell matches what is in sheet 2:

AAA
BBB
CCC
DDD
EEE

On sheet 2, column A I have a list of items that I want to not include which I want to be able to add/remove/adjust over time. For example:

DDD
EEE
FFF
LLL

I can get it to put in the correct text into sheet 1 with the following but I don't know how to get it to look through all values on sheet 2 and then only enter the "not included" on sheet 1 if it is listed on sheet 2.

So far I have

Sub checksheet()
For Row = 1 To 428
For col = 2 To 256

[Code]....

This only does it for what is written in cell A3 on sheet 2 and does not keep going down the list in sheet 2.

I think I need to define the range better on sheet 2 and ask it to look through that, but im not too sure.

View 4 Replies View Related

If Statement With A Range Selection

Aug 9, 2007

I was making a simple spreadsheet as I'm taking practice tests for some IT certifications and the spreadsheet I was making was to chart out my progress.

I had a range of scores in some cells and one of the formulas I used so that I didnt have errors in the sheet like div/0 went like the following:

=if(b5:b50="","",sum(b5:b50)) this seemed to work really well for the one section, however I tried to get a little fancy as always and I think I messed something up. Beneath that I tried to separate each practice test with the same sort of formula resulting something like the following:

=if(b30:b40="","",sum(b30:b40)) this however just returned an error #value. I dont understand why the first formula worked and the second didnt. Then again, I'm not really an excel guru, back in college I was really good with it but I dont really remember much.

View 9 Replies View Related

If Statement With Selected Range

Aug 10, 2007

I'm trying to create an if statement that have 2 criterias. Here's what I've created:

=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)

This statement is returning the #VALUE error.

I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.

View 9 Replies View Related

Date Range In An IF Statement

May 19, 2009

I'm trying to create an IF formula that returns the month as text between a certain date range in a different column.

For example

Between the 25-APR-09 and 23-MAY-09 I want to display "May" 24-MAY-09 and 27-JUNE-09 "June"

And so on so forth, the column with the dates in it is in date format.

View 9 Replies View Related

If Statement, All In Range Are Not Null

Mar 21, 2007

I need a formula that will return "true" only if ALL cells in the specified range are not Null (<>""). I'd like to assign a value (% complete) only after all tasks under a phase have been given a completion date.

View 7 Replies View Related

Use If / Then Statement - Deleting Range Of Cells

Jun 11, 2014

I have a sheet with thousands of rows and 45 columns and I need to delete a range of cells (G:Y) if the cell in column V = the cell in column X, and I need to do this by row. I think an if/then statement would work.

View 4 Replies View Related

IF Statement On Multiple Cells Not In Range

Jan 8, 2014

I'm after a formula that will look at a number of cells(that are not in a range) with the word "C" and if they all = "C" then I want the cell to come back with the result "yes" otherwise "no".

View 12 Replies View Related

If Statement Required For A Cell Range

Dec 4, 2009

If C36, C37, C38 or C39 contain a 0 then put 0 if not continue with the the formulae
I have this but I know its not right as this is a sum: =IF(C36:C39=0,0,ROUNDUP((C36/C37)+(C38/C39),0)).

View 4 Replies View Related

IF Statement To Pull A Date Range?

Mar 24, 2005

I've created an IF statement with multiple formulas, but I can't seem to
figure out if it is possible to pull the information by a specific date
range. I have sales reps that visit customers on specific dates and I need
my tally sheet to only pull dates within that current week. Does anyone know
if this is possible??

This is what I have so far:
=SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales
rep!P2:P216=I7,IF(sales rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))

If you look above I have it to calculate the date value starting at
03-18-05, but I would like it to only pull that date through the next week.

View 9 Replies View Related

Value Test Of A Range Of Cell And If Statement?

Feb 17, 2012

I am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .

Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long

[Code].....

View 9 Replies View Related







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