0

I have ask a related question here.
Sir Adelaide has provided me this very useful solution.

So now, in this almost similar case, I have 2 excel sheets in my workbook.
[Xsheet][1] Sheet1

I'm gonna loop through the name and description column in Sheet1 to see if it match the value in name or description column in XSheet (there might be infinite data rows in the column). If they do, then 'that' row in Sheet1 would be copied into new Sheet2.

I have modified a little bit in the previous coding (provided by Sir Adelaide),

Sub Procedure2()

Dim xsht As Worksheet
Dim sht As Worksheet 'original sheet
Dim newsht As Worksheet 'sheet with new data

Set xsht = ThisWorkbook.Worksheets("Xsheet")
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")

'Set dat = sht.Range("code").Cells(1,1)
Set main = xsht.Range("A1")
Set dat = sht.Range("A1")
Set newdat = newsht.Range("A1")

'initialise counters
i = 1
j = 1

'set heading on sheet2
newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 'copy code
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 'copy title
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 'copy date
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 'copy name
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 'copy descr
newdat.Offset(0, 5).Value = dat.Offset(0, 6).Value 'copy status

Do While dat.Offset(i, 0).Value <> "" 'loop row till code data goes blank
  If ((main.Offset(i, 0).Value = dat.Offset(i, 4).Value Or _
  main.Offset(i, 1).Value = dat.Offset(i, 5).Value) And dat.Offset(i, 6).Value = "active") Then 'check conditions
    newdat.Offset(j, 0).Value = dat.Offset(i, 0).Value 'copy code
    newdat.Offset(j, 1).Value = dat.Offset(i, 2).Value 'copy title
    newdat.Offset(j, 2).Value = dat.Offset(i, 3).Value 'copy date
    newdat.Offset(j, 3).Value = dat.Offset(i, 4).Value 'copy name
    newdat.Offset(j, 4).Value = dat.Offset(i, 5).Value 'copy descr
    newdat.Offset(j, 5).Value = dat.Offset(i, 6).Value 'copy status
    j = j + 1
  End If

  i = i + 1
Loop

Any advice provided would be appreciated. Thank you.
output Hi, I've tried to run the updated code.
This is my output, but there is an inactive case, which is not correct.
The correct output should be 4566,4987,4988.
I have go through the code, Idk what went wrong

I take away Xsheet link bcause I dont have enough reputation to make more than 2 hyperlink

I now looping through the Sheet1 to see if its match the columns in Xsheet.
4566, it matches 'Adam' in name col (since it's name or description so if name matches then it's a match), and (need to be) active, so its in.
4899, Edward is a match (or whatever description), but did not match and active, so no.
4987, same case as 4566, its Adam and active.
4988, Kris (not a match name), but al is in Xsheet's description, and active, so it's in.
4989, Chris not a match name, ttr not a match description, even its an active case (I also wont take it in)

Thank you for your guidance so far. I really appreciate it.

  • It would help to post screen shots of your Sheets and also paste your code in its entirety in a block. – ejbytes Mar 23 '17 at 4:06
  • Also this would give insight to what main and data is. If main is a sheet name, then you'd have to change it to Sheets("main").range ("A1").offeet (i,j).Value – ejbytes Mar 23 '17 at 4:12
  • Hi ejbytes, I've upload the screesnshot with the code. Thanks! – ExcelNovice Mar 23 '17 at 14:46
  • I figured it out for you. Simply click in the faint CheckMark next to to the solution to accept the answer. Don't feel bad, the best of us miss this one all the time. Welcome to SuperUser! – ejbytes Mar 23 '17 at 19:35
  • So in plain words: If the (Name or Description matches), then ask, is it Active. If all true, then copy. Or is it just simply, "Is this active?" if so, lets copy the info over. I think that's really it right? In that case it would make more sens to go through the S-list and check to see if the Master is requesting it and if so copy it. You know this could be done in a Table and filter, no need for VB code at all. – ejbytes Mar 24 '17 at 12:50
1

So after finding out what you are really doing. The question is simple:


"If the Name or the Description on the Master List is found in the Data Sheet and it is also Active, then copy it to a new sheet".

Logical Operators : Order of precedence

Here is a revision of the code wrt your recent comment.

Sub Procedure2()

Dim xsht As Worksheet
Dim sht As Worksheet 'original sheet
Dim newsht As Worksheet 'sheet with new data

Set xsht = ThisWorkbook.Worksheets("Xsheet")
Set sht = ThisWorkbook.Worksheets("Sheet1")
Set newsht = ThisWorkbook.Worksheets("Sheet2")

'Set dat = sht.Range("code").Cells(1,1)
Set main = xsht.Range("A1")
Set dat = sht.Range("A1")
Set newdat = newsht.Range("A1")

'initialise counters
Dim i, j, iRow As Integer   'instantiate and initialize the integers
i = 1
j = 1
iRow = 1

'set heading on sheet2
newdat.Offset(0, 0).Value = dat.Offset(0, 0).Value 'copy code
newdat.Offset(0, 1).Value = dat.Offset(0, 2).Value 'copy title
newdat.Offset(0, 2).Value = dat.Offset(0, 3).Value 'copy date
newdat.Offset(0, 3).Value = dat.Offset(0, 4).Value 'copy name
newdat.Offset(0, 4).Value = dat.Offset(0, 5).Value 'copy descr
newdat.Offset(0, 5).Value = dat.Offset(0, 6).Value 'copy status

Do While main.Offset(i, 0).Value <> "" Or main.Offset(i, 1).Value <> ""

  j = 1     'reset DataSheet pointer

  Do While dat.Offset(j, 0).Value <> ""

    If (main.Offset(i, 0).Value = dat.Offset(j, 4).Value _
    Or main.Offset(i, 1).Value = dat.Offset(j, 5).Value) _
    And dat.Offset(j, 6).Value = "active" Then

      newdat.Offset(iRow, 0).Value = dat.Offset(j, 0).Value 'copy code
      newdat.Offset(iRow, 1).Value = dat.Offset(j, 2).Value 'copy title
      newdat.Offset(iRow, 2).Value = dat.Offset(j, 3).Value 'copy date
      newdat.Offset(iRow, 3).Value = dat.Offset(j, 4).Value 'copy name
      newdat.Offset(iRow, 4).Value = dat.Offset(j, 5).Value 'copy descr
      newdat.Offset(iRow, 5).Value = dat.Offset(j, 6).Value 'copy status
      iRow = iRow + 1
    End If
    j = j + 1     'increment DataSheet pointer; fast moving; changing/resetting
  Loop

  i = i + 1     'increment XSheet pointer; slow moving outer loop; not resetting
Loop
End Sub

This revised code has FOUR changes. Added the check in the OUTER Loop to include blanks in Name field by adding Or main.Offset(i, 1).Value <> "". The change of where the information was being evaluated from i-to-i_value, to i-to-j_value, in the If statement. The addition of a third counter for data placement in the new sheet for copied data to Sheet2. And lastly, a nested loop (loop inside a loop). Loop-Outer: Looks at the Master List (xSheet) row-by-row; never repeats. Loop-Inner: Looks at the data sheet to compare top-to-bottom; repeats every new row in Master List.


You could even change the If statement to consider "active" vs. "Active", or "A" or "a". This is where a drop list comes in handy, but that's another problem in itself.

If (main.Offset(i, 0).Value = dat.Offset(j, 4).Value _
Or main.Offset(i, 1).Value = dat.Offset(j, 5).Value) _
And (dat.Offset(j, 6).Value = "active" Or dat.Offset(j, 6).Value = "Active") Then
  • Hi ejbytes, Thank you for sharing me this information. I've replace the code. but Do While dat.Offset(i, 0).Value "" 'loop row till code data goes blank is in red due to syntax error. – ExcelNovice Mar 24 '17 at 0:28
  • If you've saved it, copy the code you posted (missing end sub), then add a single left parenthesis and a single right parenthesis. You should be fine. Your code, otherwise, was fine. Just logical error with good syntax. – ejbytes Mar 24 '17 at 1:03
  • If ((main.Offset(i, 0).Value = dat.Offset(i, 4).Value Or _ main.Offset(i, 1).Value = dat.Offset(i, 5).Value) And dat.Offset(i, 6).Value = "active") Then Hi ejbytes, I modified the code a lil bit because I want the outcome to be, if name match and active (copy), if description match and active (copy). So it should be 4566,4987,4988. But I am only getting 4566 for now. Would you shed me some light on this ? thank you. – ExcelNovice Mar 24 '17 at 2:37
  • That's correct, you'll only get 4566, it's all because of the missing parentheses. – ejbytes Mar 24 '17 at 3:15
  • Oh... I see what you are trying to do. I thought your code was what you were after. But your last comment just struck a cord. I'm pretty sure this is what you are after. It's a Big(O), of n^2, or the size of the list times the size of the list. There are ways to optimize this, but this will get you there for now. – ejbytes Mar 24 '17 at 5:32

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.