Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create formula in VBA
Hello
Sub makeformulae() Dim frng As Range Const sFormula As String = _ "=if(a9="""","""",if(isna(vlookup(a9,råb1,3,false) )," & _ """"",(vlookup(a9,råb1,3,false))))" Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng .Formula = sFormula .Value = .Value End With End Sub Does anybody know how I make this formula dynamic to go with the range - so that the a9-lookup is returned in o9 and the a10-lookup is returned in o10 etc. Any thoughts? Thanks in advance. /Heine |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create formula in VBA
What does it do now?
Try commenting the ".value = .value" line and look at the formula. And a followup question... You're using column O to determine the last cell to populate in column O. Did you actually want that? If you wanted to use column A: Set frng = Range("O9:O" & Cells(Rows.Count, "a").End(xlUp).Row) Heine wrote: Hello Sub makeformulae() Dim frng As Range Const sFormula As String = _ "=if(a9="""","""",if(isna(vlookup(a9,råb1,3,false) )," & _ """"",(vlookup(a9,råb1,3,false))))" Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng .Formula = sFormula .Value = .Value End With End Sub Does anybody know how I make this formula dynamic to go with the range - so that the a9-lookup is returned in o9 and the a10-lookup is returned in o10 etc. Any thoughts? Thanks in advance. /Heine -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create formula in VBA
Hi Dave, it performs the formula the right way now. I just forgot to name a range. My mistake. Regarding the column O and A you are probably right. I changed it to A. However it seems to work no matter what letter I put. One thing I don´t understand though: What exactly in the code makes it dynamic - that is the lookup in a10 returns to O10 and a11 to O11 etc - because I only put a9 to O9 and nothing else. /Heine Dave Peterson wrote: What does it do now? Try commenting the ".value = .value" line and look at the formula. And a followup question... You're using column O to determine the last cell to populate in column O. Did you actually want that? If you wanted to use column A: Set frng = Range("O9:O" & Cells(Rows.Count, "a").End(xlUp).Row) Heine wrote: Hello Sub makeformulae() Dim frng As Range Const sFormula As String = _ "=if(a9="""","""",if(isna(vlookup(a9,råb1,3,false) )," & _ """"",(vlookup(a9,råb1,3,false))))" Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng .Formula = sFormula .Value = .Value End With End Sub Does anybody know how I make this formula dynamic to go with the range - so that the a9-lookup is returned in o9 and the a10-lookup is returned in o10 etc. Any thoughts? Thanks in advance. /Heine -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create formula in VBA
It's a feature of Excel. When you enter a formula in a contiguous range,
Excel adjusts each instance relative to its position. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Heine" wrote in message ps.com... Hi Dave, it performs the formula the right way now. I just forgot to name a range. My mistake. Regarding the column O and A you are probably right. I changed it to A. However it seems to work no matter what letter I put. One thing I don´t understand though: What exactly in the code makes it dynamic - that is the lookup in a10 returns to O10 and a11 to O11 etc - because I only put a9 to O9 and nothing else. /Heine Dave Peterson wrote: What does it do now? Try commenting the ".value = .value" line and look at the formula. And a followup question... You're using column O to determine the last cell to populate in column O. Did you actually want that? If you wanted to use column A: Set frng = Range("O9:O" & Cells(Rows.Count, "a").End(xlUp).Row) Heine wrote: Hello Sub makeformulae() Dim frng As Range Const sFormula As String = _ "=if(a9="""","""",if(isna(vlookup(a9,råb1,3,false) )," & _ """"",(vlookup(a9,råb1,3,false))))" Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng .Formula = sFormula .Value = .Value End With End Sub Does anybody know how I make this formula dynamic to go with the range - so that the a9-lookup is returned in o9 and the a10-lookup is returned in o10 etc. Any thoughts? Thanks in advance. /Heine -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create formula in VBA
Just to add to Bob's response...
Try this in a test worksheet. Select B1:B20 (about) With B1 the activecell, type this: =A1 Now look at the formulas in the other cells. VBA does the same thing. Heine wrote: Hi Dave, it performs the formula the right way now. I just forgot to name a range. My mistake. Regarding the column O and A you are probably right. I changed it to A. However it seems to work no matter what letter I put. One thing I don´t understand though: What exactly in the code makes it dynamic - that is the lookup in a10 returns to O10 and a11 to O11 etc - because I only put a9 to O9 and nothing else. /Heine Dave Peterson wrote: What does it do now? Try commenting the ".value = .value" line and look at the formula. And a followup question... You're using column O to determine the last cell to populate in column O. Did you actually want that? If you wanted to use column A: Set frng = Range("O9:O" & Cells(Rows.Count, "a").End(xlUp).Row) Heine wrote: Hello Sub makeformulae() Dim frng As Range Const sFormula As String = _ "=if(a9="""","""",if(isna(vlookup(a9,råb1,3,false) )," & _ """"",(vlookup(a9,råb1,3,false))))" Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng .Formula = sFormula .Value = .Value End With End Sub Does anybody know how I make this formula dynamic to go with the range - so that the a9-lookup is returned in o9 and the a10-lookup is returned in o10 etc. Any thoughts? Thanks in advance. /Heine -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create formula in VBA
I forgot an important step:
Just to add to Bob's response... Try this in a test worksheet. Select B1:B20 (about) With B1 the activecell, type this: =A1 And hit ctrl-enter instead of enter. <--- Added! Now look at the formulas in the other cells. VBA does the same thing. Heine wrote: Hi Dave, it performs the formula the right way now. I just forgot to name a range. My mistake. Regarding the column O and A you are probably right. I changed it to A. However it seems to work no matter what letter I put. One thing I don´t understand though: What exactly in the code makes it dynamic - that is the lookup in a10 returns to O10 and a11 to O11 etc - because I only put a9 to O9 and nothing else. /Heine Dave Peterson wrote: What does it do now? Try commenting the ".value = .value" line and look at the formula. And a followup question... You're using column O to determine the last cell to populate in column O. Did you actually want that? If you wanted to use column A: Set frng = Range("O9:O" & Cells(Rows.Count, "a").End(xlUp).Row) Heine wrote: Hello Sub makeformulae() Dim frng As Range Const sFormula As String = _ "=if(a9="""","""",if(isna(vlookup(a9,råb1,3,false) )," & _ """"",(vlookup(a9,råb1,3,false))))" Sheets("Bogholderi").Select Set frng = Range("O9:O" & Cells(Rows.Count, "O").End(xlUp).Row) With frng .Formula = sFormula .Value = .Value End With End Sub Does anybody know how I make this formula dynamic to go with the range - so that the a9-lookup is returned in o9 and the a10-lookup is returned in o10 etc. Any thoughts? Thanks in advance. /Heine -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a certain type of Excel formula? | Excel Worksheet Functions | |||
How do I create a formula by percentage within a formula? | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
How do I create a 26 'tier' IF formula? | Excel Discussion (Misc queries) | |||
How to create specific formula | Excel Worksheet Functions |