VBA - Match/Match Indexing - Entering Value from a Cell into another Sheet Based on Match of Column and Row

[dwhitey1124]

Hi all,
I have two sheets in my file. The first sheet (sheet1) is a control tab with a list of names and values. The second sheet (sheet2) is a more comprehensive list of names and values. On the control tab, I have a table of names and values to override pre-populated values on the second sheet. I have tried to write a macro that cycles through J5:J45 of the control tab. If the the cell is blank, it goes on to the next row. If it is not blank, the macro finds the row of the value in (i, J) in sheet2 A1:A400. It then finds the column of value (i, G) in A3:AA3 of sheet2. Using this match/match process, it then enters the value in that cell as (i, K) from sheet1. When I run the macro, I am getting a "Next without For" error. I hope it is an easy fix.

If anyone can think of how to address the issue or has an idea for a better macro, I would love to hear it. Thank you in advance.

sub OverRide()
Sheet1.Select

For i = 5 To 45
Cells(i, 10).Select
        If Cells(i, 10).Value <> "" Then
        Sheet2.Select

        Dim lRow1 As Long
        Dim lColumn1 As Long

        On Error Resume Next
        lRow1 = Application.WorksheetFunction.Match(Sheet1.Cells(i, 10).Value, Range("A1:A400"), 0)
        On Error Resume Next
        lColumn1 = Application.WorksheetFunction.Match(Sheet1.Cells(i, 7).Value, Range("A3:AA3"), 0)
 
        On Error GoTo 0
        If lRow1 > 0 Then
        'code
        Cells(lRow1, 1).Offset(0, lColumn1 - 1).Select
        ActiveCell.Value = Sheet1.Cells(i, 11).Value
        
        End If

Next i

End Sub

 

[COwen]

You are missing an End If

 

[dwhitey1124]

You are missing an End If

Would you please let me know where the second End If should go? I had it in before and was still getting an error so must have put it in the wrong place. Thank you.

 

[steve the fish]

Try it like this. You dont need all those selects

Dim lRow1 As Long, lColumn1 As Long, i As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheet1
Set sh2 = Sheet2

For i = 5 To 45
    If sh1.Cells(i, 10).Value = "" Then
        lRow1 = Application.Match(sh1.Cells(i, 10).Value, sh2.Range("A1:A400"), 0)
        lColumn1 = Application.Match(sh1.Cells(i, 7).Value, sh2.Range("A3:AA3"), 0)
        If Not IsError(lRow1) And Not IsError(lColumn1) Then
            sh2.Cells(lRow1, lColumn1).Value = sh1.Cells(i, 11).Value
        End If
    End If
Next i

 

[dwhitey1124]

Try it like this. You dont need all those selects

Dim lRow1 As Long, lColumn1 As Long, i As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheet1
Set sh2 = Sheet2

For i = 5 To 45
    If sh1.Cells(i, 10).Value = "" Then
        lRow1 = Application.Match(sh1.Cells(i, 10).Value, sh2.Range("A1:A400"), 0)
        lColumn1 = Application.Match(sh1.Cells(i, 7).Value, sh2.Range("A3:AA3"), 0)
        If Not IsError(lRow1) And Not IsError(lColumn1) Then
            sh2.Cells(lRow1, lColumn1).Value = sh1.Cells(i, 11).Value
        End If
    End If
Next i

That gave me a run-time error 13 type mismatch. Any ideas?

 

[COwen]

Dim lRow1 As Long, lColumn1 As Long, i As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheet1
Set sh2 = Sheet2

For i = 5 To 45
    If sh1.Cells(i, 10).Value <> "" Then
        lRow1 = Application.Match(sh1.Cells(i, 10).Value, sh2.Range("A1:A400"), 0)
        lColumn1 = Application.Match(sh1.Cells(i, 7).Value, sh2.Range("A3:AA3"), 0)
        If Not IsError(lRow1) And Not IsError(lColumn1) Then
            sh2.Cells(lRow1, lColumn1).Value = sh1.Cells(i, 11).Value
        End If
    End If
Next i

I just changed the first If Statement @steve the fish to <> "".

That gave me a run-time error 13 type mismatch. Any ideas?

Which line is it throwing the error on?

 

[dwhitey1124]

Dim lRow1 As Long, lColumn1 As Long, i As Long
Dim sh1 As Worksheet, sh2 As Worksheet

Set sh1 = Sheet1
Set sh2 = Sheet2

For i = 5 To 45
    If sh1.Cells(i, 10).Value <> "" Then
        lRow1 = Application.Match(sh1.Cells(i, 10).Value, sh2.Range("A1:A400"), 0)
        lColumn1 = Application.Match(sh1.Cells(i, 7).Value, sh2.Range("A3:AA3"), 0)
        If Not IsError(lRow1) And Not IsError(lColumn1) Then
            sh2.Cells(lRow1, lColumn1).Value = sh1.Cells(i, 11).Value
        End If
    End If
Next i

I just changed the first If Statement @steve the fish to <> "".



Which line is it throwing the error on?

Ah that worked. It was an issue with the names not being the exact same for the match function. Your code worked perfectly. Thank you. Have a great evening.

 

[DanteAmor]

If I understood correctly, the results are written on sheet2 in the range of B4:AA300.

Here another macro with another approach for you to consider:

Sub OverRide_2()
  With Sheet2.Range("B4:AA" & Sheet2.Range("A" & Rows.Count).End(3).Row)
    .Formula = "=IFERROR(INDEX(Sheet1!$K$5:$K$45,MATCH(B$3&""|""&$A4,INDEX(Sheet1!$G$5:$G$45&""|""&Sheet1!$J$5:$J$45,0),0)),"""")"
    .Value = .Value
  End With
End Sub