Match/Find data -> insert result in new row

[drcso]

Hi,

I have some troubles with code and logic for my macro.

I have Sheet1 with numbers.

I have Sheet2, where for each number for Sheet1 data is scraped from web, then certain values are searched for it Sheet2, after being found - data is being deleted, and new data, for next number from Shee1 is scraped.

The string im searcing for in Sheet2 is "Quota number" - since in this Sheet there may occur, multiple time or none, "Quota number: XYZ" (in one cell) - I want to copy such cell and paste it next to the number from Sheet1. However, if value is found more than once I would like to have it pasted in new, added row, below the number pasted with first match. Example:

1) Only one match

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Value Match
45678 0110
12345 0990
34567 0550 </code>
2) More than one match for 12345
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Value Match
45678 0110
12345 0990
12345 0770 '<--- second match in Sheet2
34567 0550 </code>
Please note, that VLOOKUP wont't work, since numbers from Sheet1 do not appear in Sheet2 - there is only data scraped with those numbers used in URL.

I have prepared a code using Match function, but I was not aware that "Quoata number" may appear more than once, and now I have no idea how to use FindNext + pasting found values in new rows (only first found value should be pasted next to the number)

Currently, the codes looks as follows:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">ws2.Activate

For Each c In ws1.UsedRange.Columns("A").Cells 'for each number in ws1 data is scraped to ws2, but I'm not pasting the hole code below

If Not IsError(Application.Match("*Quota number:*", Range("A:A"), 0)) Then
Quota = Application.Match("*Quota number:*", Range("A:A"), 0)
QuotaVal = ActiveSheet.Cells(Quota, 1)

ws1.Cells(c.Row, 2).Value = QuotaVal
End If

Next c</code>

 

[DanteAmor]

I do not understand how your data is on sheet1 and on sheet2
You could put a sample of how your data is on both sheets.
And in another painting the result you expect.


Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[drcso]

Sheet1 - numbers that will be used to generate data in Sheet2
Sheet2 - Generated data
Results

I've found an alternative way to do it, the code works, please find it below:

[/FONT][/COLOR][/FONT][/COLOR]
For Each c In LoopRange

With ActiveSheet.Range("a1:a500")


     Set x = .Find("*Quota number*", LookIn:=xlValues)
     If Not x Is Nothing Then
        firstAddress = x.Address
        Do
            Sheets("Sheet3").Cells(Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = c.Value
            Sheets("Sheet3").Cells(Sheets("Sheet3").Cells(Rows.Count, 2).End(xlUp).Row + 1, 2).Value = x.Value
            Set x = .FindNext(x)
        If x Is Nothing Then
            GoTo DoneFinding
        End If
        Loop While x.Address <> firstAddress
      End If
DoneFinding:


End With

Next

 

[DanteAmor]

Check the following syntax and try to adapt it and tell me.


https://www.mrexcel.com/forum/excel-questions/1092326-help-needed-do-loop.html