my worksheet has columns e1:il100 populated with data. what i am trying to do, is to insert a column to the right of column 'e', copy the contents of cell 'e1', to rows 3:100. then i want to cut e3:f100, and place it in the next available row in columns b:c, delete columns e:f, and then goto the next populated column, which i believe is now column e, and start over, until all columns have been moved to columns b:c.
I want to perform double_click event for combobox, but only for cells in certain columns and rows.
this is code :
[Code] ....
I have to loop through columns 6 to 67 with step 1, and rows 19 to 409 with step 5, but don't know how . I attached sample, coloured cells needs that double_click event.
Attached File : Loop_Column_Row.xls
I am having some problems in setting up a macro that is intended to do the following
Run for every cell in a collumn a check if cell =2
If true then B1 is copied to that cell.
If false then nothing
This seems rather simple but the loop should go to collumn B, C, D, E, F,... until the last collumn.
Every time the collumn shfits the cell to be copied also shifts so is B1, C1, D1, E1, F1,...
I have a data set that I need to reorganise
Currently the data runs across the columns as:
COL A:C = Descriptions
ROW 1:1 = Week End dates
ROW 2:2 = Week Number
and D3:BC122 = Values
AccData
[code].....
What I need to do is create some VBA that will loop through E:BC moving the data in the column to under the data in Col C each time (and also A:C)
I can create loops that add 1 to row number but am stuck adding 1 to column in VBA.
I have two columns of data J2:K1000, I need loop through both columns at the same time and copy the data into cells into E1 and F2, then run through the remainder of the code. The code below I have adapted but cannot get it to work correctly.
HTML Code:
Sub Test()
Dim LastRowColJ, LastRowColK As Long
Sheets("Sheet1").Select
LastRowColJ = Range("J65536").End(xlUp).Row
For Each c In Worksheets("Sheet1").Range("J2:J" & LastRowColJ).Cells
Sheets("Sheet1").Range("E1").Value = c
For Each d In Worksheets("Sheet1").Range("K2:K" & LastRowColJ).Cells
[code]....
I have a dataset with has 12 columns of data followed by 49 columns of values (one for each product) What I need (to be able to pivot it) is one column of values. I had written a quick for each loop but this doesnt work for colums:
Sub Adjust()
' Find last cell populated by data
Range("A65535").End(xlUp).Select
Last_Cell = ActiveCell.Row
'Rename a column heading
Range("M1").Value = "Partner"
Range("M2:M" & Last_Cell).ClearContents
'Loop through each column and copy data values
For Each Column In Range("N:BJ")
' Find first empty cell
Range("N65535").End(xlUp).Select
Start_Cell = ActiveCell.Row + 1.....................
i would like to create loop throug among columns, is xlup or xldown methods will make sensne ?
View 2 Replies View RelatedI've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
I tried this:
For Each col In NewSh
ColNum = ColNum + 1
Range(RwNum, ColNum).Select
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
Next col
I am OK to set values to the third row of "Loop Folder" from a column in " T" but I haven't figured out how to capture the remaining 22 columns of this worksheet.
Workbooks("Loop Folder.xls").Sheets("Sheet1"). Range("A3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C5").FormulaR1C1
Workbooks("Loop Folder.xls").Sheets("Sheet1").Range("B3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C6").FormulaR1C1
Workbooks("Loop Folder.xls").Sheets("Sheet1").Range("C3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C16").FormulaR1C1.............
I am trying to loop all columns in each of a number of sheets using current region.
It selects the current region OK but the column counter only shows 1 as the number of columns in any sheet.
The Cells(6,1) likely has something to do with it but I do not know any other way to point to the current region I need. I do not know in advance how many rows or columns I am starting with but each has the required Row 6.
For x = 1 To Sheets.Count
Sheets(x).Activate
Cells(6, 1).CurrentRegion.Select
Selection.CurrentRegion.Name = "Mydata"
'Loop all columns in sheet
For y = 1 To Range("MyData").End(xlToRight).Column
Cells(5, y).Select
Next y
Next x
I have a list of names with blank cells in Column A cell 2.
I want the programme to scroll through this column until it finds a name, when it does I want it to look in cell offset(1, 1), in this case B3 to see if there is a number. I want this number to be moved to offset (0, 1) AND any subsequent numbers below it until the next blank cell in that column (B).
In the attached image cell B2 will contain all numbers in cells B3:B9 with a / between them. Cell B10 will have number from B11 and B12 will have B13:B15. If there is no number it will just continue to the next name in column A. My rough code below delivered the first number only in the relevant cell and I'm not clever enough to get it right. It does not have to be super slick as it's for one time use.
I need numbers in column B cleared as they are concatenated into the single cell.
Spreadsheet.jpg
I am trying to run a loop on a few columns simultaneously, from the last row to the top (well, until the 2nd row).
So I have a few columns of data, A through I, and the number of rows they populate changes all the time.
So how would I run a loop that will check for the following:
If a certain row in Column F is greater than the previous row in column F by 1.5times, then in that same row, if number in Column B is greater than number in E, the code should put the the value of E in a new column, lets say M (in the same row)
I know that sounds very confusing, but is that even possible? I am playing with loops, and I can only do very simple loops where I define a range, like
My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.
Example of what I have so far:
Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub
I have a table of data (lets say A2:H30) i want to populate so i want the macro to loop based on how many columns i have? I am using this to post the data based on # of rows..
Sheets("Main Cal").Select Dim bottomA As Integer
bottomA = Range("D" & Rows.Count).End(xlUp).Row
Dim c As Range
Dim ws As Worksheet
For Each c In Range("D3:D" & bottomA)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then
I am stuck with looping columns and rows.What i am trying to do is... I have to loop through columns E3 to G3 and also rows of each column and check for a condition inside the column.How can i achieve that? I have the following structure.. How can i code it?
For i=E3 to G3 'Column loop
for j=4 to 47 'Row loop
'code to check if the value isnumber
next j
next i
I need to copy several columns (G:L) underneath (A:F)
I got this going using the recorder, and copy the action several times in the macro (See below).
I obviously would like to have this in a loop.
Range("G1:L35000").Select
Selection.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:L").Select
Application.CutCopyMode = False....................
I have a list of several hundred columns, beginning with column "G:G", with varying numbers of rows of data in column - each row dipicting a monthly data point. I'd like to average the numbers in each column (need to average over the appropraite time-frame) and compare that average with the corresponding average (same time-frame) for benchmark (column"F:F"). The problem is I don't have the same number of data points in each column; some have data points for every month for the past 33 years, and some just a few years; almost all have differing beginning and ending dates as well.
[Code] ....
I have a worksheet with 2 columns. The first column contains names and the second column contains numbers associated with the name.
How can I specify a name, go through the sheet to find all the occurances of that name and then paste all the numbers associated with that name in another worksheet in a set position.
I've managed to get my macro to work ok - but what I want to do now is to get it to loop through more columns. Currently it looks for email addresses in column AH (which will always be the same) then it looks for a "yes" in columnAB and if there is no "SENT" in column T then it sends an email,after which it adds the word "SENT" to column T. What I want t do is for the macro to then run on columns AC+U, AD+V, AE+W and AF+X. So basically it will loop through one column to the right each time for a total of 5 pairs of columns
View 2 Replies View RelatedI think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
View 3 Replies View RelatedI have the following code that rearranges the data based on the values in the first column (cannot post attachments). Currently, the code is grabbing the whole columns when performing cut and paste and with larger sets of data I am getting "Excel cannot complete this task with available resources" error. Need to modifying the code so it only grabs the used range as opposed to the whole columns.
Code:
Sub ArrangingColumnsByNumber()
Dim Count As Long
Dim CurrentNumber As Long
Dim CurrentColumn As Long
Count = 1
CurrentColumn = 1
CurrentNumber = Cells(Count, CurrentColumn)
[code].....
I've been trying to loop data for column A from row 1 until 6 and print the result on specified cell. But the problem is, it keeps looping until column B,C and D.
I want to calculate average result separately for column B,C, and D without taking the previous data.
Here is the code that I've done.
VB:
Sub Avg()
col = 1
Row = 1
Do
Name = ThisWorkbook.Sheets("Sheet2").Cells(Row, col).Value
If Name <> "#" Then
[Code] ....
I attached my excel. The green box indicate the result that I want : function.xls
I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.
I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.
Attached Images
Before.jpg‎
ter.jpg‎
I'm trying to use the following loop to increment the column width of a range of columns thus:
Code:
NewWidth = 0
WidthIncrement = 0.5
For MyCount = 1 To 26
NewWidth = NewWidth + WidthIncrement
Cells(1, MyCount).EntireColumn.ColumnWidth = NewWidth
Next
If I set WidthIncrement to be an integer value, the code works correctly. If, however, I make it something like 0.1, all the column widths that the loop acts on are set to 0. I suspect it's something to do with how I'm declaring the NewWidth and WidthIncrement variables - they're currently set to Double. I know that I can have decimal values for column widths (e.g. 8.43)
The loop is supposed to stop when the cell that is 4 columns to the left, in the workbook named "Complete DSS", changes numbers.
/ This is the cell ref. / This is the original
Do While ActiveCell.Offset(0, -4) = ThisTape
The number will stay the same for a while until it changes tapes, then it Should stop when they are no longer equal.
It used to work, but maybe some recent changes have messed up the function.
Sub Compare_printout_2_WDMS()
Application.ScreenUpdating = False
Dim ray, sht As Worksheet, Exist As Boolean
Dim BookA
Dim I As Long
I = Worksheets.Count
AreaNum = InputBox("¿Que es el Area?")
ThisTape = Range("C8").Value
ActiveWorkbook.SaveAs Filename:="C:Documents and Settingssmith27DesktopArea 319 printoutsArea " & AreaNum & " " & ThisTape & ".xls"
Columns("A:A").insert
LR = ActiveSheet.Range("B65536").End(xlUp).Row..........................
how to create a loop that grabs the ( t'th ) cell and prints into columns on the spreadsheet adjacently that cell and the ones before it (total = t) and repeats it consecutively until there are no more cells to transcribe into columns. For example - I have n=100 cells in a column that need to be placed into n-t columns (where t = 5 for example).
See image below for clarification, code to follow:
As you can see I need it to fill out in the yellow highlighted cells the rest of that pattern. Right now it only does it for one column, but every time I try to get it to do the rest of the columns, it prints the same first column over and over again (I'm having some severe mental block with implementing the correct loop code).
For clarification also, the user selects the t'th cell and it prints that cell and the previous (3 in this case)... and then it shifts down one cell and prints those 4 cells into the next column - which is what I want it to do.
Sub populate_range()
Application.ScreenUpdating = False
Dim rowoffset As Integer
Dim offsetnumber As Integer
rowoffset = Range("F2")
offsetnumber = Range("F2")
Range("d5:iv5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheet1.Select
Range("b4").Select
ActiveCell.Offset([offsetnumber], 0).Select
Range(ActiveCell, ActiveCell.Offset(-([offsetnumber] - 1), 0)).Select
Selection.Copy............................
I need a macro that will look - this is hard to explain so will prob do wee diagram too but basically the macro nees to start looking at sheet2 A5 (the numbers start at A5 before that is text) see if it is greater than Sheet1 ColumnA & Less than Sheet1 columnB if the number is then I need Sheet2 ColumnD inserted into sheet 1 ColumnG. There is the text END on all columns on both sheets for the loop, the reason for the loop of course is there could be 100 lines in sheet2 ColumnA & I must look at all lines in sheet1 to see if it is greater than columnA and Less than ColumnB and if it is populate Column G.
Sheet1
Column A ColumnB ColumnG
Row 11 4000 4999
Row 12 5000 6099
Row 13 6100 7400
ETC
END END
Sheet2
ColumnA ColumnD
Row5 5130 134.58
Row6 6900 6400.00
ETC
END END
After the macro the result would be
Sheet1
Column A ColumnB ColumnG
Row 11 4000 4999
Row 12 5000 6099 134.58
Row 13 6100 7400 6400.00
END END END
Sheet2
ColumnA ColumnD
Row5 5130 134.58
Row6 6900 6400.00
END END
I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).
I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).
In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.
Code:
Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)
[Code] ......
So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....