PDA

View Full Version : Row Fomula entry



kdigital
03-20-2013, 11:02 AM
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.

SamT
03-20-2013, 01:19 PM
This might help you understand it.
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

Next i
End Sub

Aflatoon
03-21-2013, 03:41 AM
Your formula doesn't appear to relate to your worksheet so at a guess you mean:
Range("D2:D" & Cells(Rows.Count, "A").End(xlUp).Row).FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,Sectors!R2C1:R4000C3,3,FALSE),"""")"