Excel macro paste same value in ctrl+f box although different cell value

Question

New to forum and vba but want to learn more.

Got two tables of large data and want to look for a cell value equal to the cell value to the left of my active cell in table 1 and then find that value in the 2nd table. When value is found I want to return the cell value found in the 5th column to the right of column A in the 2nd table.

The macro I have created works well - if it hadn't been that it always looks for the same value "10.136.32.10" i.e. this value does not change as the active cell moves down table 1. I would like the value to change depending on what is actually copied from the cell to the left. Is there a way to do this? I use Ctrl+f function and then paste in the cell value copied from table 1

Have the following macro:

Sub Makro2()
'
' Makro2 Makro
'

'
    ActiveCell.Offset(0, -1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("SKF-NOV-6-2017").Select
    Cells.Find(What:="10.136.32.10", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("All Equipment").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I think you want a loop of some kind. Look at For Loops and Do Loops... You will also need to swop "10.136.32.10" with Cell.value or Cell.Text for which ever range of cells you are looping over which contain the value to find — QHarr, Feb 10 '18 at 14:06

score 0 · Answer 1 · edited Feb 11 '18 at 20:33

0

Here is the code by which you can do your job. This macro searches immediately on all rows. If you only need to search for an active cell, then you need to remove the loop.

Sub macro2()

    Dim lr As Long, r As Long, c As Long
    Dim str As String

    lr = Cells(Rows.Count, 1).End(xlUp).Row

    For r = 1 To lr

        str = Cells(r, c).Offset(0, -1)

        Sheets("SKF-NOV-6-2017").Select

        Cells.Find(What:=str, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

        Selection.End(xlToLeft).Select
        ActiveCell.Offset(0, 4).Select
        Application.CutCopyMode = False
        Selection.Copy

        Sheets("All Equipment").Select

        Cells(r, c + 1).past
    Next r
End Sub

edited Feb 11 '18 at 20:33

Miguel_Ryu

1,390
3
18
26

answered Feb 10 '18 at 14:54

Вова Мельниченко

31
2

please indent your code so we can read it easily. And avoid .Select where possible. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – QHarr Feb 10 '18 at 15:29
Macro above gets stuck at "str as sting" - User-defined type not defined – Hawkfield Feb 10 '18 at 19:11
1

Should be String not Sting. – QHarr Feb 10 '18 at 21:52

Excel macro paste same value in ctrl+f box although different cell value

1 Answers1