Troubleshoot

[Gautam]

I am find an error in this statement, and the error shown is
"Application defined error or object defined error"

ActiveSheet.Range(Chr(r + 64 + 0) & c).Formula = "=index(" & Chr(11 +
64) & "2:" & Chr(11 + 64) & (max_date) & ",match(" & Chr(1 + 64) & r &
",L2:L" & max_row & "))"


The above statement basically used to match the data from one column
with any column, corresponding to the indexed column. used for
matching dates and times

Could any one please solve this

 

[Dave Peterson]

First, you can use other ways to address cells other than .range(). In your
case, .cells() looks like it work much nicer.

..cells(x,y).formula

The x represents the row and the y represents the column. And y can be a number
or a letter--excel will accept either.

I _think_ that this may be closer to what you want:

Option Explicit
Sub testme()

Dim iRow As Long
Dim iCol As Long
Dim Max_Row As Long

'test data
iRow = 3
iCol = 3
Max_Row = 777

ActiveSheet.Cells(iRow, iCol).Formula _
= "=index(K2:K" & Max_Row & ",match(A" & iRow & ",l2:L" & Max_Row & "))"

End Sub

Notice that I changed (max_date) to max_row. I can't think of where I'd want
the range in =index() to have different number of rows than the range in the
=match() portion.

 

[Gautam]

First, you can use other ways to address cells other than .range(). In your
case, .cells() looks like it work much nicer.

.cells(x,y).formula

The x represents the row and the y represents the column. And y can be a number
or a letter--excel will accept either.

I _think_ that this may be closer to what you want:

Option Explicit
Sub testme()

Dim iRow As Long
Dim iCol As Long
Dim Max_Row As Long

'test data
iRow = 3
iCol = 3
Max_Row = 777

ActiveSheet.Cells(iRow, iCol).Formula _
= "=index(K2:K" & Max_Row & ",match(A" & iRow & ",l2:L" & Max_Row & "))"

End Sub

Notice that I changed (max_date) to max_row. I can't think of where I'd want
the range in =index() to have different number of rows than the range in the
=match() portion.

Thanks Dave

Macro's working