Need VBA to insert OFFSET FORMULA relative to the inserted formula

[9tanstaafl9]

Hi. I've been out of practice with VBA for a few months and I can't remember how to do this...

My code searches through all the used cells in column D to find a blank cell (which only occurs on subtotal rows -- the actual subtotal descriptions are in column B which is hidden) . Then I use VBA to insert the description I want to show in this blank cell in col D.

I need the code to insert a formula that looks at the cell one row above and two rows to the left, but unfortunately, I have no idea what that cell will be at the time the code is inserting the formula. Is there a way to do this?

Here's what the formula would look like if I just typed it into field D196 in Excel:

 =VLOOKUP(B195,PhaseNameRange,2)

Here's what I have now (looking for the ????:

rng = Range(Range("b17"), Range("b" & Rows.Count).End(xlUp)).Rows.Count
Dim FormatVar As Range
Set FormatVar = Range("d17")
For i = 1 To rng
If IsEmpty(FormatVar) Then
    FormatVar.Value = "Phase " & FormatVar.Offset(0, -2) & " - " &  ????   
FormatVar.HorizontalAlignment = xlRight
    Set FormatVar = FormatVar.Offset(1, 0)
Else
Set FormatVar = FormatVar.Offset(1, 0)
End If
Next i

Please help, I've been working on this for hours

 

[DonkeyOte]

If D is truly blank (not Formula nulls) then

With Range(Cells(17,"B"),Cells(Rows.Count,"B").End(xlUp)).Offset(,2).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=""Phase ""&RC[-2]&"" - ""&R[-1]C[-2]"
    .HorizontalAlignment = xlRight
End With

 

[9tanstaafl9]

Thank you. Where exactly in the code I showed you woudl this go? What would it replace? I'm sorry for being dense, but I'm not that good at this, and had help with the original code and didn't completely understand it...

FYI I just now got your screen name. I was thinking you had misspelled oats or something. Boy I really am losing it today...

Jennifer

 

[DonkeyOte]

The intention was for the code to replace your code in it's entirety, ie direct replacement thereof.

It is as mentioned dependent upon fact that the values being identified in Col D are true blanks.

As for my handle, blame Rorya, I am the XL-artist formerly known as lasw10

 

[9tanstaafl9]

But your code doesn't have the lookup range (which is on another sheet) for PhaseNameRange. How would it work?

 

[9tanstaafl9]

That gave this result:

Phase 1 Total - 1

When it should be someting like:

Phase 1 Total - Jones Landscaping Project

 

[DonkeyOte]

I guess I couldn't quite follow all that was going on in the OP

On Error Resume Next
With Range(Cells(17, "B"), Cells(Rows.Count, "B").End(xlUp)).Offset(, 2).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=""Phase ""&RC[-2]&"" - ""&VLOOKUP(R[-1]C[-2],PhaseNameRange,2)"
    .HorizontalAlignment = xlRight
End With
On Error GoTo 0

 

[9tanstaafl9]

THAT WORKED PERFECTLY! Wow. Thank you.

This is so much fun once it works. I feel like when I was a kid doing cryptograms (who has time now...).

Can you tell me what the 17 stand for in the code? I'm trying to understand so I won't be such a pain in the neck in the future... If you have time.

Thanks again!
Jennifer

 

[DonkeyOte]

I used the 17 to create the first Cell in the B Column range - I took this from your code - ie assumed it to be first row of data

In basic terms

Cells(17,"B") or Cells(17,2)

is equiv. to

Range("B17")

Cells uses R1C1 reference style rather than the more traditional A1 style.