Help with formula relative cell refernce

[HacH]

Hi,

Can somebody please help me? I have some vba code (below), which looks at all the blank cells in a selected range and populates them filling them upwards. This works fine, but I need to add an if statement to check that the ID of the cell being filled up is the same as the cell containing the value (hope that makes sense - please see code and excel sheet example below for details):

CODE:

Sub FillBlanks()

Dim rRange1 As Range, rRange2 As Range

Dim lReply As Integer

If Selection.Cells.Count = 1 Then

MsgBox "You must select your list and include the blank cells", vbInformation, "Mamoon Rashid"

Exit Sub

ElseIf Selection.Columns.Count > 1 Then

MsgBox "You must select only one column", vbInformation, "Mamoon Rashid"

Exit Sub

End If

Set rRange1 = Selection

On Error Resume Next

Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)

On Error GoTo 0

If rRange2 Is Nothing Then

MsgBox "No blank cells Found", vbInformation, "Mamoon Rashid"

Exit Sub

End If

rRange2.FormulaR1C1 = "=R[-1]C"

lReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "Mamoon Rashid")

If lReply = vbYes Then rRange1 = rRange1.Value

End Sub

----------------------END OF CODE

The spreadsheet is formatted as follows:

ID Grade Point ...
11
11 pb1 7 ..
12 3
12 ps2
12 ps3 2
34 9 ..
34
34 pt2 4
34
34 pt1 3
... ... ... ...

I need the code to also run with the follwing statement:

=if(A1=A2,B2,0) - If this formula was placed in cell B1 then it would take the value 'pb1'. Using the formula allows me to ensure that the records are being poulated for their relevant record ID and not randomly.

I am able to insert the IF statement into the code above but I don't know how to insert a relative cell reference so if I highlighted column H and the formula was being inserted into H9 it would always know to look in A9 for the ID and modify the formula as "-if(A9=A10, H10, 0)".

I hope the above makes sense - please let me know if anyone would like more explanation bacause I am finding it difficult to explain properly.

Thanks very much.

HacH

 

[Gavona]

Just to be clear please post the results that you expect.
In your example would ALL the blanks be filled? If so then post some sample data where the IF statement would have an effect.

but I don't know how to insert a relative cell reference

Well "=R[-1]C" is a relative address. Try adapting that, lookup help and post your attempt if you need further help.



Gavin

 

[HacH]

Hi,

Thanks for the reply. I would expect the following results, based on the data provided above:

ID Grade Point ...
11 pb1 7
11 pb1 7 ..
12 ps2 3
12 ps2 2
12 ps3 2
34 pt2 9 ..
34 pt2 4
34 pt2 4
34 pt1 3
34 pt1 3
... ... ... ...


In simple terms, I require need the macro/code to automate process of filling in blank cells upwards based on whether the ID fields are the same.

Please see also attached a real example, which uses different field titles but has the same process with the macro etc.

Thanks,

HacH

 

[Gavona]

What I did:
1. Opened your workbook
2. Started to record a macro
3. Clicked cell B1
4. Ctrl-Shift-[DownArrow]
5. Edit, GoTo,Special,Formulas
6. Your working formula for the Active Cell is displayed in the Formula bar
7. Click into the formula bar (I didn't actually need to change anything but at this stage I could have simply corrected the formula.
8. Ctrl-Enter to enter that formula into all the selected cells
9. Stop the macro recorder
10. Examine the macro:

Sub Macro1()
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.FormulaR1C1 = "=IF(RC[-2]=R[1]C[-2],R[1]C,"""")"
End Sub

Now in your solution you will need a different way to identify the range and will then use SpecialCells(xlCellTypeBlanks)

So I think the bit you are looking for is

Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-2]=R[1]C[-2],R[1]C,"""")"

The learning point is that you had the formula and only needed to use the recorder to get it into code!

Gavin