Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then VBA statement
I have a data set, 5 columns by 18 rows, M6:Q24. At the top of each row is a
True-False statement. I want to create a function that looks at each column, and if the true-false statment says true will paste special the values into columns C:G, respectively, in the same rows. If the statment says false, the function will do nothing. Any ideas? Thanks Adam Bush |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then VBA statement
Is the true false in row 1?
Sub copyColumns() Dim i As Long For i = 3 To 7 If Cells(1, i + 10) = True Then Cells(6, i + 10).Resize(19, 1).Copy Cells(6, i) End If Next End Sub if your true false values are in row 6 Sub copyColumns() Dim i As Long For i = 3 To 7 If Cells(6, i + 10) = True Then Cells(6, i + 10).Resize(19, 1).Copy Cells(6, i) End If Next End Sub if your true false values are in row 6 and you want to copy rows 7:24 Sub copyColumns() Dim i As Long For i = 3 To 7 If Cells(6, i + 10) = True Then Cells(7, i + 10).Resize(18, 1).Copy Cells(7, i) End If Next End Sub -- Regards, Tom Ogilvy " wrote: I have a data set, 5 columns by 18 rows, M6:Q24. At the top of each row is a True-False statement. I want to create a function that looks at each column, and if the true-false statment says true will paste special the values into columns C:G, respectively, in the same rows. If the statment says false, the function will do nothing. Any ideas? Thanks Adam Bush |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then VBA statement
If you wanted to use formulas, you could use this in C6 and just copy it
over and down. =IF(M6="","",IF(M$5=TRUE,M6,"")) Through VBA, here's one way assuming your True/False values are in row 5: For Each cell In Range("M5:Q5") If cell.Value = True Then Range(cell.Offset(1, -10), cell.Offset(19, -10)).Value = _ Range(cell.Offset(1, 0), cell.Offset(19, 0)).Value Next cell HTH, Paul " m wrote in message ... I have a data set, 5 columns by 18 rows, M6:Q24. At the top of each row is a True-False statement. I want to create a function that looks at each column, and if the true-false statment says true will paste special the values into columns C:G, respectively, in the same rows. If the statment says false, the function will do nothing. Any ideas? Thanks Adam Bush |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then VBA statement
Sub test()
Range("N5").Activate For x = 1 to 5 ActiveCell.Offset(0,x) If ActiveCell.Value = "True" Then Range(ActiveCell.Offset(1,0),ActiveCell.Offset(1,0 ).End(xlDown)).Copy Range("B5").Offset(0,x).PasteSpecial (xlPasteValues) End If Next x End Sub " wrote: I have a data set, 5 columns by 18 rows, M6:Q24. At the top of each row is a True-False statement. I want to create a function that looks at each column, and if the true-false statment says true will paste special the values into columns C:G, respectively, in the same rows. If the statment says false, the function will do nothing. Any ideas? Thanks Adam Bush |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then VBA statement
Thanks a lot for your help guys
"PCLIVE" wrote: If you wanted to use formulas, you could use this in C6 and just copy it over and down. =IF(M6="","",IF(M$5=TRUE,M6,"")) Through VBA, here's one way assuming your True/False values are in row 5: For Each cell In Range("M5:Q5") If cell.Value = True Then Range(cell.Offset(1, -10), cell.Offset(19, -10)).Value = _ Range(cell.Offset(1, 0), cell.Offset(19, 0)).Value Next cell HTH, Paul " m wrote in message ... I have a data set, 5 columns by 18 rows, M6:Q24. At the top of each row is a True-False statement. I want to create a function that looks at each column, and if the true-false statment says true will paste special the values into columns C:G, respectively, in the same rows. If the statment says false, the function will do nothing. Any ideas? Thanks Adam Bush |
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) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions |