VBA Help: Add formula depending if other cells contain data

[ag1214]

Hi,

I'm currently producing a pivot table and I'm trying to write a macro for the data that coincides with it.

I have three sheets, Sheet1(PivotTable), Sheet2(Data) and Sheet3(Mapping)

Sheet2 uses a couple of inputs to drag data from Sheet3. What I want to do is write a macro that will write the formula in to every row where column A (which I'm using for ID numbers) is not blank. The data starts at row 7 so the macro can't include rows above that. So for example, cell B7 uses the formula:

=LOOKUP(A7,Mapping!B:B,Mapping!C:C)

So using a macro, if there is no number in column A then column B will be blank, but if a number is added then the formula will enter. As I'm pretty new to VBA I'm not sure how to approach this. Is anyone able to help?

 

[JustynaMK]

Hi,

Try to use something like that:

Sub LookupMapping()
    Dim lngLastRow          As Long
    Dim lngRow              As Long
    
    With Sheets("Data")
        'count the number of rows in Sheet "Data"
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'start with row no 7
        For lngRow = 7 To lngLastRow
            'if cell in column A is not empty then add Lookup formula in column B
            If Not IsEmpty(.Range("A" & lngRow)) Then
                .Range("B" & lngRow).Formula = "=LOOKUP(A" & lngRow & ",Mapping!B:B,Mapping!C:C)"
            End If
        Next lngRow
    End With


End Sub

Let me know if that works for you.

Best regards,
Justyna

 

[ag1214]

Hi Justyna,

Thanks. That works great for adding in the formulas, plus its easy to adapt for other columns in the table.

Is there any easy way of clearing the formulas if the data in column A is then deleted? Just so that any "#N/A" results won't interfere with the pivot table.

Thanks Again.

 

[JustynaMK]

Glad it worked!

Maybe try adjusting your LOOKUP formula to tackle any future changes, like:

.Range("B" & lngRow).Formula = "=IFERROR(LOOKUP(A" & lngRow & ",Mapping!B:B,Mapping!C:C),"""")"
or:
.Range("B" & lngRow).Formula = "=IF(LEN(A" & lngRow & "),LOOKUP(A" & lngRow & ",Mapping!B:B,Mapping!C:C),"""")"

Does it work for you?

 

[ag1214]

I was more hoping for something that would remove the formula, maybe deleting the row would work (which would prevent gaps in the table if earlier entries need removing).

If that's not possible then that solution you suggested will probably work best.

 

[JustynaMK]

Ahh okay. Try something like that for deleting rows:

Sub CleanLookup()
    Dim lngLastRow          As Long
    Dim lngRow              As Long
    
    With Sheets("Data")
        'count the number of rows in Sheet "Data"
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'start with the last row
        For lngRow = lngLastRow To 7 Step -1
            'if cell in column A is empty then delete the whole row
            If IsEmpty(.Range("A" & lngRow)) Then
                .Rows(lngRow).Delete
            End If
        Next lngRow
    End With


End Sub

...or this one for simply removing the formula:

Sub CleanLookup()
    Dim lngLastRow          As Long
    Dim lngRow              As Long
    
    With Sheets("Data")
        'count the number of rows in Sheet "Data"
        lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'start with the last row
        For lngRow = lngLastRow To 7 Step -1
            'if cell in column A is empty then remove formula in column B
            If IsEmpty(.Range("A" & lngRow)) Then
                .Range("B" & lngRow).Clear
            End If
        Next lngRow
    End With


End Sub

 

[ag1214]

These both seem to work.

Again, thanks for the help.

 

[JustynaMK]

Cool, glad it works! Take care