noviceuser90
09-03-2013, 10:55 AM
Hello,
So I gathered up this code with some help. I wanted a VBA code that would take the values in Sheet1 and put
them in Sheet 2 if Sheet1, column A values are "no" values, here's an example:
Sheet1, column A
yes
yes
no
no
Sheet1, column C
cat
cat
dog
dog
Sheet 1 column F
cat
cat
fish
fish
I want sheet2, column B to read:
dog
dog
and also in sheet2, column C to read:
fish
fish
The offset command in the code is taking all the values from column D through F and putting them in Sheet2
when I only want the values of column D and F only. The words dog,cat and fish are arbitrary, these values will
be random strings input by anyone, so these could be sentences and not just words of any length. So I guess we're
just looking at cell values. Any help appreciated.
Thanks!
Sub MoveNo()
Dim i As Integer
Sheets("Sheet1").Select
Range("A1").Select
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To LastRow
If Range("A" & i).Value = "no" Then
Range("C" & i).Select
Range(ActiveCell, ActiveCell.Offset(0, 1)).Copy
Sheets("Sheet2").Select
If Range("B1").Value = "" Then
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Else
Range("B100000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
End If
Next i
End Sub
So I gathered up this code with some help. I wanted a VBA code that would take the values in Sheet1 and put
them in Sheet 2 if Sheet1, column A values are "no" values, here's an example:
Sheet1, column A
yes
yes
no
no
Sheet1, column C
cat
cat
dog
dog
Sheet 1 column F
cat
cat
fish
fish
I want sheet2, column B to read:
dog
dog
and also in sheet2, column C to read:
fish
fish
The offset command in the code is taking all the values from column D through F and putting them in Sheet2
when I only want the values of column D and F only. The words dog,cat and fish are arbitrary, these values will
be random strings input by anyone, so these could be sentences and not just words of any length. So I guess we're
just looking at cell values. Any help appreciated.
Thanks!
Sub MoveNo()
Dim i As Integer
Sheets("Sheet1").Select
Range("A1").Select
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = 1 To LastRow
If Range("A" & i).Value = "no" Then
Range("C" & i).Select
Range(ActiveCell, ActiveCell.Offset(0, 1)).Copy
Sheets("Sheet2").Select
If Range("B1").Value = "" Then
Range("B1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Else
Range("B100000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
End If
Next i
End Sub