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)
The following code fills down column B for rows 3 to 110, regardless of the inserted "If Not IsEmply' statement. I've got formulas in Column A from row 3 to 110, but visible values in rows 3-5. I want it to fill the for the visible values only.
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
I have 2 columns with approximately 200K lines of data. There are only 84 and 823 unique values in each column respectively. On my report.
Column A is essentially a customer list, column B is a product list. I am trying to find out how many unique/individual products a customer orders. I have a separate column that has the customers in it (Column G), and in Column H, I want it to count the unique products ordered by customer.
I changed some of the data, but essentially what I attached is what I need.
I have a daily report that gives a percentage based on what is typed into the "Supplier" and "Total Sales" columns but I'm having trouble writing the following formulas since my excel knowledge is relatively low.
1. Where a cell in the "Supplier" column says anything but "Den", "Burrowed" or "Studio Nyx" I need the "60%" and "40%" columns filled with the relevant formula (=B11*60% for example) and the "100%" column blank.
2. Additionally, if the cell contains "Den", "Burrowed" or "Studio Nyx" I need the "100%" column filled and the "60%" and "40%" blank.
I have two worksheets, one which contains a list of workstation numbers, and another which contains all of the workstation numbers and additional information on the workstations in the company.
I want to remove all workstation numbers and their information which are not present on the first list from the second list.
In the first w.orksheet each workstation number is in a seperate row, and in column one (seperate cells).
In the second worksheet, each workstation number and its corresponding information is on the same row, and each different workstation is on a seperate row with its information.
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................
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 ....
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
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).
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.
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).
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.....
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
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
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
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)
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.
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:
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
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?
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.
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
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....................................
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