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

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

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

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

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