Unable to run Index function in VBA

[Ram kumar]

Hello Friends,

I am new to VBA. I tried to run index function through VBA. The code runs but no data is seen. Could you please help me in correcting the code.

Please find the attached document

 

[PCosta87]

Hi,

This should work:

Sub small_index()
Dim var1 As Integer
Dim var2 As Integer
Application.ScreenUpdating = False

On Error Resume Next
For i = 2 To 7
    For j = 2 To 4
        var1 = Application.WorksheetFunction.Match(Cells(i, 1).Value, Sheet1.Range("A3:A8"), 0)
        var2 = Application.WorksheetFunction.Match(Cells(1, j).Value, Sheet1.Range("B2:F2"), 0)
        Cells(i, j) = Application.WorksheetFunction.Index(Sheet1.Range("B3:F8"), var1, var2)
    Next j
Next i
Application.ScreenUpdating = True

End Sub

Untested though
I recommend avoiding the "On error resume next" as much as you can... that was why it didn't throw an error when you ran the code and, most likely, why you couldn't find the problem...

Hope it helps

 

[Ram kumar]

Hi,

This should work:

Sub small_index()
Dim var1 As Integer
Dim var2 As Integer
Application.ScreenUpdating = False

On Error Resume Next
For i = 2 To 7
    For j = 2 To 4
        var1 = Application.WorksheetFunction.Match(Cells(i, 1).Value, Sheet1.Range("A3:A8"), 0)
        var2 = Application.WorksheetFunction.Match(Cells(1, j).Value, Sheet1.Range("B2:F2"), 0)
        Cells(i, j) = Application.WorksheetFunction.Index(Sheet1.Range("B3:F8"), var1, var2)
    Next j
Next i
Application.ScreenUpdating = True

End Sub

Untested though
I recommend avoiding the "On error resume next" as much as you can... that was why it didn't throw an error when you ran the code and, most likely, why you couldn't find the problem...

Hope it helps

Hello Costa,

Thank you for the help. It worked partially. I have some cells for which there is no data, I want to put them blank. how do I do that?

Thank you

 

[PCosta87]

Hello Costa,

Thank you for the help. It worked partially. I have some cells for which there is no data, I want to put them blank. how do I do that?

Thank you

Perhaps something like this:

Sub small_index()
Dim var1 As Integer
Dim var2 As Integer
Application.ScreenUpdating = False

On Error Resume Next
For i = 2 To 7
    For j = 2 To 4
        var1 = Application.WorksheetFunction.Match(Cells(i, 1).Value, Sheet1.Range("A3:A8"), 0)
        var2 = Application.WorksheetFunction.Match(Cells(1, j).Value, Sheet1.Range("B2:F2"), 0)
        If Err = 0 Then
            Cells(i, j) = Application.WorksheetFunction.Index(Sheet1.Range("B3:F8"), var1, var2)
        End If
        Err.Clear
    Next j
Next i
Application.ScreenUpdating = True

End Sub

 

[Ram kumar]

Thanks a lot PCosta, its working

 

[PCosta87]

Thanks a lot PCosta, its working

Don't mention it