Assign Value Given Another Value In Range
Jan 21, 2013
I am doing an assignment about credit rating and I am trying to make a spread sheet based on Moody's credit rating method.
I have more or less finished my model, but I have one final problem; in the end I will come up with a numeric score, which can be translated into the final rating (Aaa,Aa1 etc.) based on some ranges. I just can not figure out how to formulate the formula, especially because I have no experience working with ranges.
I have tried with some IF formula's, but they seem to be way too long and complex, for what I imagine should be a relatively simple task.
In the attached sheet I have a final value of 6.9, which should give an A3 rating given that it is within the 6.5 - 7.5 range.
Test sheet.xlsx‎
View 2 Replies
ADVERTISEMENT
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
Oct 24, 2008
Is it possible to assign a name to a range of cells, so that when you insert a formula into another cell you can call that range of cells into the formula?
View 5 Replies
View Related
Aug 19, 2009
I'm doing somthing basically wrong here but i'm not sure what, everything seems to work as planned except in each case where a value is assigned the value isn't actually assigned. any ideas?
The basic objective is to convert a list of numbers (1 to 3 digits) to the format "000-" i.e. all three digits with a hyphen at the end.
View 9 Replies
View Related
Jul 11, 2014
Why the variable DataRange won't set as below. I get Run-time error 9: subscript out of range.
Code:
Sub CreatePivotTable()
Dim DataHeaderRow As Integer
Dim DataRowCount As Integer
[Code].....
View 7 Replies
View Related
Dec 24, 2007
I am trying to assign a range of cells to a variable but it isn't working. My code is as follows:
Dim VarRateTable As Range
Private Sub CalcBudgets(rng As String)
' Dim VarRateTable As Range
Dim CurrentRng As Range
Set CurrentRng = Selection
Set VarRateTable = Range(rng)
Private Sub NEWCALC(datarow)
Dim vt As Double
vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0)
but the Set statement gives me an error: <method 'range'of object '_global' failed>
View 2 Replies
View Related
Oct 11, 2012
I have the following VBA code which created a unique list of values in a data column. After list is created, I want to assign it to a named range ReferTo parameter. Currently I am getting compiler error. How can I assign this unique list to a named range?
VB:
Dim UList As New Collection
Dim rCell As Range
....
If TypeName(Selection) = "Range" Then
[Code].....
View 4 Replies
View Related
Jan 25, 2009
When I insert a line to select the range on the Worksheet it does select the range named as temp. However, the array does fill but only with the first part of the range eg in this case A7:C44.
My aim is to create an array of size point1A( all the rows in the multiple ranges, 3 columns).
Example of a Range to be assigned to the Array point1A. temp = "A7:C44,D7:F44,G7:I44,K7:M44,N7:P44,Q7:S44,"
View 6 Replies
View Related
Jun 22, 2006
How do you assign a loop counter value to a range row value? Scenario: Add cells from a column that aren’t successive. SUM C10 + C17 + C24 etc
Private Sub CommandButton1_Click()
Dim total As Integer
Dim counter As Integer
For counter = 10 To 700 Step 7
total = total + Range("Ccounter")
Next counter
End Sub
View 2 Replies
View Related
Dec 21, 2006
What I need is a macro that assigns manually selected cells/ranges (which may or may not be continious) to a Variables.
View 2 Replies
View Related
Dec 3, 2008
I have a simple three column range. I Autofilter the range based on one of the values in Column 1. I then want to grab the results into a range object.
I've been trying to use the Specialcells(xlcelltypevisible) route to no avail. It only gets one row when I should have many.
View 14 Replies
View Related
Dec 17, 2008
Does the line of code assign the value of cell A5 to the range or is it just a startinig point?
View 9 Replies
View Related
Jan 24, 2009
In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).
Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)
Range U28:U46 will assume the name of sheet2A11 & sheet2A4.
Range W28:W46 will assume the name of sheet2A11 & sheet2A5
Right now I am calling the code when something is entered into A11.
I have tried if statement and select case, but I ran into complications with both.
I have posted both codes with the questions I have concerning those codes.
View 6 Replies
View Related
Nov 22, 2011
How to assign a range in excel using a defined (different) cell.
I've this formula
=INDEX($A$1:$A$17,RANK(B2,$B$1:$B$17))
I want to to choose the range from A1:A17 ... i'll use the formula too many times with different ranges for example A1:A21 and i want to know a way to insteed to change it manually each time to make the formula consider the value i insert in a different cell, so i can only change the value in this cell.
for example:
=INDEX($A$1:$A$D1,RANK(B2,$B$1:$B$D1)) so it can consider the value in cell D1
View 7 Replies
View Related
May 23, 2009
For example, I have the numerical value "400" in R1C2 and that value would be used in the name "D400" which is to be assigned to R1C1:R5C2.
The code I have is:
Range("R" & DRow & "C1:R" & DRow + 4 & "C2") = "D" & Cells(DRow, 2).Value
but is throwing up the error:
"Run-time error '1004':
Method 'Range' of object '_Global' failed
DRow increases dependant on other variable data.
Also, am I right to be using R1C1 instead of A1, or can the same be achieved using A1 references? If so, I think I may not be able to see the wood for the trees.
View 9 Replies
View Related
Dec 17, 2007
I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4.
=SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4)
This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0.
Example:
J2...K2...L2...M2...N2...O2...P2...Q2...R2...
A....B.....B.....C.....B.....D....B.....C.....E
the output of the above sequence should be
1+1+1+2+4+4+0+4+4+0 = 21
View 4 Replies
View Related
Jun 17, 2008
What I need to is create a VBA function with several parameters that reads data from an M x N range of rows and columns (matrix). I cannot just pick and choose certain cells within this range as the function goes into a loop through at least 200 cells in the 35 x 200 range. Obviously, I am here posting as I cannot get this function to work. I believe my troubles lie with reading this range into the function...
Application.Goto Reference:="range"
rangearray = Selection.Value
values(I, J) = rangearray(I + 1, J)
Do I need to set a function variable equal to that range, or do I do this another way? Basically, I need to have the ability to pick a given cell(m, n) out of the range and have my way with it in the function.
View 3 Replies
View Related
May 15, 2008
I have had some help with this (thanks!) but am stuck.
Can someone add to the code below and make it so that multiple tasks, with multiple assignees, can be created from a range of cells, such as:
Column A Column B
Row 1Task SubjectAssign to
Row 2GV LP for AZJohn Brown
Row 3GV LP for COJohn Brown
Row 4GV LP for ILSuzy Smith
Row 5GV LP for INSuzy Smith
Row 6GV LP for KYSuzy Smith
Row 7GV LP for MNSuzy Smith
Row 8GV LP for MSBob Barker
Row 9GV LP for NCBob Barker
Row 10GV LP for NJBob Barker
Row 11GV LP for NMBob Barker
Row 12GV LP for NVBob Barker
Row 13GV LP for NYBob Barker
Row 14GV LP for OHBob Barker
Row 15GV LP for SCBob Barker
Row 16GV LP for WVBob Barker
(Sorry, it didn't translate well. The "GV LP for AZ" is cell A2, "John Brown" is cell B2.)
Here is the code I have so far:
View 14 Replies
View Related
May 2, 2007
I have a spreadsheet with several columns that have percentages as a whole number and with a decimal format. I need assign a score value to the cell that contains a percentage depending on a range.
For example:
If cell A1 has a value between 97%-100% then A2 should display "15", however if A1 has a value of 90%-96% then it should display "12" and if A1 has a value of less than 89% then it should display "0".
View 9 Replies
View Related
Apr 3, 2009
I've built the code for a Template form for data entry of a survey. Through Control Toolbox I've created the form and used several textboxes, option buttons, checkboxes and COMBO boxes this one in turn being my trouble.
- Sheet one is called "Data" (this is the place where all inserted information is going to be stored
- Sheet two: "Variables" (here is where I keep the required values for the como boxes - inserted manually apriorely...
Question: What is the code to assing to the combo boxes in order to have the values from the sheet "Variables" of the range A1:A4 - use the comboboxq2 for referee, I'll do the others
P.S. see the code up to now:
Private Sub CommandButtonN_Click()
Dim eRow As Long
Dim ws As Worksheet
Dim inf As WorksheetFunction
Set ws = Worksheets("Data")
Set ws = Worksheets("Variables")
' Find first empty row in database survey
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ................
View 9 Replies
View Related
Feb 9, 2008
I am trying to assign a named range to a series on a preexisting chart in a sheet, but I get the error that "unable to set values property of series class". This is the code example that DOESN'T work:
Sub assign_values()
Dim myrange As range
Set myrange = range("a1")
Set myrange = union(range("a5"),myrange)
activechart.seriescollection(1).values = myrange
End Sub
However, this code DOES work:
Sub assign_values()
activechart.seriescollection(1).values = range("a1")
End Sub
I don't see why the first wouldn't work, but the second would work. I suspect it is something to do with the union function. i am trying to go through a set of data and then group certain cells into a named range to be used on the graph.
View 3 Replies
View Related
Jul 2, 2014
I'm a VBA beginner In my Excel worksheet, I have 1 column with data (column B). The data is in rows 5-250. This data is student majors.
Now, I want to assign each major a department and display the department in column U.
I was able to complete this for row 5 by using If-Then statements (see snapshot below):
[Code] ......
However, I would have to copy and paste that code over 200 times and replace the B5 and U5 with the correct row number. How can I get the macro to do this for each cell in a specified range?
I tried the following, but it did not work:
[Code] .....
View 3 Replies
View Related
Oct 23, 2008
i have column with 40.000 entries. I have added to the column entry my ID
=B1&"?aid1425". How can i get rid off colum A without effecting column B with the ID?
View 3 Replies
View Related
Dec 22, 2012
Is it possible to assign a formula to a name that is defined and use the defined name, that will be easier to use.
say =VLOOKUP($A:$A,Sheet1!$A:$B,2,FALSE)
I would like to use the above function and name it. Is there a way out?
View 3 Replies
View Related
May 1, 2009
I have a list of numbers such as:
A01
A23
A53
A64
A74
A128
B01... the goes on too approximatly D128
and i wanted to know if there is a way to assign the value to the row?
For example:
A23 in column A row 23
A53 in column A row 53
is there any method that achieves this?
View 10 Replies
View Related
Jan 15, 2010
This code finds the second avaliable row from the top of the sheet.
View 3 Replies
View Related
Sep 22, 2005
I have read a lot of posts, and maybe I am way out of my leage.
I am trying to setup a spreed sheet for our fire department.
We want to create a way to track training pay. When someone attends the
training we want to just put an "x" in next to their name in the column for
that training event rather then 9.75 for example. But we also want to be able
to add up all training for that individual for the year and see what we paid
them.
The idea is we have a sheet that shows us checks of who attended and who
did not. But we also want to add up the pay associated with that x.
So:
We want column A to be member names
Column B is a training event.
We want to go down and put in an "X" for each member who attended.
Now What I want is to have that "x" be associated with a dollar value. For
example lets say that they get $10 for that training.
THe idea is that I can run a total at the end of the year for each member
for each "x" and see how much money they recieved. I.E. the "x" is a hidden
value maybe that when I add up all the "x"s for a member it will give me
total dolar value for all training they attended.
View 9 Replies
View Related
May 29, 2012
I want to assign a text to a value. e.g if i have 2, it should come up as negative.
View 9 Replies
View Related
Jun 11, 2013
I want to assign a value to a cell so that when a word is entered into the cell, it registers with a sum formula at the end of that row. I am trying to rank a group of people based on organization membership. For example...
John Smith gets 30 points for being president of a team, 20 points for being vice pres, 10 for being secretary, etc. There are 10 teams, A-J, I want to be able to just enter the letter of the team into a cell in the column corresponding to the position that John Smith holds.
At the end of Johns Smith's row, i need a sum formula that gives me a numerical value to rank each person based on involvement.
View 1 Replies
View Related
May 30, 2014
I don't see why I cannot populate the following array The usedvar is an array of letters and I'm trying to build a new array that converts them into ascii.
Code:
Dim usedvar_asc() As Variant, f As Integer, g As Integer, temp_usedvar_asc As Integer
Dim temp_usedvar As String
For f = 1 To UBound(usedvar)
temp_usedvar = usedvar(f)
usedvar_asc = Asc(temp_usedvar)
Next
View 9 Replies
View Related
