help with loops

  • Thread starter Thread starter Rick B
  • Start date Start date
R

Rick B

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
 
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
 
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
 
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
 
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
 
Hi,

Sheets(2).Cells(intRow, 3) = v(i, 1) should look like :
Sheets(1).Cells(intRow, 3) = v(i, 1)
 
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
 
Any thoughts on what could be contained in a worksheet that would make this
true:

IsNull(v(i, 1))
 
Times up <g>

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

--
Regards,
Tom Ogilvy



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

IsNull(v(i, 1))
Click to expand...
 
Back
Top