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. :tease:


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

http://ultraimg.com/images/jbNIG.png

[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:
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

[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:


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

[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?

[noviceuser90]

Hi ashleyuk, sorry you're right, I just have been trying to do some work on my own and try to figure it out myself. The last question I asked you is all I need. I appreciate your help.

[salmogomes]

noviceuser90, you CAN change the words to the ones you want, even random ones. They just have to be in the right place. I modified my code so you can do this.

[Aussiebear]

G'day noviceuser90, Having read your posts, have you considered simply using a filter to display your intended results? It wont tie up your workbook resources as much and the variables (names to sort on) will be far easier to work with

[noviceuser90]

This is intended to be automated with the code and no manual use. Otherwise, I wouldn't have asked for VBA coding help.

[noviceuser90]

Salmogomes--How can I adjust the code for columns? is the code made for columns starting with A1, A2? My spreadsheet isn't as nice.

[noviceuser90]

Just reposting my previous post for further clarity. This the final product for what I want.

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]

Salmogomes--How can I adjust the code for columns? is the code made for columns starting with A1, A2? My spreadsheet isn't as nice.If you want to use your variables in columns instead of rows, exchange the letter for the number in the Cell arguments. Example:

Cells(i,1) would be changed to: Cells(1,i)

It'll be a lot easier if you attach your workbook, because I'm using mine and it may be a little different.