Loop Based On Number Of Columns

Jan 10, 2014

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

View 1 Replies


Creating Number Sequence Based On For Loop

Aug 1, 2014

I am trying to create a number counter which runs up to a value which is determined by a COUNTIF Statement in the spreadsheet. I can get the number counter to run to the given value. However, due do formatting reasons I do not want the counter to start in cell A1 but A3 instead.

Essentially my code looks like this:
ActiveSheet.Cells(3, 1).Select
Dim i As Integer
For i = 1 To Range("A1")
Cells(i, 1) = i
Next i

View 3 Replies View Related

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.

View 4 Replies View Related

Vary Columns Based On A Number

Feb 5, 2009

You can always add numbers together. however, how can you vary columns based on a number?

for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.

View 2 Replies View Related

Distribute Across Columns Based On Number

Dec 20, 2006

I have a list of parts with quantities that i would like to distribute among several columns that represent cities, based on the number of parts in stock. For Example, if i have 5 parts and ten cities, i want it to put one part in the first five. In the case where theres more parts than cities i want it to loop back to the first city and add one and so on. I attached a sample spreadsheet.

View 5 Replies View Related

Copy Cells In Loop Based On Loop Increment Being Multiplied

Feb 7, 2008

I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View 3 Replies View Related

Combine Two Columns Based On Name / Number And Score

Feb 19, 2014

I have three columns in the spreadsheet.



In another sheet (sheet2) output come like this.

Praveen 3/7
Santhosh 3/8

View 2 Replies View Related

Capture Values Based On Number Of Columns?

Jun 7, 2012

My data comes from 3 different sources

Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)

Is there a slick way to capture values based on the number of columns in that row?

View 1 Replies View Related

Insert Rows Based On Number Of Columns Used

Jan 31, 2008

I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.

Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)

View 8 Replies View Related

Trying To Count Number Of Occurrences Based On Content Of Two Other Columns

Jan 30, 2014

I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.




[Code] ....

View 6 Replies View Related

Hide/unhide Number Of Columns Based On Cell Value

Jul 17, 2007

If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.

Each number is one column.

eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden

up to 200 columns.

I tried it as a select case, but it is limited to just 22 cases which obviously not enough.

View 9 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4


View 2 Replies View Related

Reference Cells Keeping Columns The Same But Change Row Number Based On User Input

Dec 10, 2012



With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following

Row #
=C143&" , "&D143&" "&E143


How do I write the functions to keep the columns the same, but change the row number based on the number I input?

View 3 Replies View Related

Loop Through Columns And Row?

Aug 16, 2014

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

View 12 Replies View Related

Loop To Run Through Columns

Apr 15, 2008

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

View 14 Replies View Related

VBA - Loop Through Columns

May 12, 2011

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


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.

View 7 Replies View Related

Loop On Two Columns?

Apr 5, 2013

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


View 4 Replies View Related

Loop Columns

Aug 21, 2009

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.

View 9 Replies View Related

Loop Through Columns

May 3, 2007

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
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
Start_Cell = ActiveCell.Row + 1.....................

View 2 Replies View Related

How To Create Loop Through Among Columns

Apr 30, 2012

i would like to create loop throug among columns, is xlup or xldown methods will make sensne ?

View 2 Replies View Related

Loop Through Columns And Sum Them At Bottom

Nov 17, 2008

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

View 9 Replies View Related

Loop Through All Columns Of A Worksheet

Nov 22, 2006

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

View 3 Replies View Related

Loop Through All Columns In All Sheets

Nov 28, 2006

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

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

View 6 Replies View Related

Loop To Add+1 To A Number

Jun 6, 2006

What I want is a button that will add 1 to a number everytime it is pressed.

View 9 Replies View Related

Concatenate With Loop Through 2 Columns Of Information

May 28, 2014

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.


View 12 Replies View Related

Running A Loop On A Few Columns Simultaneously

Feb 13, 2010

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

View 14 Replies View Related

Select Columns To Merge Within For Loop

Nov 18, 2011

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

View 1 Replies View Related

How To Loop Through Specific Columns And Rows

Mar 25, 2014

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

View 9 Replies View Related

Loop Copy Columns Action

Sep 6, 2009

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("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False....................

View 9 Replies View Related

Loop (Number Count)

Jun 17, 2009

I have a have a userform that uses a macro to save data to a worksheet and i want to display the row number using a loop ie if column B3 is not empty/contains a values add a number 1 in column A3 and so on everytime a record is added.

View 14 Replies View Related

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