Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
I need some code to do the following:
1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
With Activecell
For i = 1 To 10 If .Offset(0,1).Value < "" Then 'call procedure End If Next i End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BorisS" wrote in message ... I need some code to do the following: 1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
Dim rng as Range, cell as Range, cnt as Long
set rng = activecell.offset(0,1).Resize(1,10) cnt = application.countA(rng) for each cell in rng if len(trim(cell.value)) 0 then cell.Select myprocedure end if Next -- Regards, Tom Ogilvy Assumes your procedure acts on the active cell. or just write your code to work with the range reference: cell (and skip the select command) -- Regards, Tom Ogilvy "BorisS" wrote: I need some code to do the following: 1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
Maybe this will help.
Sub cellstoright() Set mr = ActiveCell.Resize(1, 10) For Each c In mr 'If c < "" Then MsgBox c.Address If c < "" Then c.value=c*10 Next c End Sub -- Don Guillett SalesAid Software "BorisS" wrote in message ... I need some code to do the following: 1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
typo:
With Activecell For i = 1 To 10 If .Offset(0,i).Value < "" Then '<== change here 'call procedure End If Next i End With "Bob Phillips" wrote: With Activecell For i = 1 To 10 If .Offset(0,1).Value < "" Then 'call procedure End If Next i End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BorisS" wrote in message ... I need some code to do the following: 1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
what does the resize part do, just for my education?
Thx for the help to all. -- Boris "Tom Ogilvy" wrote: Dim rng as Range, cell as Range, cnt as Long set rng = activecell.offset(0,1).Resize(1,10) cnt = application.countA(rng) for each cell in rng if len(trim(cell.value)) 0 then cell.Select myprocedure end if Next -- Regards, Tom Ogilvy Assumes your procedure acts on the active cell. or just write your code to work with the range reference: cell (and skip the select command) -- Regards, Tom Ogilvy "BorisS" wrote: I need some code to do the following: 1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for counting/shifting
It resizes the range object to that number of rows and columns.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "BorisS" wrote in message ... what does the resize part do, just for my education? Thx for the help to all. -- Boris "Tom Ogilvy" wrote: Dim rng as Range, cell as Range, cnt as Long set rng = activecell.offset(0,1).Resize(1,10) cnt = application.countA(rng) for each cell in rng if len(trim(cell.value)) 0 then cell.Select myprocedure end if Next -- Regards, Tom Ogilvy Assumes your procedure acts on the active cell. or just write your code to work with the range reference: cell (and skip the select command) -- Regards, Tom Ogilvy "BorisS" wrote: I need some code to do the following: 1) start with a given cell 2) count how many of the 10 cells to the right of it are filled with data 3) for each of those cells, perform a small procedure My hangup right now is how to get the loop structured, as I always screw up the definitions of variables, as well as how to use them in code. So if anyone can give me the first part of this, I can fill in the procedure inbetween the loop beginning and ending arguments. Thx. -- Boris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for counting the order cells are filled | Excel Discussion (Misc queries) | |||
Simple code for shifting | Excel Discussion (Misc queries) | |||
Counting entries by color code | Excel Discussion (Misc queries) | |||
Counting Code | Excel Programming | |||
Counting names thru code | Excel Programming |