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()
Selection.Insert Shift:=x1ToRight
Selection.Insert Shift:=x1ToRight
Selection.Delete Shift:=x1ToLeft
Selection.Insert Shift:=x1ToRight

View 5 Replies


Keeping Track Of Which Macro Is In Which Module Since You Can't Rename The Module?

Mar 27, 2009

After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?

View 2 Replies View Related

Copy Codes Of Module 1 And Transfer To Module 2?

Jan 24, 2013

Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?

View 1 Replies View Related

Making Msgbox In One Module With (Yes) Linking To Different Module

Jan 19, 2012

i got a question how i can make a msgbox in one module with 'Yes' linking to a different module.

such as: this is located in module2

If MsgBox("Do you want to activate module1?" & vbCr & _
"" _
, vbYesNo, "Choose") = vbNo Then Exit Sub

i want that vbYesNo different

No as in Exit Sub

yes as in activate Module1

View 5 Replies View Related

Calling Module In Module With Variables

Oct 25, 2011

What i have at the moment is a module that contains code where i call a different module that i use as a procedure.


If Sheet1.Range("C4").Value < Sheet1.Range("A2").Value Then
If Sheet1.Range("K4") = "" Then
MsgBox "Please check 06:00 tasks not done yet!"
Cell = "Range(" & Chr(34) & "F4" & Chr(34) & ")"
If Sheet1.Range("C4") + 0.042 < Sheet1.Range("A2") Then
Run "EmailProSheet"
End If
End If
End If

EmailProSheet is what i call but now i want to use the variable "Cell" in the procedure as well?


MsgBox Cell
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next


As it is now everything is working fine but does not return a value in "Cell" if the procedure is called. Is there another way?

View 5 Replies View Related

Run-time Error '50289' :: Project Protection / Module Protection / Module Visibility

Aug 18, 2004

I recieve an error :

Run-time error '50289':
Can't perform operation since the project is protected.

When i try and run my code.

The code does add parts to modules and workbook events, but I need the code to be protected.

View 9 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"
Value = "=$G$3"
Value = "=$G$4"
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

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 -

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 -

VARIABLE.Range("D4").Value = "blah blah"

View 9 Replies View Related

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

View 6 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 <> ""


While ActiveCell. Offset(5) <> ""

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

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

If (cells(x, 95) = "GotYou") Then
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:

startCell = ActiveCell.AddressLocal
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()

Selection.Delete Shift:=xlUp

Selection.Delete Shift:=xlUp

Selection.Delete Shift:=xlUp

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

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

Copyrights 2005-15, All rights reserved