For with mixed index and match

Salazard

New Member
Joined
Mar 19, 2018
Messages
2
Hi, I have to add a row2 to my actual row. But i must look up for the second row with an index and a match. I have tried severeal things but i keep taking an error of type mismatch (13). Can anybody please help me?

PHP:
Function Number2Letter(LastColumn As Long) As StringDim
 ColumnLetter As String  
ColumnLetter = Split(Cells(1, LastColumn).Address, "$")(1) 
 Number2Letter = ColumnLetterEnd Function

Sub Sumador()
Const TEST_COLUMN As String = "A"
Dim PIPO As Range, cell1 As Range, cell2 As Range, cell3 As Range, cell4 As Range
Dim LastRow As Long, LastColumn As Long, 
Dim Vec2 As StringDim Curiosity1 As String, Curiosity2 As String, Curiosity3 As String, Curiosity4 As String, Curiosity5 As String, Curiosity6 As String, Curiosity7 As String, Curiosity8 As String, Curiosity9 As String

SheetName = ActiveSheet.Name
WorkbookName = ActiveWorkbook.Name
With Workbooks(WorkbookName)With
 Worksheets(SheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
Vec2 = Number2Letter(LastColumn)

For Each cell1 In Range("Z19:Z26")  
Curiosity1 = cell1.Value
For Each cell2 In Range("A2", "A" & LastRow
 Curiosity2 = cell2.Value
If cell2.Value = cell1.Value Then
For Each cell3 In Range("B2", "B" & LastRow) 
Curiosity3 = cell3.Value
 Curiosity4 = cell1.Offset(0, 1)
If cell3.Value = cell1.Offset(0, 1).Value Then
For Each cell4 In Range("F2", "F" & LastRow)
Curiosity5 = cell4.Value
Curiosity6 = cell1.Offset(0, 3)
 If cell4.Value = cell1.Offset(0, 3) The
 i = 0 
For i = 1 To LastColumn Step 1 
Curiosity7 = cell4.Row
  cell4.Offset(0, i) = WorkbookName.WorksheetFunction.Index(Range(cell4.Offset(0, i), cell4.Offset(LastRow, i)), WorkbookName.WorksheetFunction.Match(1, (cell2.Value = Range("A1", "A" & LastRow)) * (cell1.Offset(0, 1) = Range("B1", "B" & LastRow)) * (cell4.Value = Range("F1", "F" & LastRow)), 0)) + cell4.Offset(cell4.Row, i)
 Next i 
End If 
Next cell4 
End If 
Next cell3
 End If
Next cell2
Next cell1
End With
End With
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi Salazard

In your own words, please explain what you intend to achieve with this line of code:

Code:
[FONT=Courier New][COLOR=#0000bb]cell4[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]0[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]WorkbookName[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]WorksheetFunction[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Index[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]cell4[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]0[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700]), [/COLOR][COLOR=#0000BB]cell4[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]LastRow[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]i[/COLOR][COLOR=#007700])), [/COLOR][COLOR=#0000BB]WorkbookName[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]WorksheetFunction[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Match[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700], ([/COLOR][COLOR=#0000BB]cell2[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"A1"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"A" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]LastRow[/COLOR][COLOR=#007700])) * ([/COLOR][COLOR=#0000BB]cell1[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]0[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) = [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"B1"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"B" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]LastRow[/COLOR][COLOR=#007700])) * ([/COLOR][COLOR=#0000BB]cell4[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"F1"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#DD0000]"F" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000BB]LastRow[/COLOR][COLOR=#007700])), [/COLOR][COLOR=#0000BB]0[/COLOR][COLOR=#007700])) + [/COLOR][COLOR=#0000BB]cell4[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Offset[/COLOR][COLOR=#007700]([/COLOR][COLOR=#0000BB]cell4[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000BB]Row[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]I[/COLOR][COLOR=#007700])[/COLOR][/FONT]

Cheers

pvr928
 
Upvote 0
Iam trying to add to the current cell, the value of the indexed and matched cell by adding its value and the value looked for.
I have a lot of columns which I would like to make that, so iam changing the column with a For
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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