VBA to EXTRACT & LOOP

[shahzeb123]

Guys I am Back !

I have tried CHATGPT to write the code but it is only stupid code that does not work.

Now, my query is i need a code that needs to go to cell A31 then find "F57A" then find BKCH when it finds this then start extracting from BKCH till 11 digits only.

Then skip to A68 and do the same thing and paste the data in column L

Meaning it needs to extract data from A31 then skip 37 rows and do the same thing then again again till there is no data

PLEASE HELP

 

[Joe4]

I have tried CHATGPT to write the code but it is only stupid code that does not work.

Yes, ChatGPT is pretty horrible when it comes to creating working code.

I think if would be helpful if you showed us a small sample of your data, so we can see the value you are working with.
And what should it return in column L if it doesn't find the values you are looking for (or should it ALWAYS find something)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[shahzeb123]

see this sheet in A31

Actually A31 has too much data that is why it needs to find F57A then start from BKCH

 

[Joe4]

I do not see the value you are looking for in cell A31 in your example. It appears that that part of the data is being cut-off from your screen print.

Can you show us the ENTIRE cell contents of cell A31, and show us exactly what should be pulled into column L for this example?

 

[shahzeb123]

It should extract this only

 

[Joe4]

See if this does what you want:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all data, skipping 37 rows at a time
    For r = 31 To lr Step 37
        Cells(r, "L").FormulaR1C1 = _
            "=MID(RC[-11],FIND(""BKCH"",MID(RC[-11],FIND(""F57A"",RC[-11]),999))+FIND(""F57A"",RC[-11])-1,11)"
    Next r

End Sub

 

[shahzeb123]

Your code works like butter

but there is one problem that some time the gap is not 36 row it is 37 row and then whole thing goes useless

because this highlighted words.

can we add that code to find first "BLOCK 4" because it is always written on one cell above

 

[Joe4]

Try this then:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all data
    For r = 31 To lr
'       Check to see if row above says "Block 4"
        If Left(Cells(r - 1, "A"), 7) = "Block 4" Then
'           Populate column L with formula
            Cells(r, "L").FormulaR1C1 = _
                "=MID(RC[-11],FIND(""BKCH"",MID(RC[-11],FIND(""F57A"",RC[-11]),999))+FIND(""F57A"",RC[-11])-1,11)"
        End If
    Next r

    Application.ScreenUpdating = True
   
End Sub

 

[shahzeb123]

You are my HERO

 

[Joe4]

You are welcome.
Glad I was able to help!