Help Converting excel formula to vba loop

[cmw1229]

Here is the original excel code (from 1 cell) this was copied across multiple rows and columns.

=IF(ISERROR(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"",IF(ISBLANK(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"",XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)/(100/'Instructions and Setup'!E3)))

Here is the vba code I have so far but I am having issues making them relative to move and fill in the data.

Dim i As Integer, j As Integer, ilast As Integer, jlast As Integer
    Sheets("USATP Gradebook").Select
          jlast = Application.WorksheetFunction.CountIf(Range("A6:A705"), "<>") + 1     'The end point is variable
    Sheets("Instructions and Setup").Select
          ilast = Application.WorksheetFunction.CountIf(Range("G3:G53"), "<>") + 1    'The end point is variable
   
    Sheets("Calculation").Visible = True
    Sheets("Calculation").Select
    For i = 1 To ilast
      For j = 1 To jlast
            Range("B5").Select           'Starting point would love to have in (j,i) format'
            ActiveCell = "*" & ActiveWorkbook.Sheets("Instructions and Setup").Range("G3").Value & "*"
            ActiveCell.Offset(2, 0).Select
            ActiveCell.FormulaR1C1 = "=IF(ISERROR(XLOOKUP('Instructions and Setup'!R[-4]C[4],'USATP Gradebook'!R[-2],'USATP Gradebook'!R[-1],,2)),"""",IF(ISBLANK(XLOOKUP('Instructions and Setup'!R[-4]C[4],'USATP Gradebook'!R[-2],'USATP Gradebook'!R[-1],,2)),"""",XLOOKUP('Instructions and Setup'!R[-4]C[4],'USATP Gradebook'!R[-2],'USATP Gradebook'!R[-1],,2)/(100/'Instructions and Setup'!R[-4]C[3])))"
   
       
        Next j
    Next i

 

[Natas]

Not quite understanding what you are trying to do with "ilast" and "jlast" variables. I'm guessing you want to both to define how far out to stretch your formula.
I think a autofill formula would work better for what you are trying to do.
Swap jlast and ilast depending on which you were expecting to be your rows or columns.
You don't have to use a FormulaR1C1 format for your formula as long as you do your double quotations like I did below. Don't worry, this will be fixed with it is copied into the cell.
Then Cell is autofilled to your deminsion defined by ilast x jlast.
Adjust your absolute references in your formula until you get it to autofill correctly.

Edit: I wrapped your formula so it is more readable in the code. But if you don't understand what I did here it is in one line that you can paste over what I did in your code.

    .Cells(7, 2).Value = "=IF(ISERROR(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"""",IF(ISBLANK(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"""",XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)/(100/'Instructions and Setup'!E3)))"

Dim i As Integer, j As Integer, ilast As Integer, jlast As Integer
'Sheets("USATP Gradebook").Select
'      jlast = Application.WorksheetFunction.CountIf(Range("A6:A705"), "<>") + 1     'The end point is variable
jlast = Application.WorksheetFunction.CountIf(Sheets("USATP Gradebook").Range("A6:A705"), "<>") + 1     'The end point is variable
'Sheets("Instructions and Setup").Select
ilast = Application.WorksheetFunction.CountIf(Sheets("Instructions and Setup").Range("G3:G53"), "<>") + 1    'The end point is variable

Sheets("Calculation").Visible = True
Sheets("Calculation").Activate
With ActiveSheet
    .Cells(5, 2).Value = "*" & ActiveWorkbook.Sheets("Instructions and Setup").Range("G3").Value & "*"
    .Cells(7, 2).Value = "=IF(ISERROR(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),""""," _
        & "IF(ISBLANK(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"""",XLOOKUP" _
        & "('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)/(100/'Instructions and Setup'!E3)))"
    .Cells(7, 2).AutoFill Destination:=.Range(.Cells(7, 2), .Cells(ilast, 2)), Type:=xlFillDefault
    .Range(.Cells(7, 2), .Cells(ilast, 2)).AutoFill Destination:=.Range(.Cells(7, 2), .Cells(ilast, jlast)), Type:=xlFillDefault
End With

 

[cmw1229]

Not quite understanding what you are trying to do with "ilast" and "jlast" variables. I'm guessing you want to both to define how far out to stretch your formula.
I think a autofill formula would work better for what you are trying to do.
Swap jlast and ilast depending on which you were expecting to be your rows or columns.
You don't have to use a FormulaR1C1 format for your formula as long as you do your double quotations like I did below. Don't worry, this will be fixed with it is copied into the cell.
Then Cell is autofilled to your deminsion defined by ilast x jlast.
Adjust your absolute references in your formula until you get it to autofill correctly.

Edit: I wrapped your formula so it is more readable in the code. But if you don't understand what I did here it is in one line that you can paste over what I did in your code.

    .Cells(7, 2).Value = "=IF(ISERROR(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"""",IF(ISBLANK(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"""",XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)/(100/'Instructions and Setup'!E3)))"

Dim i As Integer, j As Integer, ilast As Integer, jlast As Integer
'Sheets("USATP Gradebook").Select
'      jlast = Application.WorksheetFunction.CountIf(Range("A6:A705"), "<>") + 1     'The end point is variable
jlast = Application.WorksheetFunction.CountIf(Sheets("USATP Gradebook").Range("A6:A705"), "<>") + 1     'The end point is variable
'Sheets("Instructions and Setup").Select
ilast = Application.WorksheetFunction.CountIf(Sheets("Instructions and Setup").Range("G3:G53"), "<>") + 1    'The end point is variable

Sheets("Calculation").Visible = True
Sheets("Calculation").Activate
With ActiveSheet
    .Cells(5, 2).Value = "*" & ActiveWorkbook.Sheets("Instructions and Setup").Range("G3").Value & "*"
    .Cells(7, 2).Value = "=IF(ISERROR(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),""""," _
        & "IF(ISBLANK(XLOOKUP('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)),"""",XLOOKUP" _
        & "('Instructions and Setup'!F3,'USATP Gradebook'!$5:$5,'USATP Gradebook'!6:6,,2)/(100/'Instructions and Setup'!E3)))"
    .Cells(7, 2).AutoFill Destination:=.Range(.Cells(7, 2), .Cells(ilast, 2)), Type:=xlFillDefault
    .Range(.Cells(7, 2), .Cells(ilast, 2)).AutoFill Destination:=.Range(.Cells(7, 2), .Cells(ilast, jlast)), Type:=xlFillDefault
End With

Yes I ilast and jlast were to define the end of my loop (or auto fill) because it will change.
My i's and j's are backward from normal. Right now I had my i's as columns and j's as rows (only because I typed the wrong range and it was easier to rename the variables)


With your code I am getting an error on the autfill line (third row from bottom)

 

[cmw1229]

 

[cmw1229]

I fixed the errors. Now the dilemma:
As I copy to columns I need the Green highlighted cells to increase in rows instead of the traditional columns.
the Yellow highlighted piece will change as normal.

For example in Cell A1 it would reference another sheets G1
Then Cell B1 would Reference cell G2
C1 -> G3 ect.


Then copy Row 1 down to the last row.

 

[Natas]

Not sure I'm following your mention of Green and yellow highlighted cells. Please provide more info.