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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

COwen

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
You are missing an End If
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,318
Office Version
  1. 365
Platform
  1. Windows
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
 

dwhitey1124

New Member
Joined
Oct 24, 2014
Messages
28

ADVERTISEMENT

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?
 

COwen

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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?
 

dwhitey1124

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,391
Messages
5,601,391
Members
414,448
Latest member
Jessica 22664

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
Top