Finding a value, then copying and pasting cells around it to another sheet

[cboyce44]

Hello. I am a novice with Excel VBA. I have a certificate excel workbook that contains 34 sheets. So far I have it where the user imports data from a PDF document into a sheet called "PitchGageData". What I'm trying to do is search in the "PitchGageData" tab to look for "L1". Once it finds which cell it is in (depending on the pdf file, it moves around), I then want to copy that cell. I need the "L1", then the 2 values below it. Then I need the same for "L2", "L3", "L4", "L5", and "L6". Below is a screen shot of how the pdf was imported into the sheet.

Once I have copied all the values in the cells, I want to paste them to another worksheet called "Nominal Error Calculations" into cell A60, and look like this:

Thank you for your help. I have spent days trying to figure this out.

 

[cboyce44]

No problem, glad we could help.

Alex. I'm trying to use your search in another application. I'm trying to figure out what in the formula tells excel after you find "Pitch" where to search column, and row wise? Right now I'm looking for the word "Nominal Size" but I want it to take all of the data directly under that cell for 24 rows deep, then 6 columns over.

 

[Alex Blakenburg]

Assuming you have set rngSrc using something like this:
Set rngSrc = wsSrc.UsedRange.Find(...........

and you want a range of 24x6

Then in the below:
• Offset(1) moves down one row
• Resize format is (Row,Column)

Set rngSrc = rngSrc.Offset(1).Resize(24, 6)

 

[cboyce44]

Okay. So from your original search in this thread what is going on with the rest of this?

Set rngSrc = rngSrc.Offset(0)
Set rngSrc = rngSrc.Resize(24, 6)
cntItem = WorksheetFunction.CountA(rngSrc)

ReDim arrDest(1 To 3, 1 To cntItem)

For Each rCell In rngSrc
If rCell <> "" Then
i = i + 1
If Left(rCell, 1) = "" Then
arrDest(1, i) = "" & i
Else
arrDest(1, i) = rCell.Value
End If
arrDest(2, i) = Replace(rCell.Offset(1).Value, "I", 1)
arrDest(3, i) = Replace(rCell.Offset(2).Value, "I", 1)

End If
Next rCell

rngDest.Resize(3, i).Value = Application.Substitute(arrDest, "l", 1)

I'm trying to learn this, and am confused. When I run this, I get all of the data, but it looks like this:

It stretches all of the data out to column BT an duplicates a lot of the data.