REPOST - CF, MACRO

[Tree]

I am resposting this because I think it is not evident I replied..

THANK YOU TO BOTH!!
I think this will work, except I of course assumed you could read my mind
and knew exactly all the other particulars of the situation!! I am very sorry
to be so dense..

Here is a (hopefully) better specific detail of the situation:

Row 1 contains headers and I can't find how to have the Macro "Start"? on
Row 2;

Columns I:L would contain the formulas and they would be looking in Column C
for text and if finding text in that column, the formulas would run in each
column, i.e., Column I takes Column D + Column E - Column F.. Column J takes
Column D + Column E - Column H. Column K = Column G. Column L is the formula
Column J - Column K.
And these formulas need to "copy down and into succeeding rows" into Columns
I through L as long as there is text filling in Column C.
Columns A through H data is already there from the Ron Bruin macro which is
based on constantly changing data on individual tabs throughout the workbook.
I am ok with adding macros and feel a little comfortable with the VB world..
albeit not as comfortable as you wonderfully brilliant folks! ..
thank you again for your time!

You might be able to modify this and add it to your existing code.

'========
Sub CreatesFormulas()

'Where do you want formula?
For Each cell In Range("B:B")
'What column are you checking for text?
If Cells(cell.Row, "A").Text <> "" Then
'What is the formula?
'Use double quotes if actual formula
'would contain a single quotation
cell.Formula = "=COUNTIF(A:A,""Bob"")"
End If
Next
End Sub
'=============

FROM BOB BRIDGES:
"Wonderfully brilliant", that must be me!
If you don't know how to write a macro yourself and don't care to start
learning, then I imagine you don't want to modify Mr Bruin's macro either, in
other words you'd prefer to stick with worksheet functions. That may be
possible, but first I need to know exactly what you want copied: what does
"down rows and into columns" mean, exactly? The rest of it may be a bit
complicated but should be possible.


ORIGINAL POST:
I am not sure what to use but here is the situation:
I have a macro that Ron Bruin brilliantly helped with that combines data in
several tabs into one summary sheet and that works great..

Then, in the summary sheet I need to add formulas that "automatically" copy
down rows and into columns based on if there is a text in a certain cell in
that row.. if there isn't, then I need the condition/macro to stop..

I have tried a couple of different things and nothing is working so
obviously I am lost and need help from the wonderfully brilliant folks here..

Thank you in advance for your time and assistance..

 

[ker_01]

Based on your post, it looks like you just want to autofill formulas for rows
that have data.

The following should work (you can add as many columns as needed). Try this:

Sheet1:

A B
Header Header
123 =A2 [evaluates to 123]
456
789

Then put this in a module and run it:

Sub CreatesFormulas()
LastCell = Find_LastCellInColumn(Sheet1, "A")
Sheet1.Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & LastCell), Type:=xlFillDefault
'Sheet1.Range("C2").Select
'Selection.AutoFill Destination:=Range("C2:C" & LastCell), Type:=xlFillDefault
End Sub

Function Find_LastCellInColumn(sht As Worksheet, ColID As String)
'searches down, so may stop on first empty cell- make sure target column
has no blank cells
Find_LastCellInColumn = sht.Range(ColID & "1").End(xlDown).Row
End Function

You should get

A B
Header Header
123 =A2 [evaluates to 123]
456 =A3 [evaluates to 456]
789 =A4 [evaluates to 789]

To do all the columns you mentioned (I through L) just change the column
from B to I, uncomment the 'C' lines and make them J, and add two more to
cover I and L.

This requires that you use the same formula starting in row 2 all the way to
the last row of your data; if you make any changes within those cells they
will be overwritten the next time you run the macro.

HTH
Keith

 

[Tree]

Thank you for your prompt reply.. but this did not work.. I need the
different formulas to fill into the respective columns (I-L) as mentioned
below based on if Cell C is NOT blank and to keep doing this until there is
no more data in Cell C, and keeping in mind that headers are in Row 1 and the
data has filled from a macro that has already run.

In your example, I don't see where it allows for the formulas that I need in
the columns as I stated below..

Thank you!!

Based on your post, it looks like you just want to autofill formulas for rows
that have data.

The following should work (you can add as many columns as needed). Try this:

Sheet1:

A B
Header Header
123 =A2 [evaluates to 123]
456
789

Then put this in a module and run it:

Sub CreatesFormulas()
LastCell = Find_LastCellInColumn(Sheet1, "A")
Sheet1.Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & LastCell), Type:=xlFillDefault
'Sheet1.Range("C2").Select
'Selection.AutoFill Destination:=Range("C2:C" & LastCell), Type:=xlFillDefault
End Sub

Function Find_LastCellInColumn(sht As Worksheet, ColID As String)
'searches down, so may stop on first empty cell- make sure target column
has no blank cells
Find_LastCellInColumn = sht.Range(ColID & "1").End(xlDown).Row
End Function

You should get

A B
Header Header
123 =A2 [evaluates to 123]
456 =A3 [evaluates to 456]
789 =A4 [evaluates to 789]

To do all the columns you mentioned (I through L) just change the column
from B to I, uncomment the 'C' lines and make them J, and add two more to
cover I and L.

This requires that you use the same formula starting in row 2 all the way to
the last row of your data; if you make any changes within those cells they
will be overwritten the next time you run the macro.

HTH
Keith

I am resposting this because I think it is not evident I replied..

THANK YOU TO BOTH!!
I think this will work, except I of course assumed you could read my mind
and knew exactly all the other particulars of the situation!! I am very sorry
to be so dense..

Here is a (hopefully) better specific detail of the situation:

Row 1 contains headers and I can't find how to have the Macro "Start"? on
Row 2;

Columns I:L would contain the formulas and they would be looking in Column C
for text and if finding text in that column, the formulas would run in each
column, i.e., Column I takes Column D + Column E - Column F.. Column J takes
Column D + Column E - Column H. Column K = Column G. Column L is the formula
Column J - Column K.
And these formulas need to "copy down and into succeeding rows" into Columns
I through L as long as there is text filling in Column C.
Columns A through H data is already there from the Ron Bruin macro which is
based on constantly changing data on individual tabs throughout the workbook.
I am ok with adding macros and feel a little comfortable with the VB world..
albeit not as comfortable as you wonderfully brilliant folks! ..
thank you again for your time!



FROM BOB BRIDGES:
"Wonderfully brilliant", that must be me!
If you don't know how to write a macro yourself and don't care to start
learning, then I imagine you don't want to modify Mr Bruin's macro either, in
other words you'd prefer to stick with worksheet functions. That may be
possible, but first I need to know exactly what you want copied: what does
"down rows and into columns" mean, exactly? The rest of it may be a bit
complicated but should be possible.


ORIGINAL POST:
I am not sure what to use but here is the situation:
I have a macro that Ron Bruin brilliantly helped with that combines data in
several tabs into one summary sheet and that works great..

Then, in the summary sheet I need to add formulas that "automatically" copy
down rows and into columns based on if there is a text in a certain cell in
that row.. if there isn't, then I need the condition/macro to stop..

I have tried a couple of different things and nothing is working so
obviously I am lost and need help from the wonderfully brilliant folks here..

Thank you in advance for your time and assistance..