Insert Formula in Range
 

I'm trying to insert the formula shown in CODE 2 below into cells C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the formula
in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

Insert Formula in Range
 

On 12¿ù13ÀÏ, ¿ÀÀü10½Ã38ºÐ, "scott" wrote:
I'm trying to insert the formula shown in CODE 2 below into cells C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the formula
in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

not c.Value , try c.Formula="=blahblah~"

HTH
sjoo

Insert Formula in Range
 

thanks, but how do I make the formula's relative cell reference increment?

wrote in message
...
On 12¿ù13ÀÏ, ¿ÀÀü10½Ã38ºÐ, "scott" wrote:
I'm trying to insert the formula shown in CODE 2 below into cells C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on
until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the
formula
in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

not c.Value , try c.Formula="=blahblah~"

HTH
sjoo

Insert Formula in Range
 

Hi Scott

Option Explicit

Sub InsertFormula()

Dim c As Range
Dim i As Integer

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
i = c.Row
c.Formula = "=IF(ISNA(VLOOKUP(A" & i & ",DataRange,1,FALSE)),"""",A"
& i & ")"

Set c = c.Offset(1, 0)

Loop

End Sub

Regards

Per

"scott" skrev i en meddelelse
...
thanks, but how do I make the formula's relative cell reference increment?

wrote in message
...
On 12¿ù13ÀÏ, ¿ÀÀü10½Ã38ºÐ, "scott" wrote:
I'm trying to insert the formula shown in CODE 2 below into cells C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed
in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on
until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the
formula
in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

not c.Value , try c.Formula="=blahblah~"

HTH
sjoo

Insert Formula in Range
 

This is why I like R1C1 referencing in VBA:

If it's a named range as you say:

Sub InsertFormula()
Range("C3:C13").FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC1,DataRange,1,FALSE)),"""",RC1 )"
' Add your named range in place of C3:C13
End Sub

or to loop:

Sub InsertFormula()
Dim c As Range
Set c = ActiveSheet.Range("C3")
Do While c.Offset(0, -2).Value < ""
c.FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC1,DataRange,1,FALSE)),"""",RC1 )"
Set c = c.Offset(1, 0)
Loop
End Sub



--
Tim Zych
SF, CA
"scott" wrote in message
...
I'm trying to insert the formula shown in CODE 2 below into cells C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on
until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the
formula in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

Insert Formula in Range
 

Another way is to just populate the whole range in one assignment:

Option Explicit
Sub InsertFormula2()

Dim myRng As Range
Dim LastRow As Long


With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("C3:C" & LastRow)
End With

myRng.Formula = "=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"""", A3)"

End Sub

But I wouldn't use =vlookup() to check for a match.

I'd use a single column named range and a formula like:

=if(isnumber(match(a3,datarangecol1,0)),a3,"")

(Remember to double up your double quotes if you're going to use that formula in
your code.)

scott wrote:

I'm trying to insert the formula shown in CODE 2 below into cells C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the formula
in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

--

Dave Peterson

Insert Formula in Range
 

thank you, thank you!

i appreciate that and will learn from it.

"Per Jessen" wrote in message
...
Hi Scott

Option Explicit

Sub InsertFormula()

Dim c As Range
Dim i As Integer

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
i = c.Row
c.Formula = "=IF(ISNA(VLOOKUP(A" & i &
",DataRange,1,FALSE)),"""",A" & i & ")"

Set c = c.Offset(1, 0)

Loop

End Sub

Regards

Per

"scott" skrev i en meddelelse
...
thanks, but how do I make the formula's relative cell reference
increment?

wrote in message
...
On 12¿ù13ÀÏ, ¿ÀÀü10½Ã38ºÐ, "scott" wrote:
I'm trying to insert the formula shown in CODE 2 below into cells
C3:C13.
C3:C13 is a named range that always will be constantly growing, so my
function
InsertFormula() in CODE 1 should handle the range expanding dilema.

My question is syntax with the formula. CODE 2 shows the formula needed
in
C3. However, when my function loops to the C4 cell, the formula being
inserted needs to change from using A3 cell to the A4 cell, and so on
until
the function fills my range will the formula.

Can someone help me modify my InsertFormula() function to insert the
formula
in each cell while incrementing the relative cell reference?

CODE 1:

Sub InsertFormula()

Dim c As Range

Set c = ActiveSheet.Range("C3")

Do While c.Offset(0, -2).Value < ""
c.Value = xxxxxx ' Should be my formula
Set c = c.Offset(1, 0)
Loop

End Sub

CODE 2:

=IF(ISNA(VLOOKUP(A3,DataRange,1,FALSE)),"",A3)

not c.Value , try c.Formula="=blahblah~"

HTH
sjoo