Assign Loop Counter To Range Row

Jun 22, 2006

How do you assign a loop counter value to a range row value? Scenario: Add cells from a column that arent 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


To Assign A Counter To The MsgBox- Exit Loop

Aug 31, 2007

This loop is to find if there is the string "Need Parent " in the range. If so the Msgbox notifies the user.

After it finds the string, the loop needs to exit- I tried to assign a counter to the MsgBox, but no go....

For i = 6 To lLrw
If Range("D" & i).Value = "Need Parent" Then
MsgBox "Stop. Parent Records Still Need To Be Created."
End If
ii = MsgBox + 1
If MsgBox > 0 Then Exit For
End If
Next i

View 9 Replies View Related

Non-consecutive Loop Counter: The Counter Skip Values In Between

Jun 29, 2006

How can I write a For-Next Loop and have the counter skip values in between. For example:

For i = 8 to 17

When that is completed, the counter jumps to 21:

For i = 21 to 30

When that is completed, the counter jumps to 34:

For i = 34 to 43

View 3 Replies View Related

Avoid Changing A Loop Counter Within A Loop?

Oct 24, 2009

I've worked on a solution for this thread ( but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.

The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.

Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?

View 3 Replies View Related

Using For Loop To Assign Numbers To A Series Of Triangles?

Dec 18, 2011

am looking for a code that uses for loops of any other type of loops to create an array shown below:

1 2 6
6 5 1
2 3 7
7 6 2
3 4 8
8 7 3
5 6 10
10 9 5
6 7 11
11 10 6
7 8 12
12 11 7

The array above represents the node number of the triangles within a rectangle as shown in the image. [URL] .....

View 8 Replies View Related

Increment Counter Between Date Range

Feb 13, 2014

I have a list of information contained in a massive spreadsheet. I am trying to create a counter that increments starting with 1 up to X between two date ranges. The increment counter should start with 1 for the easiest date and increment up X up to the last item in the list. The list contains tasks names with some tasks have dates but there are some tasks that don't have dates (blank cells).

View 6 Replies View Related

Var Formula With Range Based On Counter

Feb 23, 2012

I have a macro that I am working on that, I hope, will calculate the variance in my worksheet. The issue is, I am removing the outliers from my formula, so I need to loop through a range counting the "non-outliers." When the loop is complete I will know the last cell needed for my range. How would I take that variable and turn it into a range, similar to this: "A2:a(count)"

Here is what I have currently

Dim cell As Range
Dim count As Integer
Dim var As Long
var = 0
count = 0


View 1 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.

thisString = "SubjectNames" & strSubjectFamilyOfGroup

MsgBox Range(thisString).Address

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

View 1 Replies View Related

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 View Related

Assign A Name To A Range Of Cells

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

Assign Value To Range Object

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

Assign Range To Variable?

Jul 11, 2014

Why the variable DataRange won't set as below. I get Run-time error 9: subscript out of range.

Sub CreatePivotTable()
Dim DataHeaderRow As Integer
Dim DataRowCount As Integer


View 7 Replies View Related

Assign Range To Variable

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

How To Assign List To Named Range

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?

Dim UList As New Collection
Dim rCell As Range
If TypeName(Selection) = "Range" Then


View 4 Replies View Related

Assign A Non Contiguous Range To An Array

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

Assign Variable To Selected Range

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

Assign Autofilter Results To A Range Object

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

Does The Line Of Code Assign The Value Of Cell A5 To The Range

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

Assign Range Name Based On Cell Values

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

How To Assign Range Using Defined Cell In Excel

Nov 22, 2011

How to assign a range in excel using a defined (different) cell.

I've this formula

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

Assign Name To Range Using Variables And Cell Values

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

Assign Numerical Values By Letter Across A Range

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.


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.



the output of the above sequence should be

1+1+1+2+4+4+0+4+4+0 = 21

View 4 Replies View Related

Assign Range & Interate Through & Insert Values

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

Create/assign Outlook Tasks From A Range Of Cells

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

Assign A Score Value To The Cell That Contains A Percentage Depending On A Range

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

Assign Range Values To Control Toolbox Combo Box

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

Assign Named Range To Chart Series Collection

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

Put A Counter To 001 Rather That 1?

Feb 3, 2010

I want to use the macro to run an XML file but I would like the images to run from 001 to what ever say 300 is this possible? So rather that 1,2,3 the counter would be 001, 002, 003 etc.

View 3 Replies View Related

How To Put A Counter

Apr 28, 2009

I would like to put a counter (number) in my excel sheet. Consider that like a bill number. Any time I open my Excel I want to have new number on top. For example 201, 202 so on and so forth. I would appreciate if anyone had this project before and willing share it with me.

View 9 Replies View Related

Sum With Counter

Apr 13, 2007

I am using excel with system called PI, where i can retrive datas from network

I have proble with calculating day (With sum) becoz date is virying daily and data changes accordingly

22-Mar-07 23:40:101
23-Mar-07 23:40:101
24-Mar-07 23:40:101
25-Mar-07 23:40:101
26-Mar-07 23:40:101
27-Mar-07 23:40:101
28-Mar-07 23:40:101
29-Mar-07 23:40:101
30-Mar-07 23:40:101
31-Mar-07 23:40:101
01-Apr-07 23:40:101
02-Apr-07 23:40:100
03-Apr-07 23:40:100
04-Apr-07 23:40:100
05-Apr-07 23:40:100
06-Apr-07 23:40:100
07-Apr-07 23:40:100
08-Apr-07 23:40:100
09-Apr-07 23:40:101
10-Apr-07 23:40:101
11-Apr-07 23:40:101

For this example Now i want to start counting from 9th April.
Sure i dont want to calculate from 22 march to 1st april

Sure this wont be same every time, becoz after 10-15 days this figure will become zero and stays for 0 for 10-12 days and again become 1 and keep continue
So every day automatically 1 day go out from top and 1 day will add on bottom with either 0 or 1 (But if zero it will continue for few days or 1 than also case is same)

So i like to start counting from where it changes from 0 to 1 again
Hope i explained question properly

View 7 Replies View Related

Copyrights 2005-15, All rights reserved