Find a value and move the cursor after copy the active cell

[Jordan22]

Hello!
I am new in the VBA so I would like to ask a little help.

I need to find a value in a sheet (second sheet in the workbook) (it's the header cell of a column) this column's position always different, so it can be in C or D or E etc. column).

If i can find it, i need to jump into last cell (what contains value) in this column (luckily it doesnt have empty cells between datas), go up with one cell and after step into left way with 8 cell and i need that value in a different sheet.

For example: Header cell in N2, so it will jump into N17, go back with 1 cell N16, go into left way with 8 cell so i will get the F16, and i need this value.
Just the column's number and row always depend on header's location.

 

[Joe4]

I need to find a value in a sheet (second sheet in the workbook) (it's the header cell of a column) this column's position always different, so it can be in C or D or E etc. column).

For example: Header cell in N2, so it will jump into N17, go back with 1 cell N16, go into left way with 8 cell so i will get the F16, and i need this value.
Just the column's number and row always depend on header's location.

I don't think I am understanding this too well. If you were in columns C, D, or E, it is not possible to go 8 columns to the left.

I think it might be best if you can show us some data, and walk us through the actual steps with that data.

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.

 

[Jordan22]

1. I need to find the "Watermelon" cell in this sheet. It's location always different, because sometimes we add extra columns and rows into sheet. So now it's in the G3, but next time it can be the H3 or maybe I4 or J5 etc
2. I need to jump into the last cell what contains value in this column, in our example it will be in the G20 (value is 17)
3. I need to go back with one cell, so i will be in the G19 (value is 16)
4. I need to go left side with 4 cell, so i will be in the C19 (value is 188)
5. I need this cell(C19)'s value in a different sheet's G5 cell

Thanks your help!

 

[Joe4]

See if this does what you want.
Change the sheet names and value to look for to suit your needs:

Sub MyValueMacro()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim str As String
    Dim hdr As Range
    Dim vl
    
    Application.ScreenUpdating = False
    
'   Set worksheet to search
    Set ws1 = Sheets("Sheet4")
'   Set worksheet to paste results to
    Set ws2 = Sheets("Sheet5")
    
'   Enter value to search for on 1st sheet
    str = "Watermelon"
    
'   Find column on first sheet with value to search for
    ws1.Activate
    Set hdr = Rows("2:2").Find(What:=str, After:=Range("A2"), LookIn:=xlFormulas2 _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
'   Get value from desired cell
    vl = hdr.End(xlDown).Offset(-1, -4).Value
    
'   Paste to cell G5 on 2nd sheet
    ws2.Range("G5") = vl
        
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
        
End Sub

 

[Jordan22]

Thank you very much!

It has a little problem, but i try to figure what is the problem with it.

 

[Joe4]

That usually means it cannot find what you are looking for.
If you step into the code, and go through one line at a time (using the F8) key, what line of code triggers that error?

 

[Jordan22]

It has problem with this line.

 

[Joe4]

Put this line just before that highlighted line, and tell us what it returns:

MsgBox hdr.Address

 

[Jordan22]

Sorry for the late reply.
It gives the same error.

 

[Jordan22]

I edited this part Rows("2:2") --> Rows("1:999"), and now it works perfectly! Thanks again!