VBA- Skipping word in column of data and displaying result in other sheet

[noviceuser90]

Hello,

So I'm stuck with this problem. I have a column of data that says "yes" or "no". I want to get this data ignoring the yes column and putting this new column into another sheet. With no blank cells. I'm using excel 2010. I want a VBA code since I want this to be automated. A code that would do this in the same sheet would be helpful as well.

example:

Column A (in sheet 1)

yes
no
yes
yes
no

Column B (in sheet 2)

no
no


Thanks!

[ashleyuk1984]

Please can you explain again more thoroughly, I'm having a hard time understanding what you want.

[noviceuser90]

I have column A and I want column B in another sheet of the same workbook. Column B would only have the no's from column A.

[ashleyuk1984]

I want to understand the problem fully before I start coding.

So, on sheet1, in column A you have data that looks like this:

yes
no
yes
yes
yes
no
no
yes

You want to take all of the "no" values and place them onto sheet2, in column B ?

So then, Sheet1, column A would look like this

yes
yes
yes
yes
yes

and Sheet2, column B would look like this

no
no
no

Is this correct ????

[noviceuser90]

Almost correct. The original data in Sheet1, column A will stay the same.

I just want the no's in Sheet2, column B.

Thanks!

[ashleyuk1984]

Here you go. This should do the trick.
Everyone, please excuse my VBA programming, I'm still learning.

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("A" & i).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

[noviceuser90]

You're awesome!!! This works! Thanks so much!! I was beating my head over this.

[noviceuser90]

Another quick question, what could I add to the code if I want to add a value that matches the new no column?

For example:

Sheet1, column A

yes
yes
no
no

Sheet1, column C
cat
cat
dog
dog

I want sheet2, column B to read:

dog
dog

These match the no values of column A of sheet1

Thanks for your help!

[ashleyuk1984]

Based on that, you will need to edit the code to read like this:
Change this one line from:

Range("A" & i).Copy

to

Range("C" & i).Copy

[noviceuser90]

Hey, sorry, I added one line to your code and I got it.
Is there a way to copy and paste multiple values like dog in column C and another dog in column D in the same code?

[noviceuser90]

I think I did the exact same thing as you last posted, I think the copy and paste to this website messed up your text.
Clearing up my other question, is there a way to copy and paste multiple values like dog in column C and another word, let's say fish, in column D in the same code?

Sheet1, column A

yes
yes
no
no

Sheet1, column C
cat
cat
dog
dog

Sheet 1 column D

cat
cat
fish
fish

I want sheet2, column B to read:

dog
dog

and also in sheet2, column C to read:

fish
fish

Thanks a lot!

[ashleyuk1984]

So you want dog in columns B, C & D on sheet2 ?
If that's the correct, then you'll want something like this.

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).Copy
            Sheets("Sheet2").Select
            If Range("B1").Value = "" Then
                Range("B1").Select
                Range(ActiveCell, ActiveCell.Offset(0, 2)).PasteSpecial (xlPasteValues)
                Sheets("Sheet1").Select
            Else
                Range("B100000").End(xlUp).Offset(1, 0).Select
                Range(ActiveCell, ActiveCell.Offset(0, 2)).PasteSpecial (xlPasteValues)
                Sheets("Sheet1").Select
            End If
        End If
         
    Next i
     
     
End Sub

[noviceuser90]

Hi, no this isn't what I want, sorry for the confusion, please look at my previous post, I'll copy it here again for clarity:

Sheet1, column A

yes
yes
no
no

Sheet1, column C
cat
cat
dog
dog

Sheet 1 column D

cat
cat
fish
fish

I want sheet2, column B to read:

dog
dog

and also in sheet2, column C to read:

fish
fish

Thanks a lot!

[ashleyuk1984]

Not a problem.

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

[noviceuser90]

Sorry for bothering. In the case that I would want to skip over a column for the offset. In this case I want the fish value in column F instead of D. I tried modifying the offset code but it selects all values in D, E and F columns.

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

Thanks!

[salmogomes]

Hello noviceuser90, see if that's what you want:
[vba]Sub MoveValues()

Count = 1
Count2 = 1

For i = 1 To 500

If Cells(i, 1) = "" Then Exit For

If Cells(i, 1) = "no" And Cells(i, 3) = "dog" Then

Sheets(2).Cells(Count, 2) = Cells(i, 3)

Count = Count + 1

End If

If Cells(i, 6) = "fish" Then

Sheets(2).Cells(Count2, 3) = Cells(i, 6)

Count2 = Count2 + 1

End If

Next i

End Sub
[/vba]

[noviceuser90]

Salmogomes--Hmm, actually, the dog and fish words are arbitrary to show an example in this post, the words will vary, these could be sentences as well so in this case you're looking at cell values not specific words in the if statements.

[salmogomes]

In that case, you can put the words or sentences you want in another sheet. I used "Sheet3" in the code.

The variables:

"k" for "no", "l" for "dog" and "m" for "fish".

In Sheet3, I put the words:

A1 B1 C1
no dog fish

My point is, you can change these words as you need. See this:

[vba]
Sub MoveValues()

Count = 1
Count2 = 1

For i = 1 To 500

If Cells(i, 1) = "" Then Exit For

For k = 1 To 100

If Sheets(3).Cells(k, 1) = "" Then Exit For

If Cells(i, 1) = Sheets(3).Cells(k, 1) Then Exit For

Next k

For l = 1 To 100

If Sheets(3).Cells(l, 2) = "" Then Exit For

If Cells(i, 3) = Sheets(3).Cells(l, 2) Then Exit For

Next l

If Cells(i, 1) = Sheets(3).Cells(k, 1) And Cells(i, 3) = Sheets(3).Cells(l, 2) Then

Sheets(2).Cells(Count, 2) = Cells(i, 3)

Count = Count + 1

End If

For m = 1 To 100

If Sheets(3).Cells(m, 3) = "" Then Exit For

If Cells(i, 6) = Sheets(3).Cells(m, 3) Then Exit For

Next m

If Cells(i, 6) = Sheets(3).Cells(m, 3) Then

Sheets(2).Cells(Count2, 3) = Cells(i, 6)

Count2 = Count2 + 1

End If

Next i

End Sub[/vba]

[noviceuser90]

The words won't be specific words, it will random words anyone will input. So I can't have the code be limited to specific words or sentences.

[ashleyuk1984]

Can't you just provide your FULL question ???

It becomes very tiresome for a coder to provide somebody with a piece of code which does exactly what is asked of it, but then the question changed, so then we have to change the code, but then question is changed again... and again and again.
Can you understand my point?