Populate a formula with macro

G

Greg Snidow

Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate a
formula in cell A17, and drag it down to the last cell in the range. This is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the formula
in A17, but I can not get the macro to populate it for me. I am not sure of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
 
D

Don Guillett

Modify this idea to suit
Sub putformula()
lr=[D2000].End(xlUp).Row - 1).
Range("d2:d" & lr).Formula = "=vlookup(E2,$A$2:$D$22,2)"
End Sub
 
B

Bernie Deitrick

Sub PopulateFormula2()

'Populate the formula to all cells
Range("A17:A" & [D2000].End(xlUp).Row - 1).Formula = _
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2))," & _
"""NOT LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"

End Sub
 
P

PCLIVE

Using your code, but changing to just Formula and NOT FormulaR1C1, I got
this in A17.

=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),"NOT
LISTED",VLOOKUP(C17,Region!A$2:B$98,2))

However, the FillDown line did not work. I'm not sure what "[D2000]" is
suppose to be. If that is suppose to be Range D2000, then try this:

Sub PopulateFormula()

Range("A17").Formula =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
Range("A17:A" & Range("D2000").End(xlUp).Row - 1).FillDown

End Sub


HTH,
Paul
 
G

Greg Snidow

Thanks Don. I actually got it to work by changing FormulaR1C1 to just
Formula, as was indicated in all three posts. Not sure where or why to use
R1C1.

Don Guillett said:
Modify this idea to suit
Sub putformula()
lr=[D2000].End(xlUp).Row - 1).
Range("d2:d" & lr).Formula = "=vlookup(E2,$A$2:$D$22,2)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Greg Snidow said:
Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate
a
formula in cell A17, and drag it down to the last cell in the range. This
is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the
formula
in A17, but I can not get the macro to populate it for me. I am not sure
of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the
internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
 
G

Greg Snidow

Not sure why the fill down did not work, it works in my sheet. The D2000 is
used because the data in column D is the last row of the sheet, and I want
the formula in A to drag down to that row.

PCLIVE said:
Using your code, but changing to just Formula and NOT FormulaR1C1, I got
this in A17.

=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),"NOT
LISTED",VLOOKUP(C17,Region!A$2:B$98,2))

However, the FillDown line did not work. I'm not sure what "[D2000]" is
suppose to be. If that is suppose to be Range D2000, then try this:

Sub PopulateFormula()

Range("A17").Formula =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
Range("A17:A" & Range("D2000").End(xlUp).Row - 1).FillDown

End Sub


HTH,
Paul

--

Greg Snidow said:
Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate
a
formula in cell A17, and drag it down to the last cell in the range. This
is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the
formula
in A17, but I can not get the macro to populate it for me. I am not sure
of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the
internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
 
D

Don Guillett

r1c1 is used with r1c1 style. I rarely, as in almost never, use it.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Greg Snidow said:
Thanks Don. I actually got it to work by changing FormulaR1C1 to just
Formula, as was indicated in all three posts. Not sure where or why to
use
R1C1.

Don Guillett said:
Modify this idea to suit
Sub putformula()
lr=[D2000].End(xlUp).Row - 1).
Range("d2:d" & lr).Formula = "=vlookup(E2,$A$2:$D$22,2)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Greg Snidow said:
Greetings all. I know this exact question has been answered many times
in
past posts, but I can not get it to work for anything. I need to
populate
a
formula in cell A17, and drag it down to the last cell in the range.
This
is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the
formula
in A17, but I can not get the macro to populate it for me. I am not
sure
of
the difference between Formula and FormulaR1C1, but I have tried both,
and
neither work. I saw in a recent post that I need to double up the
internal
quotes, so I did to no avail. I am sure it is something simple, but I
can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
Click to expand...
 
B

Bernie Deitrick

R1C1 style is useful when you want to dynamically create a formula, since you don't need to figure
out the relative cell addresses, like this

ActiveCell.FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"

which gives this, when the activecell is G9

=F9-F8

HTH,
Bernie
MS Excel MVP


Greg Snidow said:
Thanks Don. I actually got it to work by changing FormulaR1C1 to just
Formula, as was indicated in all three posts. Not sure where or why to use
R1C1.

Don Guillett said:
Modify this idea to suit
Sub putformula()
lr=[D2000].End(xlUp).Row - 1).
Range("d2:d" & lr).Formula = "=vlookup(E2,$A$2:$D$22,2)"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Greg Snidow said:
Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate
a
formula in cell A17, and drag it down to the last cell in the range. This
is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the
formula
in A17, but I can not get the macro to populate it for me. I am not sure
of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the
internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
Click to expand...
 
L

Lusy

Hi Bernie, I'm having a problem with my macro. It should drag down a formula
in my Sheet 2 cell A2, all the way down until the last active cell. The
formula of the cell A2 wraps cell A2, B2, C2 from sheet 1. At this point is
doing it but the problem is that only copies down the formula and not drag it
down, can you help me out to make it work please?

Thanks,
L

******************************************************
Sub DragFormula()

Dim intLastRowInSummary As Integer

Sheets("Sheet1").Select
Range("A2").Select
intLastRowInSummary = ActiveCell.Row
Do
intLastRowInSummary = intLastRowInSummary + 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

Sheets("Sheet2").Select
Range("A2").Select
Do
ActiveCell.Formula =
"=Sheet1!A2&char(10)&Sheet1!B2&char(10)&Sheet1!C2"
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Row = intLastRowInSummary

End Sub
****************************************************


Bernie Deitrick said:
Sub PopulateFormula2()

'Populate the formula to all cells
Range("A17:A" & [D2000].End(xlUp).Row - 1).Formula = _
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2))," & _
"""NOT LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"

End Sub


--
HTH,
Bernie
MS Excel MVP


Greg Snidow said:
Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate a
formula in cell A17, and drag it down to the last cell in the range. This is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the formula
in A17, but I can not get the macro to populate it for me. I am not sure of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
 
B

Bernie Deitrick

Lusy,

No need to loop:

Sub DragFormula2()
With Sheets("Sheet2").Range("A2:A" & Sheets("Sheet1").Range("A2").End(xlDown).Row)
.Formula = "=Sheet1!A2&char(10)&Sheet1!B2&char(10)&Sheet1!C2"
.WrapText = True
End With
End Sub


--
HTH,
Bernie
MS Excel MVP


Lusy said:
Hi Bernie, I'm having a problem with my macro. It should drag down a formula
in my Sheet 2 cell A2, all the way down until the last active cell. The
formula of the cell A2 wraps cell A2, B2, C2 from sheet 1. At this point is
doing it but the problem is that only copies down the formula and not drag it
down, can you help me out to make it work please?

Thanks,
L

******************************************************
Sub DragFormula()

Dim intLastRowInSummary As Integer

Sheets("Sheet1").Select
Range("A2").Select
intLastRowInSummary = ActiveCell.Row
Do
intLastRowInSummary = intLastRowInSummary + 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

Sheets("Sheet2").Select
Range("A2").Select
Do
ActiveCell.Formula =
"=Sheet1!A2&char(10)&Sheet1!B2&char(10)&Sheet1!C2"
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Row = intLastRowInSummary

End Sub
****************************************************


Bernie Deitrick said:
Sub PopulateFormula2()

'Populate the formula to all cells
Range("A17:A" & [D2000].End(xlUp).Row - 1).Formula = _
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2))," & _
"""NOT LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"

End Sub


--
HTH,
Bernie
MS Excel MVP


Greg Snidow said:
Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate a
formula in cell A17, and drag it down to the last cell in the range. This is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the formula
in A17, but I can not get the macro to populate it for me. I am not sure of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
Click to expand...
 
L

Lusy

great!
it works fine!
Thank you so much!
Lusy

Bernie Deitrick said:
Lusy,

No need to loop:

Sub DragFormula2()
With Sheets("Sheet2").Range("A2:A" & Sheets("Sheet1").Range("A2").End(xlDown).Row)
.Formula = "=Sheet1!A2&char(10)&Sheet1!B2&char(10)&Sheet1!C2"
.WrapText = True
End With
End Sub


--
HTH,
Bernie
MS Excel MVP


Lusy said:
Hi Bernie, I'm having a problem with my macro. It should drag down a formula
in my Sheet 2 cell A2, all the way down until the last active cell. The
formula of the cell A2 wraps cell A2, B2, C2 from sheet 1. At this point is
doing it but the problem is that only copies down the formula and not drag it
down, can you help me out to make it work please?

Thanks,
L

******************************************************
Sub DragFormula()

Dim intLastRowInSummary As Integer

Sheets("Sheet1").Select
Range("A2").Select
intLastRowInSummary = ActiveCell.Row
Do
intLastRowInSummary = intLastRowInSummary + 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

Sheets("Sheet2").Select
Range("A2").Select
Do
ActiveCell.Formula =
"=Sheet1!A2&char(10)&Sheet1!B2&char(10)&Sheet1!C2"
ActiveCell.Offset(1, 0).Select

Loop Until ActiveCell.Row = intLastRowInSummary

End Sub
****************************************************


Bernie Deitrick said:
Sub PopulateFormula2()

'Populate the formula to all cells
Range("A17:A" & [D2000].End(xlUp).Row - 1).Formula = _
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2))," & _
"""NOT LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"

End Sub


--
HTH,
Bernie
MS Excel MVP


Greetings all. I know this exact question has been answered many times in
past posts, but I can not get it to work for anything. I need to populate a
formula in cell A17, and drag it down to the last cell in the range. This is
what I am trying:

*******************************************************
Sub PopulateFormula()

'First populate the formula in the top cell
Range("A17").FormulaR1C1 =
"=IF(ISERROR(VLOOKUP(C17,Region!A$2:B$98,2)),""NOT
LISTED"",VLOOKUP(C17,Region!A$2:B$98,2))"
'Then drag it down to last cell in range
Range("A17:A" & [D2000].End(xlUp).Row - 1).FillDown

End Sub
*********************************************************
I know it looks jumbled here, but in the code the formula is all on one
line. The drag down part works fine if I first manually populate the formula
in A17, but I can not get the macro to populate it for me. I am not sure of
the difference between Formula and FormulaR1C1, but I have tried both, and
neither work. I saw in a recent post that I need to double up the internal
quotes, so I did to no avail. I am sure it is something simple, but I can
not see it. Any ideas? Thank you

Greg Snidow
Click to expand...
Click to expand...
Click to expand...
 
L

Lusy

Bernie,

I have a new question for you. I want to do as this formula, which you help
me before, and it wraps in vertical direction:

With Sheets("Sheet2").Range("A2:A" &
Sheets("Sheet1").Range("A2").End(xlDown).Row)
..Formula = "=Sheet1!A2&char(10)&Sheet1!B2&char(10)&Sheet1!C2"
..WrapText = True
End With

but rather of going vertical, i want it to go horizontal.

in the sheet 1 I have (row 2, 3, 6 ... are empty and so on).
A B C
Month Day Year
1 04 08 08
2
3 05 06 08
4
5 04 04 08

in the sheet 2 i want it to wrap into one a single cell in horizontal way
with char 10:
A B C D
1 04 05 04
08 06 04
08 08 08

thank you so much,
Lusy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro question 1
VLOOKUP question 3
Inconsitent Formula Error 1
Excel Import Comments 3
Change text in Textbox on Chart? 3
Lookup formula problemo 2
Vlookup from a drop down list 2
Deleting a range of cells. 3

Top