Loops And Variables

Nov 10, 2009

I'm trying to better understand loops and variables. I have an instance I'd like to try and implement but I'm struggling with it. I know how to set a variable as 1 and loop through the code increasing the number each time but this one is a little different.

At the start of my code, I scan the worksheets for the worksheet names -

Monday
Tuesday
Wednesday
etc etc

and then assign these worksheets as

Monday = ws
Tuesday = ws2
Wednesday = ws3
etc etc

That way in my code I can do things to the workbooks like -


ws.Range("D4").Value = "blah blah"
and always know it will be tackling the right worksheet regardless of the sheets position in the workbook or sheetcode.

Well I have lots of changes to make on each of these sheets (Monday -> Sunday) which are identical. What I'd like to do is something like this -


For each VARIABLE in (MY VARIABLES WS,WS2,WS3 ETC)
VARIABLE.Range("D4").Value = "blah blah"
Next VARIABLE

View 9 Replies


ADVERTISEMENT

Variables In For Loops

Oct 14, 2006

Is it possible to use a variable to change a controls name in a For Loop? I have several worksheet combo boxes that have the same root name, e.g., cbobox1, cbobox2. cbobox3, etc. I want to be able to keep the main name and change the numeric part in a for loop and also perform some function with the control. For example:

For x = 1 To 10
Worksheets("Name").cbobox(x).Clear
Next x

View 6 Replies View Related

Using Variables In VBA Loops To Get Form Textbox Contents

Jan 28, 2013

I have written the following snippet in my code, the purpose of which is to cycle through the text boxes on a form (frmPaymentOrders) and transfer the value of each text box txtPO1 to 10 to the variable PORow (Dim as Long). These will then be pasted into the correct part of my worksheet.

But I keep getting an "Invalid Qualifier" error - even though I think I have correctly qualified the PORow variable as Long.

Code:
For i = 1 To 10
PORow.Value = ["frmPaymentOrders.txtPO" & i]
Next i

View 7 Replies View Related

Match And Copy: Declare The Variables Correctly And Have The Operations Done In A Series Of Do/ Loops Or For/Nexts

Feb 2, 2007

I have a series of operations to carry out and, while I can do the code for each individual one, how to declare the variables correctly and have the operations done in a series of Do/ Loops or For/Nexts. Especially the declaring of named ranges as variables. Also a bit uncertain of the best way to find and coy the match. I have attached a simplified version of the workbook, with explanations on it.

Basically what I need to do is loop through a series of named ranges and then loop through the names in each, match each name with a name in a master list (with a flag as an image), add an e-mail hyperlink to that flagged name and copy both to a new cell.

View 2 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Loops In Vba

Apr 13, 2007

how can i make a loop throught the G colum and to check if the cell before the currect is bigger/ lower or equal to the currect cell. and if its higher then an arrow will be shown '^' if its lower an upside down arrow will be show 'v'
equal will show -> arrow. example:

G2 = 200
G3 = ^ 300
G4 = -> 300
G5 = v 209

so how can i change the G3 to G4 G5 etc.. in a loop in the: Range("G3").Select command? and i need to change the

.Value = "=$G$2"
to
Value = "=$G$3"
Value = "=$G$4"
etc...
in 2 places................

View 4 Replies View Related

Macro That Loops

Oct 18, 2008

i have this code that upon opening of workboot it generates the next number thats available on sheet 2 i then enter the data that i need to and click the button it then transfers to sheet2 and prints 2 copies and then clears the cells that i have input data to

what i really need it to do is exactly as it does right now but to then change the next number available to what it is ....

View 14 Replies View Related

Nested Loops..?

Aug 11, 2009

I seem to be having trouble creating a nested loop. It seemed simple enough in my brain holder, however, in real life...Well I am here aren't I. Here is my

View 2 Replies View Related

Using For Loops When Referencing

Aug 20, 2009

First of all, I'm completely new to both this forum and VBA. I have just done a programming course in java before. I hope you will forgive me if this have already been posted. No to my problem.

I am tryring to use different projected values to project other subparts of those units, which is done in the sheet mean needed weekly. Tje values are taken from projections. The answer that I get from mean needed weekly should be copied to a third sheet.

This is the code that I am using right know, but I cant get the for loop to work (or more exactly to be able to place the "i" in my text without making compilation errors).

View 9 Replies View Related

Eliminate Loops

Oct 9, 2009

One of the multitude of areas where I still need drastic improvement is with efficiency. Specifically, avoiding loops.

Are any of you aware of any good, basic level sites, blogs, posts, etc., about better methods or processes? I'm not particularly experienced with programming (I've been a software TESTER for 13 years and just started some programming in recent months), so the more basic the better.

View 2 Replies View Related

Loops With Formulas

Oct 2, 2008

I have recorded a macro that cleans up my data that I receive from an external source and inserts two columns.

I recorded a seperate macro to execute an "if formula" that evaluates Column A and B and if A is blank return the value in B, else return the value in A. I wrote the formula =if(a1=" ",b1,a1).

I referred to the text I got in the class and found that I can use a "Next Loop". I added to the formating macro the Next Loop critera and instead of inserting text I inserted the above formula.

It ran and returned data, however it was not in the column I intended, it cascaded off to the side of my data in a step down (kinda like stairs).

View 9 Replies View Related

VBA Colourindex & Do Loops

Nov 3, 2009

I need to maintain a KPI in excel2000 which details weekly progress, I normally do this using some condition formatting and hiding cells, I have to be honest its not pretty.

What I would like to do is to use the colorindex function to show the wk breakdown status and a Do loop on my target range, I think!...... I've a basic example below,

If I could show the benefits to my boss he might even let me take a VBA course.....

View 9 Replies View Related

Two Loops In One Module

May 7, 2007

Is it possible to do 2 loops in one procedure? I want to copy and paste formula the cells in one column and again in another column so I did two Do Loops. It only ran for the first loop but not on the second, although they have almost the same code (I just edited the column from C to I). Please see below

Dim RowCount As Integer

Sub Format()
Columns("C:C").Select
Selection.Insert Shift:=x1ToRight
Columns("I:I").Select
Selection.Insert Shift:=x1ToRight
Columns("K:L").Select
Selection.Cut
Columns("R:R").Select
ActiveSheet.Paste
Columns("K:L").Select
Selection.Delete Shift:=x1ToLeft
Columns("L:L").Select
Selection.Insert Shift:=x1ToRight

View 5 Replies View Related

Syntax Of Loops

Feb 2, 2008

I am trying to figure out what a past employee's macros do and how they do it. I would simply like to know what the followin syntax enables you to do:

While ActiveCell <> ""
...
...
Wend

or

While ActiveCell. Offset(5) <> ""
...
...
Wend

View 3 Replies View Related

Copying Cells Using Loops

Mar 19, 2007

I'm trying to copy cells from one sheet to another. I know that if I know what cells I want then I can use something like this:

View 9 Replies View Related

Using Loops To Find Data

Feb 4, 2010

I want to find some code that will search down a column until it finds one of a number of different words. when it finds one of the words it stops and deletes the row on which the word is contained. after deleting the data in the row, it continues searching down the column until it finds another of the words.

for example......see work sheet attached,

I want some code that will search down column B until it finds Sally or Robyn. As soon as it finds either of these names it stops and deletes all the data in that row and then continues searching down column B to find Sally or Robyn again........

Is using a loop the best way to go with this?

MOD: thread moved to parent Programming forum (VBA)

View 14 Replies View Related

Insert Row And Data Using 2 Loops

Dec 9, 2011

I need to insert data as following pattern :

1 1
2 1
3 1
3 2
4 1
4 2
4 3

View 1 Replies View Related

How To Control Many Loops Directly After Each Other

Aug 7, 2012

How I can control many loops directly after each other. For example I have this script:

If (Cells(x, column) > 35 And Cells(r, column) < 25 Then

If (cells(x, 75) = "GotU") Then
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Else

If (cells(x, 85) = "GotYah") Then
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Else

If (cells(x, 95) = "GotYou") Then
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
End if

Is this script correct if I want the loops to check all data that I write (for ex. GotYou, GotYah etc.) or do I need to end every if before next if? I want that the loops makes all 3 controls.

View 9 Replies View Related

Loop That Loops Through The Values

Dec 5, 2007

On my sheet in Range("a4:a20") I have the names of the "Named Ranges" that I set referenicng ranges on different worksheets. Range("b4:b20") I count the # of rows for each of my "Named Ranges." I need a loop that loops through the values in column "B" and if it is > 0 then to take the corresponding "Named Range" in column "A" and paste it starting in cell "E6".

This is the start to my code it isn't working


Dim CellV As Range 'i want this to be the Cell Value
Dim CellValue_Range As Range 'this is the range B4:B20
Dim NRange As Range 'This is range A4:a20
lastrow = Range("a1").Value 'this counts the number of named ranges I am dealing with

For Each CellV In CellValue_Range
If CellV.Value > "0" Then
Range("a4").Value.Copy 'this range "a4" should change one row down each time it loops (this also correspondes to my "NRange"
Range("e" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If
Next CellV

View 9 Replies View Related

Using Loops For Taking Averages

Jul 14, 2006

I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows:

Range("A2").Select
startCell = ActiveCell.AddressLocal
ActiveCell.offset(3,0).select
stopCell = ActiveCell.AddressLocal

Now, clumsy programming aside, I just want to say something like this

averageCell = AVERAGE(startCell:stopCell)

View 2 Replies View Related

Using Loops On Current Code

Jul 27, 2006

I currently have the following macro - as you can see it is quite repetitive. I know it is possible to do this using a loop any ideas on how to do this would be appreciated.

Sub Clean()

Rows("4:4").Select
Selection.Delete Shift:=xlUp

Rows("5:5").Select
Selection.Delete Shift:=xlUp

Rows("6:6").Select
Selection.Delete Shift:=xlUp

Rows("7:7").Select
Selection.Delete Shift:=xlUp

I thought that this would work - but not so probably some basic syntax error:

Sub Runny()
For x = 4 To 17
Rows("x:x").Select
Selection.Delete Shift:=xlUp
Next x
End Sub

View 9 Replies View Related

Loops Through Range In Specified Increments

Oct 15, 2006

For i = 10 To 260 Step 10
iStrg = i
cntrl = "A" & iStrg
Range(cntrl).Value = i
Next i

I have been trying to use this code which displays 10 in A10, 20 in A20, 30 in A30....etc. What i am trying to do is display 0 to 260 with a step of 10 in the cells A2 to A28, could anyone be of assistance?

View 7 Replies View Related

Loops Creation For Treeview

Oct 24, 2006

I write macros that creates a treeview according to data from column "A". In attached example I have series of numbers, that means following:

0 is a root (A1), 1 is its nod (A2), 2 are nods of 1 (A3,A4), three appearance of 3 are nods of 2 (A5,A6,A7) , e.t.c.
It means that I must dinamically to create the loops.
How to make it.
All that I know is a static creation.

View 2 Replies View Related

Use Of For Loops For Values That Are Present

Nov 22, 2006

If you want to create a FOR...NEXT loop for values that are present, how would you go about it? Sometimes you may have 1,2,6,8,12,13 and the next 2,3,5,6,7,10,11,14.
It may be a number between a certain range, but the sequence and step will change every time (for trying to get a Auto-filter to cull a list based on descriptor)

View 2 Replies View Related

Speed Up Multiple Loops

May 17, 2007

I have a triple loop I would like to create a triple loop
code at the moment

m = Worksheets("txt"). Range("AM5").Value

n = Worksheets("txt").Range("AM1").Value
o = Worksheets("txt").Range("AM2").Value
p = Worksheets("txt").Range("AM3").Value
q = Worksheets("txt").Range("AN1").Value
r = Worksheets("txt").Range("AN2").Value
s = Worksheets("txt").Range("AN3").Value
t = Worksheets("txt").Range("AO1").Value
u = Worksheets("txt").Range("AO2").Value
v = Worksheets("txt").Range("AO3").Value

Worksheets("txt").Select
For myRow = 1 To m
myCol = 5
Worksheets("txt").Cells(myRow, myCol).Select
With Selection
If .Value = "" Then
.Value = n
Else: .Value = .Value
End If
End With
Next myRow

View 9 Replies View Related

Loop Not Completing All Loops

Jul 3, 2007

Im using the following code to assign numerical values to words in two columns, then multiplying the values together and painting a cell with a specific color assigned to the final number. The problem is it wont do this past row 19

mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row
For m = mLastRow To 5 Step -1
' assign numerical values to the words in these columns
e = 22
r = 23

' Give Extent a value
If Cells(m, 9) = "Minor" Then
Cells(m, e) = 1
End If
If Cells(m, 9) = "Moderate" Then
Cells(m, e) = 5
End If
If Cells(m, 9) = "Serious" Then
Cells(m, e) = 8
End If
If Cells(m, 9) = "Critical" Then
Cells(m, e) = 10
End If....................................

View 4 Replies View Related

Nested Loops For Different Ranges

Mar 26, 2008

I have a basic while loop that does what I need it to do stand alone. Now I want to replace the 3 hard coded ranges (n51,r26,m26) with ranges that update during iteration #2. The new ranges for the while loop would be (n52,r27,m27). I will eventually have more than 2 iterations.

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 3/19/2008 by User
'
' Keyboard Shortcut: Ctrl+g
'

Dim rng As Range, Acell As Range
Dim rng2 As Range, Bcell As Range
Dim rng3 As Range, Ccell As Range

View 3 Replies View Related

Convert Sheet To PDF File Using Loops

Aug 14, 2009

Using a macro, how can i convert one excel sheet to one pdf file using loops etc.

e.g. Go to sheet named "sheet1"
save this sheet as "sheet1.pdf"..

Go to "sheet2"
save this sheet as "sheet2.pdf"

View 3 Replies View Related

Countifs Function Using Dates And For Loops

Jul 14, 2014

I am trying to develop a For Loop that will do countifs functions based on the criteria in the For Loop. Part of the countifs criteria is determining which values in a range are within certain dates. I have isolated the month and year data and am looping with for loops through the years (outer loop) and months (inner loop). The issue that I am running in to is how to place the month and year into the countifs in VBA: month/year. When I do these calculations in the spreadsheet it is easy to write for one of the criterias: ">=1/2013", but trying to do that same formula with variables is become much more challenging.

How could I write a countifs that uses variables to do the same as ">=1/2013"? In essence: ">=monthCount/yearCount".

View 8 Replies View Related

Nested Do Loops With Find Next Capability

Jun 20, 2007

how to nest do loops with find next capability. I have written code (that does not work, of course) that I hope will explain what I am trying to do. The code error is something like 'Object needed'.

Do

Range("r:r").Select
Selection.Find( _
What:="2", _
LookIn:=xlValues).Activate

n5 = ActiveCell.Address

View 9 Replies View Related







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