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


ADVERTISEMENT

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

Nested Loops. Start & End Loop

Dec 5, 2006

is there any body kind enough to look into this code and tell me from where the loop body starts and ends and which is the loop variable and how it is being incremented or decremented. i know it is a stupid question but i admit here that i learn and manage to update my sheets only because of this forum help, as i have absolutly no idea of VBA when i started some couple of months back but know i can do little bits.
thanx again for all the help i got so far and hope to get some solution this time as well

Sub LowRisk_Chart()
Clearout
Voffset = 0
Vcounter1 = 2
Vline = Sheets("ChartWork"). Cells(Vcounter1, 1)
Vblocks = Sheets("ChartWork").Cells(Vcounter1, 4)
Vstdman = Sheets("ChartWork").Cells(Vcounter1, 5)
Vlength = Vblocks

View 3 Replies View Related

Not Completing Sub - Skipping To Another Sub

Mar 16, 2012

I have the following coding that I have tried in both the Sheet code and as a module at different times.

Sub CopyEmployed()
Sheets("Employment").Select
Range("A3:L200").Select
Selection.ClearContents
Range("D4").Select
Sheets("Act1").Select

- And there will be more here when I can get this first part working-

When I step through this works up to "Selection.ClearContents" but then ignores the next two lines

Range("D4").Select
Sheets("Act1").Select

and goes straight to the Private Sub coding below that I am using to change the sheet tab names (This is in the ThisWorkbook section and works perfectly)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LinkedCells As Range
Dim ArrayOfMatchingSheets As Variant
Dim i As Long
Set LinkedCells = Sheets("Menu").Range("E8:E22")
[Code] ....

I can continue to step through this until it eventually returns to

Range("D4").Select
Sheets("Act1").Select

It may then run the remainder of the coding but what I have tried so far produces more Runtime and type mismatch errors which I will post after I can fix this.

Bottom line is I dont want the change sheet tab code run - I need the remainder of the Sub CopyEmployed() to run.

View 9 Replies View Related

Completing This IF Code

Feb 19, 2009

situation: if cell I22 in Sheet1 = "MFRHTC", a message box will pop up and give the user some info and at the same time ask if this is a Fed Ex shipment

if user selects NO, then nothing will happen

if user selects YES, then the user willl be directed to cell L15 in Sheet2 in order for them to input their ship to address

here is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("I22"), Target) Is Nothing _
And Target.Value = "MFRHTC" Then
Msg = "Units will provide the following in order to have ammunition Fed Ex to HTC's " & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & " POC" & vbCrLf
Msg = Msg & " Unit ship to Address / CANNOT BE A P.O. BOX" & vbCrLf
Msg = Msg & " Phone Number" & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & "Input the required info in the Fed Ex ship to address Box"
MsgBox Msg, vbInformation, "FED EX AMMO INFO REQUIRED"
If MsgBox("IS THIS A FED EX SHIPMENT REQUEST", vbQuestion + vbYesNo, "SELECT EITHER YES or NO") = vbYes Then

End If

End Sub

View 9 Replies View Related

Completing Footer From Initial Userform

Oct 21, 2009

We currently use Excel to make reports of data obtained in field surveys. General data is repetitive throughout report. We have a title page, general particulars (GP) page, table of contents then data table pages. Each data page has info from general particulars page. i.e project name, date, inspector etc. Right now some cells of data table pagess have formulars pointing to applicble cells on GP page.

Presently I am designing a userform to input title and GP page data. So far OK.

Question: Can I take input on userform and add to footer. Info to be added after or inserted within text already there? i.e. Surveyors Name: ?(left footer) something center and something right. What type of code needs to be added and where do I place it .

View 9 Replies View Related

Completing IF Function With Date And Time As Inputs?

Jan 16, 2014

Using the attached doc, and its legend tab, I need the following columns corrected as I am seriously tripping up on formats vs inputs for outputs.

1.) Column "N's" result based on the duration output of the preceding column "M" is:

<=4 = 40
5 = 30
6 = 20
>=7 = 10

2.) Column "Q's" result based on the duration output of the preceding column "P" is:

<=18 == 40
>=19<=24 == 30
>=25<=48 == 20
>=49 == 10

The legend tab shows the info as well.

View 3 Replies View Related

Excel Not Auto-completing Long Word?

Jun 24, 2013

I have to type in a long place name with 10 letters repeatedly and whereas before Excel completed it after I typed the first two letters it has now stopped doing so. Is there a way to reactivate this facility? Other names do get auto-completed when typed in the same column.

View 3 Replies View Related

Completing Probation Period This Week From Join_Date

Feb 5, 2007

I have got Names in column A and Join_Date in column B. In column C I want those whose Probation period is going to finish this week. Probation is of 90 days.

View 9 Replies View Related

Completing Range Of Cells Based On Data On Another Sheet Prompted By Dropdown Selection

Mar 23, 2014

First sheet has a client name and several cells of data along the same line. All of the data on this sheet is kept long term.

The second sheet is completed copied and deleted. In completing the second sheet the client name is selected of a drop down list and pulled from sheet one.

What I want to do and not sure if there is a way is that when I select a client and populate it on sheet two I would like the related data on that clients line from sheet one to populate to same named lines on sheet two.

if this is possible and if so provide a brief description of how it would work

View 3 Replies View Related

Excel 2007 :: Spreadsheet To Track Group Timeline For Completing Strategy Meetings With Customers

Apr 30, 2014

How to use conditional formatting in Excel 2007 (). I'm creating a spreadsheet to track my group's timeline for completing strategy meetings with our customers. When we have a meeting, I will enter the date of that meeting in a specific cell. In a successive column(s), I'd like for a cell(s) to turn a specific color based on the length of time from the last meeting (or if there is no date for a meeting entered - i.e. no initial meeting has been set).

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

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 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
Worksheets("Name").cbobox(x).Clear
Next x

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

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







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