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

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" <(E-Mail Removed)> wrote in message
news:54738677-5BAE-47BF-8B28-(E-Mail Removed)...
>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

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

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
(E-Mail Removed)
"BorisS" <(E-Mail Removed)> wrote in message
news:54738677-5BAE-47BF-8B28-(E-Mail Removed)...
>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

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" <(E-Mail Removed)> wrote in message
> news:54738677-5BAE-47BF-8B28-(E-Mail Removed)...
> >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
>
>
>

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

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" <(E-Mail Removed)> wrote in message
news:03E782F6-9909-4C9B-8E7A-(E-Mail Removed)...
> 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

Have you tried looking in the vba help index for RESIZE, especially, RESIZE
PROPERTY
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"BorisS" <(E-Mail Removed)> wrote in message
news:03E782F6-9909-4C9B-8E7A-(E-Mail Removed)...
> 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