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

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

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

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

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

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