How do you loop through columns

**fredcho** · 01-29-2013, 09:47 PM

I'm new to to vbs script in excel, and i'm stuck on looping through column function. I have the function below it does what I want but it stop after one iteration. I want it to loop through entire range A1 until the cell is blank. Range A1 contain given numbers, and functions below that does the sum until it hit 100; once it get 100 total. The paste of functions right next to the cell of 100 continue until the end of range A1. The number of sum equal 100 is varies between 4 column and 6 column.

once the sum of cell is 100, the adjacent cell is blank even with formula exist. So, I'm checking for blank cell on A2 range to repeat the process of pasting, rather than the value 100.

Please Login or Register  to view this content.

============================================
A B C D E F G H I J K
1 10 10 10 40 30 10 20 30 40 10 15
2 10 20 30 70 100 10 30 60 100 10 25
3 # # # # # #
4 # # # # # #
5 # # # # # #
6
7
8
9 Reference Range of Formula to copy and paste
10 10 10 10 40 30
11 10 20 30 70 100
12 # # # # # #
13 # # # # # #
14
15

Thanks,
Fred

Moderators Note:

Please follow Forum Rule #3 and use code tags.
Added for you this time, but please use them in the future…Thanks.

**patel45** · 01-30-2013, 04:13 AM

you say "I want it to loop through entire range A1" but A1 i only a cell, how can you loop through a single cell ?
You say "Range A1 contain given numbers" but one cel can contain only one number
explain better your goal, attach a sample file with current and desired table

**fredcho** · 01-30-2013, 07:23 AM

that explained why i'm stucked, I've no clue.

row 1 is given numbers.
row 2 column 1 is blank then copy the formula from given range. Then scan again for blank cell on row2 for blank cell and repeat pasting process until the end, where cell in row1 is blank (end of given numbers).

**JOHN H. DAVIS** · 01-30-2013, 09:36 AM

This will loop through cells in row1 and place the word help. Maybe you can apply the idea to your code.

Please Login or Register  to view this content.

**fredcho** · 01-30-2013, 06:37 PM

Originally Posted by JOHN H. DAVIS

This will loop through cells in row1 and place the word help. Maybe you can apply the idea to your code.

Please Login or Register  to view this content.

I'm still cluless of what is cell and what's Range, but combine John's Code with mine in previous posted it does what I want:
[code
sub TryMe()
Dim Rng As Range
Dim x As Long
For x = 1 To ActiveSheet.UsedRange.Columns.Count
Set Rng = ActiveSheet.Range(Cells(2, x), Cells(2, x))
If WorksheetFunction.CountA(Rng) = 0 Then
If ActiveSheet.Range(Cells(1, x), Cells(1, x)) <> "" Then
ActiveSheet.Range("A16:F20").Copy Destination:=Rng
End If
End If
Next x
End Sub
[/code]

**fredcho** · 01-30-2013, 07:14 PM

Now, I'm running into new issue. the formula has width up to 6 column, the script does copy every 6th columns until the end.

some of the sum equal zero on the 5th column, I need the script able to know that and start to paste on the 5th cell (the blank cell).

Please see attached.

Thanks,
Fred.

**fredcho** · 01-31-2013, 05:19 PM

Can someone help me correct statement below? I got compile error when run the code :

Working Statement: If ActiveSheet.Range(Cells(2, x), Cells(2, x)) = "" Then
Compile Error Statement: If ActiveSheet.Range(Cells(2, x), Cells(2, x)).Offset(0, -2) = "" Then

Please Login or Register  to view this content.

**fredcho** · 01-31-2013, 06:26 PM

Solved.

Working Code:

Please Login or Register  to view this content.

How do you loop through columns

LinkBack

Thread Tools

Rate This Thread

Display

How do you loop through columns

Re: How do you loop through columns

Re: How do you loop through columns

Re: How do you loop through columns

Re: How do you loop through columns

Re: How do you loop through columns

Re: How do you loop through columns

Re: How do you loop through columns

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions