Active sheet / cell programming question

**welchs101** · 12-26-2008, 07:20 AM

I have the following code

    'Insert/put HW formula
     Workbooks(Gradebook_Filename).Worksheets(Gradebook_Sht_Nm_Roster).Range("G2").Formula = _
                        "=IF(ISERROR(VLOOKUP(A2," & Gradebook_Sht_Nm_HW & "!$A$2:$V$" & _
                        Rster_lst_row & ",22,FALSE)),""NA"", " & "VLOOKUP(A2," & Gradebook_Sht_Nm_HW & "!$A$2:$V$" & _
                        Rster_lst_row & ",22,FALSE))"

Which i thought would "select" cell G2 on the appropriate sheet and then paste the formula. However, what i get is the formula pasted into a totally different cell. To get the formula pasted into G2 i actually need to have the following code.

 'For some reason i need the next statement otherwise the formulas are not put into the correct place
    Application.Goto Workbooks(Gradebook_Filename).Worksheets(Gradebook_Sht_Nm_Roster).Range("G2")
    
    'Insert/put HW formula
     Workbooks(Gradebook_Filename).Worksheets(Gradebook_Sht_Nm_Roster).Range("G2").Formula = _
                        "=IF(ISERROR(VLOOKUP(A2," & Gradebook_Sht_Nm_HW & "!$A$2:$V$" & _
                        Rster_lst_row & ",22,FALSE)),""NA"", " & "VLOOKUP(A2," & Gradebook_Sht_Nm_HW & "!$A$2:$V$" & _
                        Rster_lst_row & ",22,FALSE))"

Does anyone know why? I think its a "selection" issue but i am not sure.

DAvid

**davesexcel** · 12-26-2008, 10:18 AM

Try this code, change the names of course...

Sub InsertFormula()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim r As Range
    Dim f As String
    Set wb = Workbooks("11th Week.XLS")
    Set ws = wb.Worksheets("Sheet1")
    Set r = ws.Range("G1")
    f = "=Your Formula"
    r = f
End Sub

**welchs101** · 12-26-2008, 03:29 PM

thanks.......i will check it out. thanks.

So basically, i need to do a "set" first..........

**shg** · 12-26-2008, 04:30 PM

Your first construct looks fine to me. If you're using Excel 2007, you can shorten it to

Workbooks(Gradebook_Filename).Worksheets(Gradebook_Sht_Nm_Roster).Range("G2").Formula = _
   "=IFERROR(VLOOKUP(A2," & Gradebook_Sht_Nm_HW & "!$A$2:$V$" & Rster_lst_row & ",22,FALSE), NA())"

**welchs101** · 12-26-2008, 05:28 PM

Thanks. But for some reason i need the additional line of code with the "Application.goto" statement otherwise the formula is put into the wrong location. Cant figure out why.