Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |