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

dwhitey1124

New Member
Joined
Oct 24, 2014
Messages
28
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.

VBA Code:
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try it like this. You dont need all those selects

VBA Code:
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
 
Upvote 0
Try it like this. You dont need all those selects

VBA Code:
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?
 
Upvote 0
VBA Code:
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?
 
Upvote 0
VBA Code:
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.
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top