Return lines Values between 2 lines with a specific sentence via FIND

Question

I have this in Sheet 1 column A, with a lot of other text before and after :

I want to copy all what is betwen cell with :

Sponsor de l'Indice Marché Site Internet

and with :

DEFINITIONS APPLICABLES AUX(EVENTUELS), AU

In B8 sheet2 :

This is my pseudo-code (Not working on VBA) :

Dim x As Long
    x = 1
    Do While Worksheets("Adobe Reader").Range("A1:A500").Find("Sponsor de l'Indice March? Site Internet").Row != Worksheets("Sheet1").Range("A1:A500").Find("DEFINITIONS APPLICABLES AUX(EVENTUELS), AU").Row
Set SJ = Worksheets("Sheet1").Range("A1:A500").Find("Nom de l'Indice Code Bloomberg Sponsor de l'Indice March? Site Internet")
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Copy
Worksheets("Sheet2").Range("B8").Offset(ColumnOffset:=x - 1).Paste

You don't state your problem, so I will guess it lies with the `ActiveCell` statements, which you want to avoid since very often, the active cell is not the one you think. See [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Nov 15 '18 at 18:00
My code translate more my mind than a true code, for exemple "!=" will not work here — babou, Nov 15 '18 at 18:10
Then please [edit your question](https://stackoverflow.com/posts/53325304/edit) to mention it is "pseudo-code" and let us know what the problem is! — cybernetic.nomad, Nov 15 '18 at 18:14

score 1 · Accepted Answer · answered Nov 15 '18 at 18:13

1

You can try this:

Option Explicit

Sub copyRangeBetweenLookUpValues()

    Dim lookUpValue1 As String
    Dim lookUpValue2 As String

    Dim lookUpValue1R As Long, lookUpValue1C As Long, lookUpValue2R As Long, lookUpValue2C As Long

    'set your lookup values here
    lookUpValue1 = "Sponsor de l'Indice Marché Site Internet"
    lookUpValue2 = "DEFINITIONS APPLICABLES AUX(EVENTUELS), AU"

    'find row and column of first value
    lookUpValue1R = Cells.Find(lookUpValue1).Row
    lookUpValue1C = Cells.Find(lookUpValue1).Column

    'find row and column of second value
    lookUpValue2R = Cells.Find(lookUpValue2).Row
    lookUpValue2C = Cells.Find(lookUpValue2).Column

    'copy range between these 2 values (but without values so first Row+1, second Row -1)
    Range(Cells(lookUpValue1R + 1, lookUpValue1C), Cells(lookUpValue2R - 1, lookUpValue2C)).Copy

    'paste 
    Range("B1").PasteSpecial xlPasteAll

End Sub

answered Nov 15 '18 at 18:13

Pawel Czyz

1,651
4
17
21

Thank you !! It works well ! And if values are in sheet1 and should it be paste in sheet 2 ? I found it with Select function but it's ugly... – babou Nov 16 '18 at 08:17
1

You can do it like so: Worksheets("sheet2").Range("B1").PasteSpecial xlPasteAll and if you want to target another workbook you can specify it before worksheet. Basically if you do not specify it assumes that you want to target active workbook which is not very robust. Best way is to declare all target sheets workbooks etc and then refer to them using variable name. If it get too long just use With statements. – Pawel Czyz Nov 16 '18 at 08:21
And if there is no lookUpValue2 ? How can I cancel all the code ? – babou Nov 16 '18 at 16:20
Dim rngFound As Range Set rngFound = Sheets("WhateverSheet").UsedRange.Find(What:="SoughtValue",LookIn:=xlFormulas) If Not rngFound Is Nothing Then 'you found the value - do whatever Else ' you didn't find the value End if – Pawel Czyz Nov 17 '18 at 18:25
More about it here: https://www.mrexcel.com/forum/excel-questions/519070-vba-error-handling-when-dealing-cells-find.html – Pawel Czyz Nov 17 '18 at 18:25
ok, but I Don't want to put all my code who come next in an IF… how can I adapt it to the code that you sent to me ? – babou Nov 19 '18 at 10:54

Return lines Values between 2 lines with a specific sentence via FIND

1 Answers1