If Then VBA statement

[Guest]

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

 

[Guest]

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

 

[PCLIVE]

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

"(e-mail address removed)"

 

[Guest]

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

 

[Guest]

Thanks a lot for your help guys

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

"(e-mail address removed)"