Using a negative loop number in VBA

0
votes

I have a for loop, using i as the counter, in Excel VBA. I have one statement that stubbornly gives me errors:

ActiveCell.FormulaR1C1 = "=VLookup(RC[-3],R3C7:R22C15,3)" & " & " & "R[-i]C" & " _ 

& " & "Vlookup(RC[-3],R3C7:R22C15,4)"

Using the -i is evidently causing the errors. I tried adding negi=-i and then changing the R[-i]C to R[negi]C, but that didn't fix it. I added a Dim negi as Integer statement in earlier code.

Edit: Here's more of the code. I'm using two loops. rownumber is the counter for the outer loop, and i is the counter for the inner loop. i ranges from 1 to 20, and rownumber ranges until a row is reached with a blank in column 3.

Range("A25").Select
Dim Rownumber As Integer
Dim i As Integer
Dim negi As Integer
Rownumber = 1

' This starts the outer loop Do While ActiveCell.Offset(0, 3) <> "" ' Adds twenty rows ActiveCell.Offset(1, 0).Select

Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(19, 5)).Select
Selection.Insert Shift:=xlDown

ActiveCell.Offset(-1, 0).Select

For i = 1 To 20

ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = i
ActiveCell.Offset(0, 1).Select
 ActiveCell.Value = Rownumber
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=Vlookup(RC[-2],R3C7:R22C15,2)"
    ActiveCell.Offset(0, 1).Select
    negi = -i
    ActiveCell.FormulaR1C1 = "=VLookup(RC[-3],R3C7:R22C15,3)" & " & " & _
     "R[negi]C" & " & "& "Vlookup(RC[-3],R3C7:R22C15,4)"

    ....
    rownumber = rownumber + 1
    next i

How can I accomplish this while avoiding errors?

2
excelvbafor-loopcounter
What is the value of i when it gives you an erro? What is the error? Is it possible that the cell you're changing is, say, A1, and you are telling it to go to A0 [which does not exist]? - Grade 'Eh' Bacon
Provide complete code sample you're using. We can't read in your mind or direct from your screen. - Maciej Los
Edited to add more code in response to comments. - Steven8322
@Grade 'Eh" Bacon - the value of i is 1 when I get the error. The error I get is Run time error '1004': Application-defined or object-defined error - Steven8322

2 Answers

1
votes

There are a couple of bad string concatenations in the formula build.

i will have to be outside of the quoted string(s) and concatenated in and & " & " & probably isn;t doing what you want it to.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],R3C7:R22C15,3)&" & _
                                   """ & ""&R[-" & i & "]C&"" & ""&" & _
                                   "VLOOKUP(RC[-3],R3C7:R22C15,4)"

Remember that you have to double up quotes within a quoted string.

0
votes

You can do a loop from a greater value to a lower value. This will loop from the last cell used to the first.

lRow = ws.UsedRange.Rows.count
Do While lRow > 0
    lCol = ws.UsedRange.Columns.count
    Do While lCol > 0
        If InStr(ws.Cells(lRow, lCol), job) Then

        End If
        lCol = lCol - 1
    Loop

    lRow = lRow - 1
    ws.Range("A" & lRow).Activate
Loop