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.
I need it to cycle through whole numbers for three variables whose range I can define. I need this macro to test the result of all possible combinations of A, B and C, as is shown in the attached file. The initial range boundaries for each variable are as follows:
VariableABC
Minimum1408
Maximum510012
So the macro should start with the following combination (1,40,8) then (2,40,8) then (3,40,8) etc until it reaches (5,100,12). I need the results for each combination to be posted at a separate sheet next to the corresponding combination.
I am trying to define my variables with a for loop and if I run the code to the line after the first variable is defined, it shows that the variable is equal to the appropriate value, but after the for loop is done all of the variables are empty.
[Code] ......
i,d like to define variables,
a=2,7
i<j<k,m<n
5*a+10=i+j+k+m+n
i,j,k is defined in the process (i,j,k are three elements of a,a+1,a+2,a+3,a+4)
how do i define m,n.(two elements of a,a+1,a+2,a+3,a+4)
I have 3 variables right now...
Dim celadr As Range
Dim cLetter As String
Dim avgRow As Integer
and one line of code reads:
celadr = Range(ColumnLetter(LastScoreColumn) & avgRow)
When I mouseover columnletter it shows "H" and when i mouseover avgRow it shows "2"... this info is right but I think the problem may be trying to combine a string and an integer value as a range. How can I do this so that celadr is the range H2?
Code to loop through worksheets 1 to 3 only and perform a certain task, and how to define the variables i.e. worksheets 1 to 3 when I am not using actual sheet numbers, but tab names. (worksheet 1 to 3 is just the positionof each tab).
What i need the code to do is perform a task on the first sheet, loop to the next sheet, perform the same task, until it has done the 3rd sheet.
I have a drop down list in a column called Report Type (example below).
Report Type - Drop-Down Menu in Column F
Business/Operational/Work Plan
Budget Report
Performance Report
Program Quarterly Report
Program Mid-Year Report
Program Annual/Year-End/Final Report
Service Quarterly Report
Service Mid-Year Report
Service Annual/Year-End/Final Report
Financial Quarterly Report
Financial Mid-Year Report
Financial Annual/Year-End Report
Auditied Financial Statements
In-Year Reallocation
Annual Reconciliation Report
SRI Report
Other Report
I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report
Is this possible with a countif formula?
I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?
View 2 Replies View RelatedI have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.
The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.
why this won't work.
Code:
Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange
how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:
Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
Next
I'd like to define the end of the range with more accuracy than I'm currently doing.
I'm trying to define a range to be copied during execution of a macro. The range to be copied must include all contiguous data from a defined starting range to the rightmost column and the bottommost row containing data (boht of which can change depending on input data). I use <End>+<RightArrow> and <End>+DownArrow> to define the range when working in Excel (not under macro control).
I used the macro recorder to define the code for the process described above, with results shown below:
Sub Macro2()
Sheets("Source_Info").Select
Range("A2:D6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working_Data").Select
Range("A2").Select
ActiveSheet.Paste
End Sub
When I run the macro, however, the End(xlToRight) and End(xlDown) commands do not have any effect (i.e., the rang is not made any bigger when these statements are executed).
I've used the same code in previous macros (with previous versions of Excel and Windows), and it worked fine. I'm running Excel 2003 under Windows XP Professional now.
is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown
I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Sub CalculateATR()
Sheets("Data").Range("a1400").Select
Do
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Selection.Copy
Range("G5:K31").Select
ActiveSheet.Paste
Else
End If
End Sub
how to set up an array within a worksheet
View 3 Replies View Relatedhow do you go about referencing a cell by using a value of an integer? For example, I want to select cell A5. I have the number i= 5 (in my vba code) and I want to use that together with the row letter A.
View 4 Replies View Relatedis there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.
eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".
I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Z100 =vlookup(D4,NAMED RANGE,2,false)
the Named range would be a value in the same row ie B4 - being Male or Female
so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Z100 =vlookup(D4,B4.Value,2,false)
otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).
Many more Excel Formulas Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) )
I need to be able to work out a percentage based on a value range. ie.
Cell D2 to D5 will define the percentages (so I can adjust it and play)
D2 = 18%
D3 = 15%
D4 = 13%
D5 = 10%
I need to work out:
If the value in cell A10 is between 11 and 200 calculate on D2, if between 201 - 500 calculate on D3, if between 501 - 2000 calculate on D4 ect ect
Cells A10 to A100 will have some figures put in them.I then want to calculate the %value used based on weather the figure in one of the A10 to A100 cells falls in a range. The data being put into the cells in column A will vary from report to report.
I'm trying to automate the calculations :
I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.
[Code] ........
Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'
'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.
How can I define a range, only for the active cell?
View 6 Replies View RelatedI have a daily collection of data based on half hour meter readings. Responsibility for this data is to be split between core and non-core hours so for example core hours would be from 6:00 to 18:30.
What i would like to do is have a combobox for both the start time and end time allowing the user to change these as appropriate.
Where i'm struggling is using these comboboxes to select the data between these two times and total the values per row.
My data is currently arranged with the times (staring at 00:00) running horizontally on row 14.
The way I have try to define lastcell is wrong. But I don't know how to do it.
Code:
Sub createUniqueRecordID_2()
'
' fill using CTRL + ENTER
'
Dim lastcell
Sheets("projects list").Select
[a1].Select
[Code]...
I found some code online that works great for defining a named range. My only issue is that occasionally my worksheet name will contain spaces. this throw off the current code. i do know that if i manually go in to the range and add a "'" to the beginning and to the end of the worksheet name it fixes it. unfortunatly i cannot figure out how to add the "'" in the following VBA.
Code:
Sub AddDynamicRangeVertical()
On Error Resume Next
Dim sRangeName As String
Dim n As Name
If ActiveWorkbook Is Nothing Then Exit Sub
[Code] ..........
I'm trying to define a dynamic column range call 'Cost'
If it starts in Column C, row 4 [C4]. I want the range that to go down to the Row I have defined as 'subtotal'
The user will be able to insert new rows above 'subtotal'
How can I define Column C4 so that any new row added will be including in the defined row range 'Cost'?
I got this code I have been trying to solve but it does not work...
What I need to do is define a worksheet and a range of that worksheet.
I can only seam to work out either a worksheet or a range but not the two together.
Private Sub cmd_Cform_Click()
Dim rng As Range
Set rng = Range("A1")
With Sheets("Customer")
rng = .Range("A1")
Application.CommandBars.FindControl(ID:=860).Execute
End With
End Sub
I am trying to define a number of ranges in different sheets using VBA I am using the follwoing syntax but I am getting a 1004 error:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
ThisWorkbook.names.Add Name:="Test", RefersTo:=Worksheets("Sheet1").Range("E6", Range("E6").End(xlDown))
End Sub
As long as I have sheet1 active, it works. If I have another sheet active and change smt, i get the error.
The code is located in the ThisWorkbook object.
How can I take the source data of a chart to be all those cells in the column, that begin with the number "1"?
View 2 Replies View RelatedHow can I redefine name of the cell? This is what I have tried and not work
intWDColumn = Range("rngWaterDepthStart").Column + 3
intWDColumn = intWDColumn + 1
strNameRange = "rawData!R7C3:R7C" & intWDColumn
ActiveWorkbook.Names.Add Name:="rngWaterDepthStart", RefersToR1C1:=strNameRange
and the cell name have define to ="rawData!R7C3:R7C7" which the "" stop the rename working I belive.? What I want is to add or reduce extra column into the name depends on macro.
I want to define the range from a given cell and all data below within the same column strDataStart is the named cell in the worksheet that want to start from. Below is what I tried and failed.
Function rngDataCol(strDataStart As String) As Range
Dim rngDataEnd As Range
rngDataEnd = Range("strDataStart").End(xlDown)
rngDataCol = Range(Range("strDataStart").Address, rngDataEnd.Address)
End Function
I am trying to create a correlation matrix that uses the =ADDRESS function to define the various columns of data which I want to correlate against each other (I require this flexibility because the number of columns and rows of data to be correlated will change). The basic formula I am using is something like the following: =CORREL((ADDRESS(Sheet1!$C$2+3,L2+2,,,"Sheet1")),(ADDRESS(Sheet1!$B$2,L2+2,,,"Sheet1")))
However, while the individual ADDRESS fomulas deliver the correct start and end cell references required, it seems that CORREL function won't accept the cell addresses that result from using the =ADDRESS function.
