help with loops
 

I have a spreadsheet as follows:

Sheet1.Range(A1:A..nth) may or may not have values
Sheet2 same range may or may not have values

needs to check sheet1 and sheet2 for possible values in cells A1:A..nth

if sheet2 A1 has data, copy it then go to next step
if sheet1 A1 is null select it, and paste data otherwise continue one cell
down until empty cell is found then paste data

continue through sheet2 A1:A..nth until all cells are copied into sheet1
column A

sheet 2 gets over written each month. I am looking for code which will copy
cell contents for the purpose of keeping a history

any help is greatly appreciated

-rick

help with loops
 

Hi,

I hope this works...

P.S. : not tested


Sub copyRange()

Dim v As Variant
Dim i As Integer
Dim intRow As Integer

intRow = 1
Do
intRow = intRow + 1
Loop Until Sheets(1).Cells(intRow, 1) = ""

v = Sheets(2).Range("a1:a100")

For i = LBound(v) To UBound(v)

If Not IsNull(v(i, 1)) Then
Sheets(1).Cells(intRow, 1) = v(i, 1)
intRow = intRow + 1
End If

Next


End Su

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

help with loops
 

This works excellent...thank you very much......I do have one slight problem
however, if instead of the range A1:A..nth as I stated in the
problem.....how would this be written if the range in both worksheets is
C50:C70.

I am having a hard time following your logic.

Thank you

-rick

"tolgag " wrote in message
...
Hi,

I hope this works...

P.S. : not tested


Sub copyRange()

Dim v As Variant
Dim i As Integer
Dim intRow As Integer

intRow = 1
Do
intRow = intRow + 1
Loop Until Sheets(1).Cells(intRow, 1) = ""

v = Sheets(2).Range("a1:a100")

For i = LBound(v) To UBound(v)

If Not IsNull(v(i, 1)) Then
Sheets(1).Cells(intRow, 1) = v(i, 1)
intRow = intRow + 1
End If

Next


End Sub


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

help with loops
 

Hi,

it's quita a simple chage :

I think the first setting is clear (v = Sheets(2).Range("a1:a100"))
Just change it to v = Sheets(2).Range("c20:c50")

The variable intRow is the cell, where it starts to look for the nex
empty cell and as you see, it starts at 1. All you have to do is, star
searching at 20th cell

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

help with loops
 

That helped. This is what I have:

Sub copyRange()

Dim v As Variant
Dim i As Integer
Dim intRow As Integer

intRow = 49
Do
intRow = intRow + 1
Loop Until Sheets(1).Cells(intRow, 1) = ""

v = Sheets(2).Range("C50:C70")

For i = LBound(v) To UBound(v)

If Not IsNull(v(i, 1)) Then
Sheets(2).Cells(intRow, 3) = v(i, 1)
intRow = intRow + 1
End If

Next

End Sub

However, the problem is that it runs flawlessly on the first execution,
however, on the secong, it rewrites over the same cells on Sheet1 instead of
finding the first empty cell. Not sure what I am doing wrong.

You have been extremely helpful

Thanks in advance.

-rick


"tolgag " wrote in message
...
Hi,

it's quita a simple chage :

I think the first setting is clear (v = Sheets(2).Range("a1:a100"))
Just change it to v = Sheets(2).Range("c20:c50")

The variable intRow is the cell, where it starts to look for the next
empty cell and as you see, it starts at 1. All you have to do is, start
searching at 20th cell.


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

help with loops
 

Hi,

Sheets(2).Cells(intRow, 3) = v(i, 1) should look like :
Sheets(1).Cells(intRow, 3) = v(i, 1)


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

help with loops
 

I have removed the do loop & replaced it with a single command to find
the nextr blank cell on sheet 1 column c


Sub copyRange()

Dim v As Variant
Dim i As Integer
Dim intRow As Integer

v = Sheets(2).Range("C50:C70")

For i = LBound(v) To UBound(v)

If Not IsNull(v(i, 1)) Then
intRow = Sheets(1).Range("c65536").End(xlUp).Row + 1
Sheets(1).Cells(intRow, 3) = v(i, 1)
intRow = intRow + 1
End If

Next

End Sub


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

help with loops
 

Any thoughts on what could be contained in a worksheet that would make this
true:

IsNull(v(i, 1))

--
Regards,
Tom Ogilvy

mudraker wrote in message
...
I have removed the do loop & replaced it with a single command to find
the nextr blank cell on sheet 1 column c


Sub copyRange()

Dim v As Variant
Dim i As Integer
Dim intRow As Integer

v = Sheets(2).Range("C50:C70")

For i = LBound(v) To UBound(v)

If Not IsNull(v(i, 1)) Then
intRow = Sheets(1).Range("c65536").End(xlUp).Row + 1
Sheets(1).Cells(intRow, 3) = v(i, 1)
intRow = intRow + 1
End If

Next

End Sub


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

help with loops
 

Times up <g

use isempty to test if a cell is empty or ="" or len(trim(cell)) = 0

--
Regards,
Tom Ogilvy



Tom Ogilvy wrote in message
...
Any thoughts on what could be contained in a worksheet that would make
this
true:

IsNull(v(i, 1))

--
Regards,
Tom Ogilvy

mudraker wrote in message
...
I have removed the do loop & replaced it with a single command to find
the nextr blank cell on sheet 1 column c


Sub copyRange()

Dim v As Variant
Dim i As Integer
Dim intRow As Integer

v = Sheets(2).Range("C50:C70")

For i = LBound(v) To UBound(v)

If Not IsNull(v(i, 1)) Then
intRow = Sheets(1).Range("c65536").End(xlUp).Row + 1
Sheets(1).Cells(intRow, 3) = v(i, 1)
intRow = intRow + 1
End If

Next

End Sub


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