Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing conditional formatting formula, arrow keys insert range. | Excel Discussion (Misc queries) | |||
Macro to insert a formula based on a range | Excel Discussion (Misc queries) | |||
Insert SUM Formula using VBA (Range Varies) | Excel Programming | |||
Macro to insert formula result into range with zero values in cell | Excel Programming | |||
How to insert formula to a range of cells from VBA? | Excel Programming |