I've used this code successfully in another application that I programmed. However, it seems to have me stumped at this instance. This should be simple. All I want to do is add the vlookup formula in a cell. However, the program needs to determine the number of cells in use by using counta in column A then cycle through the Rows and place the formula in cell D.
This is what I have so far any help would be appreciated.
This might help you understand it.
[vba]Sub vlookupsector()
Dim nRow As Long
Dim cnt As Integer
Dim Rng As Range
Dim SomeWorksheet As Worksheet 'Find and replace in code below
Set Rng = WkSht.Range("a1:s800")
'Set cnt = Number of Non Empty Cells in Column("A")
cnt = WorksheetFunction.CountA(SomeWorksheet.Range("a1:a800"))
For i = 1 To cnt
'Set nRow to last used Row + 1 in Column ("A")
'This may or may not be the same value as cnt.
nRow = SomeWorksheet.Range("A1").End(xlDown).Row + 1
'Rng(nRow) is one row. Can't tell what Column you want.
Rng(nRow, "").Formula = "=IF(ISNA(VLOOKUP(Q" & nRow & ",trliqlookup!$A$2:$i$4000,3,FALSE)),,VLOOKUP(Q" & nRow & ",trliqlookup!$A$2:$i$4000,3,FALSE))"
'Instead of: "=IF(ISNA(VLOOKUP(Q" & nRow & "
'Try: "=IF(ISNA(VLOOKUP(""Q" & nRow & ""
'However, say that nRow = 345. The VLookUp LookUp Value for Cell "A345"
'becomes Range("Q345").Value
Your formula doesn't appear to relate to your worksheet so at a guess you mean:
[vba] Range("D2" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sectors!R2C1:R4000C3,3,FALSE),"""")"
[/vba]