entering a formula via a macro

[jaypatel]

Hi,

how would you a macro that entered say a vlookup formula in column K from rows 1 through to 65536.

The problem i have is that it should check whether column d is blank or not.....

eg
if cell d1= blank then do not enter formula, but check cell d2, d3, d4 etc
if cell d2= text or number then enter in cell K2 (the row numbers will need to correspond) the vlookup formula etc

Jay

 

[TryingToLearn]

awkward with an entire column, but if this is what you mean it will work as a formula in column K

=IF($D1="","",VLOOKUP(stuff)

 

[parry]

Hi Jay heres an example. The vlookup is just one I made up. If you advise what your Vlookup is for say K2 then I can amend this for you.

AddFormula()
Dim Rng As Range, c

'Set range for column D
Set Rng = Range("D1:D" & Range("D65536").End(xlUp).Row)

'If column D value is not blank then add formula in Column K
For Each c In Rng
If Not IsEmpty(c) Then
c.Offset(0, 7).Formula = "=VLOOKUP(4,A1:B10,1,FALSE)"
End If
Next c

End Sub

EDIT: You can of course have a formula in every row in K but return an empty string for the result if D is blank so the result in K looks blank. That way you wont need a macro at all.

 

[tusharm]

Turn on the macro recorder (Tools | Macro > Record new macro...), enter the formula by hand, and turn off the macro recorder. XL will give you the necessary syntax and starter code. You can then streamline and generalize the code using the ideas in Excel | VBA | 'Beyond Excels Recorder' page of my web site.

Hi,

how would you a macro that entered say a vlookup formula in column K from rows 1 through to 65536.

The problem i have is that it should check whether column d is blank or not.....

eg
if cell d1= blank then do not enter formula, but check cell d2, d3, d4 etc
if cell d2= text or number then enter in cell K2 (the row numbers will need to correspond) the vlookup formula etc

Jay

 

[jaypatel]

Thanks Parry.... it works a treat....

Say if i wanted to enter the CONCATENATE formula in column E for all the rows and miss out the blank cells....

so,
=CONCATENATE(a1,b1,c1) in cell e1
=CONCATENATE(a2,b2,c2) in cell e2 etc

and if cell a3 and b3 and c3 is blank then enter no formula in cell e3 etc

Jay

 

[Legacy 21301]

Thanks Parry.... it works a treat....

Say if i wanted to enter the CONCATENATE formula in column E for all the rows and miss out the blank cells....

so,
=CONCATENATE(a1,b1,c1) in cell e1
=CONCATENATE(a2,b2,c2) in cell e2 etc

and if cell a3 and b3 and c3 is blank then enter no formula in cell e3 etc

Jay

All that is required is to follow Tusharm's suggestion.