How to skip empty cells while running a macro?
 

I want my macro to skip an empty cell and go to next if there is n
(numerical) data in the cell. Can you Please tell me how can I includ
this in my macro.
I am want my macro to be precise with least amount of lines.
Here is what I am doing:
select cell
if value is < than say 5
select 4 cells to its left copy (I am giving relative cell address)
goto another sheet,
select a range
insert copied cells
goback to sheet
goto next cell ( down)
do the same
if no data stop macro

Thank you for helping

--
Message posted from http://www.ExcelForum.com

How to skip empty cells while running a macro?
 

Hi
not tested but try something like

sub foo()
dim rng as range
dim cell as range

set rng=activesheet.range("E1:E100")
for each cell in rng
if cell.value<"" and cell.value<5 then
cell.offset(0,-4).resize(1,4).value = _
worksheets("sheet2").range("A1:D1").value
end if
next
end sub


--
Regards
Frank Kabel
Frankfurt, Germany


I want my macro to skip an empty cell and go to next if there is no
(numerical) data in the cell. Can you Please tell me how can I
include this in my macro.
I am want my macro to be precise with least amount of lines.
Here is what I am doing:
select cell
if value is < than say 5
select 4 cells to its left copy (I am giving relative cell address)
goto another sheet,
select a range
insert copied cells
goback to sheet
goto next cell ( down)
do the same
if no data stop macro

Thank you for helping.


---
Message posted from http://www.ExcelForum.com/

How to skip empty cells while running a macro?
 

You do not have to go back and forth to do what you like. Something like

for each c in selection
if isnumber(c) and c<5 then c.offset(0-4) .copy sheets("sheet2").range("a1")
next

--
Don Guillett
SalesAid Software

"saziz " wrote in message
...
I want my macro to skip an empty cell and go to next if there is no
(numerical) data in the cell. Can you Please tell me how can I include
this in my macro.
I am want my macro to be precise with least amount of lines.
Here is what I am doing:
select cell
if value is < than say 5
select 4 cells to its left copy (I am giving relative cell address)
goto another sheet,
select a range
insert copied cells
goback to sheet
goto next cell ( down)
do the same
if no data stop macro

Thank you for helping.


---
Message posted from http://www.ExcelForum.com/

How to skip empty cells while running a macro?
 

isnumber(c) should be isnumeric(c)

for excel 97, these should be on separate lines:

for each c in selection
if isnumeric(c) and not isempty(c) then
if c<5 then c.offset(0-4) .copy sheets("sheet2").range("a1")
End if
next

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
You do not have to go back and forth to do what you like. Something like

for each c in selection
if isnumber(c) and c<5 then c.offset(0-4) .copy
sheets("sheet2").range("a1")
next

--
Don Guillett
SalesAid Software

"saziz " wrote in message
...
I want my macro to skip an empty cell and go to next if there is no
(numerical) data in the cell. Can you Please tell me how can I include
this in my macro.
I am want my macro to be precise with least amount of lines.
Here is what I am doing:
select cell
if value is < than say 5
select 4 cells to its left copy (I am giving relative cell address)
goto another sheet,
select a range
insert copied cells
goback to sheet
goto next cell ( down)
do the same
if no data stop macro

Thank you for helping.


---
Message posted from http://www.ExcelForum.com/

How to skip empty cells while running a macro?
 

Thanks for the correction Tom.

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
isnumber(c) should be isnumeric(c)

for excel 97, these should be on separate lines:

for each c in selection
if isnumeric(c) and not isempty(c) then
if c<5 then c.offset(0-4) .copy sheets("sheet2").range("a1")
End if
next

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
You do not have to go back and forth to do what you like. Something like

for each c in selection
if isnumber(c) and c<5 then c.offset(0-4) .copy
sheets("sheet2").range("a1")
next

--
Don Guillett
SalesAid Software

"saziz " wrote in message
...
I want my macro to skip an empty cell and go to next if there is no
(numerical) data in the cell. Can you Please tell me how can I
include
this in my macro.
I am want my macro to be precise with least amount of lines.
Here is what I am doing:
select cell
if value is < than say 5
select 4 cells to its left copy (I am giving relative cell address)
goto another sheet,
select a range
insert copied cells
goback to sheet
goto next cell ( down)
do the same
if no data stop macro

Thank you for helping.


---
Message posted from http://www.ExcelForum.com/

How to skip empty cells while running a macro?
 

Hello to all guys n gals,
Thank you, it worked. All of you suggested almost same script.
Sazi

--
Message posted from http://www.ExcelForum.com