VBA Find-Copy and Loop Trouble

antora

New Member
Joined
May 13, 2013
Messages
5
hi guys need your help.

I have a problem in using the 'find-copy and loop' method,

here's my data:
ABCDEFG
1Name 1A1OKA2OK--
2Name 2B1OK----
3Name 3C1OKC2OKC3OK
4Name 4D1OK--D3OK
5Name 5E1OK----
6....

<tbody>
</tbody>

and this is the code I used:
Code:
Sub Copas()
  Dim DestSheet        As Worksheet
  Set DestSheet = Worksheets("Sheet2")
  
  Dim sRow       As Long
  Dim dRow       As Long
  Dim sCount     As Long
  Dim X As Long
  sCount = 0
  dRow = 1

X = 1
Do Until Cells(X, 1) = ""

  For sRow = Cells(X, 1) To Cells(X, 1000).End(xlToRight).Column
     If Cells(X, sRow) Like "*OK*" Then
        sCount = sCount + 1
        dRow = dRow + 1
        Cells(X, 1).Copy Destination:=DestSheet.Cells(dRow, "A")
        Cells(X, sRow).Copy Destination:=DestSheet.Cells(dRow, "B")
        Cells(X, sRow - 1).Copy Destination:=DestSheet.Cells(dRow, "C")
     End If
Next sRow
  
X = X + 1
Loop
End Sub
what happens is:
The code does not copy all the results that I found. whereas, based on the above data should be there around 9 results but that appears there's only 8 results. what is lacking or wrong of my code.?

thanks before.
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
try:
Code:
Sub Copas()
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sColm As Long
Dim dRow As Long
Dim sCount As Long
Dim X As Long
sCount = 0
dRow = 1

X = 1
Do Until Cells(X, 1) = ""
    For sColm = 2 To Cells(X, Columns.Count).End(xlToLeft).Column
        If Cells(X, sColm) Like "*OK*" Then
            sCount = sCount + 1
            dRow = dRow + 1
            Cells(X, 1).Copy Destination:=DestSheet.Cells(dRow, "A")
            Cells(X, sColm).Copy Destination:=DestSheet.Cells(dRow, "B")
            Cells(X, sColm - 1).Copy Destination:=DestSheet.Cells(dRow, "C")
        End If
    Next sColm
    X = X + 1
Loop
End Sub
(I changed sRow to sColm as it seemed odd to name a variable with the word row in it when it refers to a column!)
 

antora

New Member
Joined
May 13, 2013
Messages
5
try:
Code:
Sub Copas()
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sColm As Long
Dim dRow As Long
Dim sCount As Long
Dim X As Long
sCount = 0
dRow = 1

X = 1
Do Until Cells(X, 1) = ""
    For sColm = 2 To Cells(X, Columns.Count).End(xlToLeft).Column
        If Cells(X, sColm) Like "*OK*" Then
            sCount = sCount + 1
            dRow = dRow + 1
            Cells(X, 1).Copy Destination:=DestSheet.Cells(dRow, "A")
            Cells(X, sColm).Copy Destination:=DestSheet.Cells(dRow, "B")
            Cells(X, sColm - 1).Copy Destination:=DestSheet.Cells(dRow, "C")
        End If
    Next sColm
    X = X + 1
Loop
End Sub
(I changed sRow to sColm as it seemed odd to name a variable with the word row in it when it refers to a column!)
Thanks P45cal... it works
so., what if the search is based on rows. which lines should be replaced?
 

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
so., what if the search is based on rows. which lines should be replaced?
I don't really understand the question. Currently there's a nested loop:

Code:
Do Until Cells(X, 1) = "" 'X is used to define the row number and is incremented each time (the outside loop) 
  For sColm = 2 To Cells(X, Columns.Count).End(xlToLeft).Column 'scolm is used to define the column number and is incremented each time (the inner loop)
    'do stuff here with Cells(X,sColm)  'syntax is: Cells(row number, column number)
  Next sColm   
  X = X + 1 
Loop
The comments in the above snippet show that both rows and colums are used.
I suppose you could invert the loops and put the inside one on the outside:

Code:
For sColm = 2 To Cells(X, Columns.Count).End(xlToLeft).Column
   Do Until Cells(X, 1) = ""
     'do stuff here with Cells(X,sColm)     
     X = X + 1   
   Loop
Next sColm
Is that what you meant?
 

Forum statistics

Threads
1,082,139
Messages
5,363,362
Members
400,730
Latest member
cookie123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top