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