VBA Formula Adding Single Quotes to Result

[daboujibo]

Hello.

I apologize if this has been asked in the past, but I cannot find a question/answer to this particular situation. I may be using the wrong search criteria, so I apologize if there is already help for this type of situation.

I am trying to do the following:

1. Loop through a column from the bottom to the top to find the next empty cell.
2. Then if that is TRUE, I need to identify the next cell above it that is not empty.
3. Then I need to enter a formula into the blank cell found in #1
4. Then loop again until I reach cell B26 (will be B1 once I get this part working)

The reason for doing #1 from the last row to the top is that I need to find an empty cell, but then enter a formula that requires the next filled cell above that empty cell and will continue that way for each blank cell. Example is the table below for columns A and B. #1 should find cell B3 as the first empty cell, then do the calculation in B3 with the value in B1.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1.25
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2.25
[/TD]
[/TR]
</tbody>[/TABLE]


I'm having a problem with line below displaying the value "=SUM(VLOOKUP(Q30,Triggers,14,FALSE) *'B25'" instead of "=SUM(VLOOKUP(Q30,Triggers,14,FALSE) *B25". (Q30 changes as the code loops as does B25.) It is adding single quotes around the last cell reference in the formula.

.Cells(iRow, 2).Formula = "=SUM(VLOOKUP(RC[15],Triggers,14,FALSE)*B" & kCell & ")"

Is there something wrong with the syntax? Is there a better way to accomplish this? I can just run a find/replace function afterwards to remove the single quotes, but I was hoping there was a way to get the code to display what I want the first time.

Thank you to any help that you can provide. Have a great day!

~Chris

Sub TESTFindBlankRow()
    Dim iRow As Integer
    Dim kCell As Integer
    Dim lastRow As Integer 'Last Row with a value in column A
    
    With Worksheets("TestSheet")
           
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row   'This gives the last Row with a nonempty cell in column A
    
        For iRow = lastRow To 26 Step -1
                If IsEmpty(.Cells(iRow, 2)) Then
                    
                    kCell = .Cells(iRow, 2).End(xlUp).Row
                    .Cells(iRow, 2).Formula = "=SUM(VLOOKUP(RC[15],Triggers,14,FALSE))*B" & kCell & ")"
                    
                End If
        Next iRow
    
    End With
End Sub

 

[daboujibo]

Good day.

In addition, I'm working with Column C as well. However, instead of returning "...* 'C23'), it is returning "...* '$V:$V') as if I am asking for column 23 not column C in the reference. It only happens with C. I can imagine it would happen with column "R" as well but treat it as row instead of the column. Can someone help with that as well? Or should I post that as a separate question?

Thank you for your help.

~Chris

 

[Norie]

Chris

Your formula uses both A1 and R1C1 notation, you can't use both in the same formula.