Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then statement
I'm not familiar with if then statements, although I've heard of them so I'm
thinking that is what I need for this situation. What I need to do is copy the text from the active sheet called "Data Input". I need to copy the cells from Rows 19 - 57, Col A, B and D, only if there is text in column B and copy it to a new workbook titled "Pending and Short", to sheet called "Pending". It would copied to the next available row where Col B is empty. I know I need to concatenate the column text into Col B and not sure of that either. Thanks for your help in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then statement
assumes the text values in column B are constants and not produced by
formulas. Sub copydata() Dim rng as Range, rng1 as Range, rng2 as Range Dim rng3 as Range with worksheets("Data Input") set rng = .Range("B19:57").SpecialCells(xlConstants,xlTextVa lues) if not rng is nothing then set rng1 = Intersect(rng.entireRow,.Columns(1).Resize(,2)) set rng2 = Intersect(rng.entireRow,.Columns(4)) else exit sub end if end with set rng3 = workbooks("Pending and Short.xls") _ .Worksheets("Pending").Cells(rows.count,2).End(xlu p)(2) rng1.copy rng3.offset(0,-1) rng2.copy rng3.offset(0,2) -- Regards, Tom Ogilvy "SITCFanTN" wrote: I'm not familiar with if then statements, although I've heard of them so I'm thinking that is what I need for this situation. What I need to do is copy the text from the active sheet called "Data Input". I need to copy the cells from Rows 19 - 57, Col A, B and D, only if there is text in column B and copy it to a new workbook titled "Pending and Short", to sheet called "Pending". It would copied to the next available row where Col B is empty. I know I need to concatenate the column text into Col B and not sure of that either. Thanks for your help in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then statement
Hi Tom,
I can't get the macro to work and I'm wondering if it is because Col A is text, Col B is currency and Col D is a date? Also I added the location of the new worksheet which is located on my C drive so here is what I've done: Can you see what I'm doing wrong? Thanks for your help. Sub copydata() Dim rng As Range, rng1 As Range, rng2 As Range Dim rng3 As Range With Worksheets("Data Input") Set rng = .Range("B19:57").SpecialCells(xlConstants, xlTextValues) If Not rng Is Nothing Then Set rng1 = Intersect(rng.EntireRow, .Columns(1).Resize(, 2)) Set rng2 = Intersect(rng.EntireRow, .Columns(4)) Else Exit Sub End If End With Set rng3 = Workbooks(C:\Documents and Settings\jsmith\Desktop\Joyce Smith\0-Production File\Balancing\Pending and Short Log\Pending and Short.xls") _ .Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) rng1.Copy rng3.Offset(0, -1) rng2.Copy rng3.Offset(0, 2) End Sub "Tom Ogilvy" wrote: assumes the text values in column B are constants and not produced by formulas. Sub copydata() Dim rng as Range, rng1 as Range, rng2 as Range Dim rng3 as Range with worksheets("Data Input") set rng = .Range("B19:57").SpecialCells(xlConstants,xlTextVa lues) if not rng is nothing then set rng1 = Intersect(rng.entireRow,.Columns(1).Resize(,2)) set rng2 = Intersect(rng.entireRow,.Columns(4)) else exit sub end if end with set rng3 = workbooks("Pending and Short.xls") _ .Worksheets("Pending").Cells(rows.count,2).End(xlu p)(2) rng1.copy rng3.offset(0,-1) rng2.copy rng3.offset(0,2) -- Regards, Tom Ogilvy "SITCFanTN" wrote: I'm not familiar with if then statements, although I've heard of them so I'm thinking that is what I need for this situation. What I need to do is copy the text from the active sheet called "Data Input". I need to copy the cells from Rows 19 - 57, Col A, B and D, only if there is text in column B and copy it to a new workbook titled "Pending and Short", to sheet called "Pending". It would copied to the next available row where Col B is empty. I know I need to concatenate the column text into Col B and not sure of that either. Thanks for your help in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then statement
you can't copy to a closed workbook
instead of Set rng3 = Workbooks(C:\Documents and Settings\jsmith\Desktop\Joyce Smith\0-Production File\Balancing\Pending and Short Log\Pending and Short.xls") _ .Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) try Dim bk as Workbook set bk = Workbooks.Open(C:\Documents and Settings\" & _ "jsmith\Desktop\Joyce Smith\0-Production File" & _ "\Balancing\Pending and Short Log\Pending and Short.xls") set rng3 = bk.Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) -- Regards, Tom Ogilvy "SITCFanTN" wrote in message ... Hi Tom, I can't get the macro to work and I'm wondering if it is because Col A is text, Col B is currency and Col D is a date? Also I added the location of the new worksheet which is located on my C drive so here is what I've done: Can you see what I'm doing wrong? Thanks for your help. Sub copydata() Dim rng As Range, rng1 As Range, rng2 As Range Dim rng3 As Range With Worksheets("Data Input") Set rng = .Range("B19:57").SpecialCells(xlConstants, xlTextValues) If Not rng Is Nothing Then Set rng1 = Intersect(rng.EntireRow, .Columns(1).Resize(, 2)) Set rng2 = Intersect(rng.EntireRow, .Columns(4)) Else Exit Sub End If End With Set rng3 = Workbooks(C:\Documents and Settings\jsmith\Desktop\Joyce Smith\0-Production File\Balancing\Pending and Short Log\Pending and Short.xls") _ .Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) rng1.Copy rng3.Offset(0, -1) rng2.Copy rng3.Offset(0, 2) End Sub "Tom Ogilvy" wrote: assumes the text values in column B are constants and not produced by formulas. Sub copydata() Dim rng as Range, rng1 as Range, rng2 as Range Dim rng3 as Range with worksheets("Data Input") set rng = .Range("B19:57").SpecialCells(xlConstants,xlTextVa lues) if not rng is nothing then set rng1 = Intersect(rng.entireRow,.Columns(1).Resize(,2)) set rng2 = Intersect(rng.entireRow,.Columns(4)) else exit sub end if end with set rng3 = workbooks("Pending and Short.xls") _ .Worksheets("Pending").Cells(rows.count,2).End(xlu p)(2) rng1.copy rng3.offset(0,-1) rng2.copy rng3.offset(0,2) -- Regards, Tom Ogilvy "SITCFanTN" wrote: I'm not familiar with if then statements, although I've heard of them so I'm thinking that is what I need for this situation. What I need to do is copy the text from the active sheet called "Data Input". I need to copy the cells from Rows 19 - 57, Col A, B and D, only if there is text in column B and copy it to a new workbook titled "Pending and Short", to sheet called "Pending". It would copied to the next available row where Col B is empty. I know I need to concatenate the column text into Col B and not sure of that either. Thanks for your help in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If then statement
Hi Tom,
I"ve replaced the text that you mentioned and open the workbook first but now I get a 1004 run time error. I thought I got the error because on rows 32 and 45 the cells A-D were mergered so I changed the criteria to 20 - 31 and it still doesn't run through. I'm not sure what is wrong. Knowing that I have a few merged cells, I need to run for ranges ("20:31"), ("33:44") and ("46:57"). Why would I be getting that error and how would I code for the 3 sets of ranges. Thanks again for your help. Set rng = .Range("B20:31").SpecialCells(xlConstants, xlTextValues) Sub CopyDataToPendAndShortLog() Dim rng As Range, rng1 As Range, rng2 As Range Dim rng3 As Range With Worksheets("Data Input") Set rng = .Range("B20:31").SpecialCells(xlConstants, xlTextValues) If Not rng Is Nothing Then Set rng1 = Intersect(rng.EntireRow, .Columns(1).Resize(, 2)) Set rng2 = Intersect(rng.EntireRow, .Columns(4)) Else Exit Sub End If End With Dim bk As Workbook Set rng3 = Workbooks("C:\Documents and Settings\Joyce Smith\Desktop\Joyce Smith\0-Production File\Balancing\Assets Daily Balancing\Pending and Short Log.xls").Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) rng1.Copy rng3.Offset(0, -1) rng2.Copy rng3.Offset(0, 2) End Sub "Tom Ogilvy" wrote: you can't copy to a closed workbook instead of Set rng3 = Workbooks(C:\Documents and Settings\jsmith\Desktop\Joyce Smith\0-Production File\Balancing\Pending and Short Log\Pending and Short.xls") _ .Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) try Dim bk as Workbook set bk = Workbooks.Open(C:\Documents and Settings\" & _ "jsmith\Desktop\Joyce Smith\0-Production File" & _ "\Balancing\Pending and Short Log\Pending and Short.xls") set rng3 = bk.Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) -- Regards, Tom Ogilvy "SITCFanTN" wrote in message ... Hi Tom, I can't get the macro to work and I'm wondering if it is because Col A is text, Col B is currency and Col D is a date? Also I added the location of the new worksheet which is located on my C drive so here is what I've done: Can you see what I'm doing wrong? Thanks for your help. Sub copydata() Dim rng As Range, rng1 As Range, rng2 As Range Dim rng3 As Range With Worksheets("Data Input") Set rng = .Range("B19:57").SpecialCells(xlConstants, xlTextValues) If Not rng Is Nothing Then Set rng1 = Intersect(rng.EntireRow, .Columns(1).Resize(, 2)) Set rng2 = Intersect(rng.EntireRow, .Columns(4)) Else Exit Sub End If End With Set rng3 = Workbooks(C:\Documents and Settings\jsmith\Desktop\Joyce Smith\0-Production File\Balancing\Pending and Short Log\Pending and Short.xls") _ .Worksheets("Pending").Cells(Rows.Count, 2).End(xlUp)(2) rng1.Copy rng3.Offset(0, -1) rng2.Copy rng3.Offset(0, 2) End Sub "Tom Ogilvy" wrote: assumes the text values in column B are constants and not produced by formulas. Sub copydata() Dim rng as Range, rng1 as Range, rng2 as Range Dim rng3 as Range with worksheets("Data Input") set rng = .Range("B19:57").SpecialCells(xlConstants,xlTextVa lues) if not rng is nothing then set rng1 = Intersect(rng.entireRow,.Columns(1).Resize(,2)) set rng2 = Intersect(rng.entireRow,.Columns(4)) else exit sub end if end with set rng3 = workbooks("Pending and Short.xls") _ .Worksheets("Pending").Cells(rows.count,2).End(xlu p)(2) rng1.copy rng3.offset(0,-1) rng2.copy rng3.offset(0,2) -- Regards, Tom Ogilvy "SITCFanTN" wrote: I'm not familiar with if then statements, although I've heard of them so I'm thinking that is what I need for this situation. What I need to do is copy the text from the active sheet called "Data Input". I need to copy the cells from Rows 19 - 57, Col A, B and D, only if there is text in column B and copy it to a new workbook titled "Pending and Short", to sheet called "Pending". It would copied to the next available row where Col B is empty. I know I need to concatenate the column text into Col B and not sure of that either. Thanks for your help in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |