Excel-VB to copy/paste a row if Certain Text

August 18, 2011 at 12:05:22
Specs: Windows XP
Hi helpful gurus!
New to VB and have question.
I have spreadsheet with text values in Col A (Laptop, Desktop..etc) mixed in with other text
(Ex: Column A could say Laptop, it could also say "Brand New Laptop"...etc)

Based on finding the text value "laptop" regardless of case anywhere in Col A, I want VB to copy/paste that row into a secondary spreadsheet called Laptop.

I have a VB that does this if EXACT match for "Laptop" in Col A. How do I tell it to look in Col A for "laptop" wherever the word comes inside the cell in Col A?
Thanks so much!
Ihsahn

I have:

Sub JimMacro()
'
' JimMacro Macro
'

'
Set a = Sheets("SOURCE081711")
Set b = Sheets("LAPTOPS")
Dim x
Dim z

x = 1
z = 2

Do Until IsEmpty(a.Range("A" & z))

If a.Range("A" & z) = "Laptop" Then
x = x + 1
b.Rows(x).Value = a.Rows(z).Value

a.Select
Rows(z).Copy

b.Select
Rows(x).PasteSpecial

End If
z = z + 1
Loop
End Sub


See More: Excel-VB to copy/paste a row if Certain Text

Report •

#1
August 18, 2011 at 14:15:36
actually, I figured it out, it's a matter of using LIKE and then a wildcard character *, and enclosing this within "" for a specific search and return.

So I amended my code to this, hope it helps somebody else!

Sub JimMacro()
'
' JimMacro Macro
'

'
Set a = Sheets("SOURCE081711")
Set b = Sheets("LAPTOPS")
Dim x
Dim z

x = 1
z = 2

Do Until IsEmpty(a.Range("A" & z))

If a.Range("A" & z) Like "*Laptop*" Then
x = x + 1
b.Rows(x).Value = a.Rows(z).Value

a.Select
Rows(z).Copy

b.Select
Rows(x).PasteSpecial

End If
z = z + 1
Loop
End Sub


Report •
Related Solutions


Ask Question